Maintain Large Tables / Cleanup Data From Our Tables
May 18, 2011
I have to cleanup data from our tables (Production Environment) that contain millions of rows. The question is apart from the solution of the partitioned tables what alternative recommended solution suggests Oracle?
To delete these tables by using a cursor PL/SQL block or to import all the database and in the tables that we want to remove the old rows to use the QUERY option of the data pump utility.
I have used both ways and i have to admit that datapump solution is much much faster than the deletion that suffers from I/O disk.The question again is which method from these two is more reliable and less risky for the health of the database.
View 5 Replies
ADVERTISEMENT
Feb 6, 2012
I have a 27 million row table in the following format:
MEDCLM_MTH_SUM_KEY PRIMARY_DIAG_CD DIAG_CD2 DIAG_CD3 DIAG_CD4 DIAG_CD5 DIAG_CD6 DIAG_CD7 DIAG_CD8 DIAG_CD9 DIAG_CD10
2212990780 5552 78907 53170 5368
2231127242 V5481 7812 71595 4019 2761 2859 496 V4364 30501
I need to unpivot this data to get it to look like this:
MEDCLM_MTH_SUM_KEY DIAG_CD_LEVEL DIAG_CD
2212990780 PRIMARY_DIAG_CD 5552
2212990780 DIAG_CD2 78907
2212990780 DIAG_CD3 53170
[code]...
I was wondering if there was a quicker, more efficient way to do this.
View 3 Replies
View Related
Nov 2, 2008
is it possible to upload very large files in oracle's tables. For example 1-2 gigabyte video file or even more. In other words is it possible to use oracle as file server to upload very large files and store them?
View 2 Replies
View Related
Sep 3, 2012
I came across an implementation where data from DB2 tables are moved to Oracle tables, for BI solutioning, using some oracle procedures called from MS SQL DTS packages which are scheduled jobs.Just being curious, can this be done using OWB or ODI rather than the above detour. I suppose there are some changes being done in those procedures before the data is being loaded into Oracle tables, can't this be done using OWB/ODI? Can it be scheduled too as jobs using OWB/ODI?
View 1 Replies
View Related
Mar 14, 2011
I have a large table with 450 column and we are only using nearly 170 columns and our BD block size is 8k.The DBA informed that there is an row chaining happening in the Database.My question is if we have data available in 170 column .why row chaining is happening.
The DBA informed us to remove the unnecessary columns .. Does those empty columns have any impact on the chaining.If we increase the size of DB block to 32k . does it will resolve the issue.
View 4 Replies
View Related
Jul 17, 2013
11.2.0.3 This is for a build. We are still in development. No risk of data loss. As part of the build, I drop the user,re-create it, re-create the objects. Allows us to test the build all the way through. Its our process. This user has some tables with several 1000 partitions. I ran a 10046 trace and oracle is using pl/sql to do loops to do DML against the data dictionary. Anyway to speed this up? I am going to turn off the recyclebin during the build and turn it back on. anything else I can do? Right now I just issue 'drop user cascade'. Part of is the weak hardware we have in the development/environment. Takes about 20 minutes just to run through this part of the script (the script has alot more pieces than this) and we do fairly frequent builds. I can't change the build process. My only option is to try to make this run a little faster.
View 3 Replies
View Related
Apr 5, 2012
Create small functional indexes for special cases in very large tables.
When there is a column having one values in 99% records and another values that have to be search for, it is possible to create an index using null value. Index will be small and the rebuild fast.
Example
create index vh_tst_decode_ind_if1 on vh_tst_decode_ind
(decode(S,'I','I',null),style)
It is possible to do index more selective when the key is updated and there are many records to create more levels in b-tree.
create index vh_tst_decode_ind_if3 on vh_tst_decode_ind
(decode(S,'I','I',null),
decode(S,'I',style,null)
)
To access the record can by like:
SQL> select --+ index(vh_tst_decode_ind_if3)
2 style ,count(*)
3 from vh_tst_decode_ind
4 where
5 decode(S,'I','I',null)='I'
6 group by style
7 ;
[code]....
View 2 Replies
View Related
Nov 9, 2010
problem on oracle 11gR2 where i have to import data from a source database to an existing table without truncate or drop the target table in the target database.
we have found something called table_exist_action=append in impdp.
View 2 Replies
View Related
May 29, 2013
In our schema we have corresponding audit tables for most of the production tables
Ex Table name Audit Table
EMP EMP_AU
DEPT DEPT_AU
Audit tables will have all the columns of production table along with audit columns AUDIT_DATE , AUDIT_OPERATION There are few production tables which are not having audit tables.I need to write a script to identify
1) Production tables where corresponding audit table is missing
2) Where there is column difference (In case any column missing in audit table) between Production table and Audit table
View 11 Replies
View Related
Oct 11, 2012
I need to export only the data from schemas or tables, how to do that with Oracle Data Pump? when we use schemas parameter this export all schema, not only the data right?
View 7 Replies
View Related
May 30, 2011
I created a data warehouse in oracle 10g n with three Dimension and one cube after that it crates 4 tables . How to use an insert sql statement to insert data in those tables n how to access them.
View 7 Replies
View Related
Apr 12, 2008
I have a problem with my Oracle 9i SQL Query and I'm struggling to get it done.
I have three tables namely Student, Lease and Room and want to retrieve data from these three tables.
I want the Student name, the Lease details and the Room No from these tables.
The problem with my SQL query is that I get all the information from the tables except from the Room table, where in the column it show Room_No but the values are not displayed, the query is given below.
SELECT STUDENT.STU_FNAME, STUDENT.STU_LNAME, LEASE.LSE_NO, LEASE.LSE_DURATION, LEASE.LSE_STARTS, LEASE.LSE_ENDS, ROOM.ROOM_NO
FROM STUDENT
LEFT OUTER JOIN LEASE ON LEASE.STU_ID = STUDENT.STU_ID
LEFT OUTER JOIN ROOM ON ROOM.PLACE_NO = LEASE.PLACE_NO;
View 3 Replies
View Related
Feb 15, 2009
I have two tables
table1
col1.....................................col2
primary key................ foreign key refer to col2 in tab2
table2
col1 ........................................... col2
foreign key refer to col1 in tab1 ............ primary key
now my question is how to insert data in to the two tables
View 2 Replies
View Related
Sep 23, 2011
I have two databases.
one is "ora" it is a 8i version
2nd is "orcl" it is a 11g version
"Oracle" is the my local database. i wrote following program for comparing the row by row data in both the tables.
Q)Is it BEST practice? If not let me know the best practice to compare data in tables?
Q) If am not using the order by clause its giving me wrong output even though both the data tables has same data. WHY?
DECLARE
v_emptest1 EMP_TEST1%ROWTYPE;
v_emptest2 EMP_TEST1@ora%ROWTYPE;
v_suc_flg NUMBER := 0;
v_cnt1 NUMBER:=0;
[code].......
View 9 Replies
View Related
Sep 12, 2011
Is there any way i can select * from all the tables owned by particular schema at once.
View 22 Replies
View Related
Mar 16, 2010
I want to do a comparision for the missing rows between two diffrent tables
TBL1 and TBL2 both with the same structure but with diffrent data some data is identical. though my data is huge i wanted to make sure the technique i am using
View 8 Replies
View Related
Feb 11, 2011
What are some performance issues you might run into when having to query 3 or 4 tables at a time.
View 1 Replies
View Related
Jun 2, 2011
I have a requirement to get the data in "SQL" directly and should not use plsql/ can't redirect the output to a .sql file. I have a master table Main_tab which does have all "child table" names.
I want to build a query such that it will give all the data from child tables.
create table main_tab ( tab_name varchar(10));
insert into main_tab values ('one');
insert into main_tab values ('two');
insert into main_tab values ('three');
create table one ( a char);
insert into one values ('a')
create table two ( b char);
insert into two values ('b')
create table three ( c char);
insert into three values ('c');
Now I have got the query to combine all the table data using
select decode (rownum,1,'','union all ') || 'select * from ' || tab_name example from main_tab
but I am expecting the output to get as
a
b
c
which is the data from tables one,two and three respectively.
I don't want to save the above select data in to .sql and then execute it , instead I want to run it on "fly" in SQL itself. Is there such a possibility to do so?
View 12 Replies
View Related
Sep 12, 2012
I have 3 tables as below. I am looking to get data from these 3 table at a time using a pl/sql block but unable to do. Is there any way that I can get the data in a single query.
PS:All the columns are same in all the 3 tables
table1_COLLECTIONS_a
table1_COLLECTIONS_b
table1_COLLECTIONS_c
View 7 Replies
View Related
May 13, 2013
In SQL...I want to display the manager details and the corresponding employee details and i donot want to use joins or co-related subqueries
I am trying it to use CASE or DECODE..
Here is the Query..
SELECT *
FROM (SELECT empno,
ename,
sal,
job,
CASE
WHEN empno IN (SELECT mgr FROM emp)
[code].....
The output is as follows..
7839KING5000PRESIDENT7839 KING Manager 5000 0 10
7698BLAKE2850MANAGER 7698 BLAKE 7839 2850 0 30
7782CLARK2450MANAGER 7782 CLARK 7839 2450 0 10
7566JONES2975MANAGER 7566 JONES 7839 2975 0 20
7902FORD3000ANALYST 7902 FORD 7566 3000 0 20
7788SCOTT3000ANALYST 7788 SCOTT 7566 3000 0 20
I am able to display only the Manager details or employee details and not both at the same time..
View 13 Replies
View Related
Aug 19, 2010
I have to move data from old tables into new tables. I use a sqlplus script and execute multiple 'insert from select'. I thought the SQL statements are executed in sequence, one after the other. I run into this problem: Let's take this script for an example
insert into A (a,b,c,....
select a,b,c......
from X;
insert into B (a,b,....
select a,b,....
from A joined with Y;
commit;
After executing the script in our development DB
select count (*) from A
returns 200
When the script is executed in production, A table record count is 5
select count (*) from A joined with Y; (used in the second insert)
returns 200.
It seems that the two statements are executed at the same time and table A is not fully populated when the select in statement two is executed.I suspect this is due to parallel execution in production.
Will executing
ALTER SESSION DISABLE PARALLEL DML;
ALTER SESSION DISABLE PARALLEL QUERY ;
View 8 Replies
View Related
Aug 2, 2010
i have source table having 1000 records, i want insert first 100 rows in table1,second 200 rows in table2 and remaining row in table3.
View 12 Replies
View Related
Aug 15, 2012
My tnslistner is not working that is why i am not able to login in my database. so i have planned to reinstall it and if possible get my database back from old files.
i do not have backup, is there any way to get tables and data back.
View 5 Replies
View Related
Jul 21, 2012
I Have to write a procedure which takes XML data and inserts into some tables.
If the XML format is fixed then i can use extract function for parsing and can insert into the tables.
But the problem is there is not fixed format for the xml.
Are there any built in packages which takes the xml data for parsing..
View 3 Replies
View Related
Mar 24, 2011
I'm having trouble to get the weekend records from 2 tables. table 1 has a start date and an End Date. Table 2 has END_INTV_TIME ( HOUR FORMAT AS 0100-1am,0200-2am.......1300-1pm,1400-2pm.....2400).
create table t (traffic_sample_id number,
site-id_nbr varchar2(12),
start_date varchar2(6),
end_date varchar2(6)
[code]....
My requirement is to get the weekend days and i tried using the function
( TO_DATE (start_date,
'mmddrr'
)
+ TRUNC (rn / 24),
'Day'
)
{code}
But the problem is its going just after the table1 START_DATE and getting me the weekend day based on start date, but when i join the tables the problem is with the END_INTV_TIME, the recods start at 1000 (10am) (bin_data_id =1037804) so according to my logic the weekend records will be 48 records from the first record, but i want to take into consideration the END_INTV_TIME as well when its (0100 1am) then my weekend starts ie(1037843 bin_data_id) instead my weekend record is coming from 1037852 (1000 10am) ,
View 4 Replies
View Related
Jun 27, 2007
I have a form which has three detail portions. I want that when I press SAVE, it should insert data in two tables & then run the specific code & then insert data in other two tables.
I am using Developer 6i. Couldn't find out the proper trigger or related thing.
View 2 Replies
View Related
Mar 13, 2007
how can I insert data into tables on another user. They both are in the same table-space.
View 1 Replies
View Related
Jun 16, 2011
In my select statement i am fetching the data from
OE_ORDER_HEADERS_ALL,OE_ORDER_LINES_ALL,WSH_DELIVERY_DETAILS,WSH_SERIAL_NUMBERS.
I need appropriate links for that tables.
View 2 Replies
View Related
Aug 29, 2008
Im having a problem with writing an appropriate query for a report in my web application. I need it to extract data from three related tables:
CAR(
PK CAR_ID INT NOT NULL,
TYPE VARCHAR NOT NULL)
REPAIR_CENTER(
PK REPAIR_CENTER_ID INT NOT NULL,
NAME VARCHAR NOT NULL)
[code]...
I need the report to display only available cars. Available cars must have these characteristics:
1. if the CAR_REPAIR table is empty, displays all entries from CAR table...
2. if car has multiple entries in the CAR_REPAIR table display only the latest DATE_RETURN if its lower than todays date (SYSDATE), otherwise don't display that car...
3. don't display cars that are in the CAR_REPAIR table and have DATE_RETURN value of NULL
View 3 Replies
View Related
Apr 19, 2011
How to Insert the data in Repeating Tables. I mean Suppose One Student has many Addresses like home,office,permanent etc.
There is one column in this table sequence_no. while Inserting a record how to insert this sequence no I don't know It maintains unique sequence no for each student.
If student has 3 addresses then Its seq no is 3.
I am inserting through a procedure where multiple students data is to be inserted.
how to take care of this sequence no.
View 15 Replies
View Related
