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.
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?
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?
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.
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.
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 ;
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.
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
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?
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.
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.
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?
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
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?
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.
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 ;
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.
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).
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) ,
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.
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
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.
