I want to purge a table which is having more then 98M rows...here are the details...
Purge Process I followed
---------------------------------------------
Step 1. Created backup table from Main table with required data only
create table abc_98M_purge as
select * from abc_98M where trunc(tran_date)>='01-jul-2011'
-> table created with 5325411 rows
Step 2. truncate table abc_98M
Step 3. inserted all 5325411 rows back to abc_98M from abc_98M_purge using below procedure
DECLARE
TYPE ARRROWID IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
tbrows ARRROWID;
row PLS_INTEGER;
cursor cur_insert is select rowid from abc_98M_purge order by rowid;
BEGIN
open cur_insert;
loop
[code]....
I have written a purge package that would delete records older than 10 years. Since the data is huge, the purging was taking 14 hours plus. To improve performance, I disabled constraints , deleted records and then reanabled them. This was quite quick but the only problem is rollback. Say for some reason if enabling constraints fails there is no way to rollback as enabling and disabling constraints does an implicit rollback.
I'm currently working on a project which is to archive the old data and then purge the same data from the main table.
Here is a detail description:
There are around 50 odd tables from which I would need to archive the old data(matching certain filter conditions...not date based). Meaning I have to store the data in a temp table. Once stored in temp table then I would have to delete those rows from the main table. This temp table will be later exported and stored on ARchive database(a seperate database). These tables are very huge. One of the table is actually 250 GB in size. And all these tables have many indexes built - both normal and bitmap.The 250 GB size table has 40 million rows that need to be archived and purged. The total number of rows in the table are 540 million.On this table alone there are 50 bitmap indexes and 2 normal indexes. This table is partitioned based on date column.This date column is not used/useful in identifying the old data. There are around 20 tables which are quite similar in size to the above described table. Rest of them are little small when compared to the above table.
We have to execute this activity over a weekend which gives us about 48 hours time to complete the activity. Best possible ways to handle this activity. Most importantly should be able to complete the activity within the specified 48 hour window.
The solution what we are now thinking of is:
1. Create the temp table ---Create tmp_tbl as select * from main_table where <<conidtions identifying old data>>
2. Once the temp table is created. Make copy of indexes that exist on the main table and eventually drop them.
3. Execute a PL/SQL script to perform the bulk delete from main table and commit for every 100000 rows.
4. Once the bulk delete is finished then recreate the indexes on the main table using the copy made at earlier step.
Our main worry is about the step#4. Considering the size of these tables and the number of indexes to be built,we are not sure how long the index re-creation will run for each table.
depending on the possibilities we may have to split the activity in to 2-3 phases spreading across 2-3 weekends. Even then we are not sure whether we will be able to pull off this activity.
primary key constraint on transaction_dtl_bk is affecting the insertion of next correct rows.
CREATE OR REPLACE PROCEDURE NP_DB.san_po_nt_wnpg_1 ( dt DATE ) IS v_sql_error VARCHAR2 (100); -- added by sanjiv v_sqlcode VARCHAR2 (100); ---- added by sanjiv added by sanjiv
We have to load 10 million rows in a table from another table based on the multiple joins. How much tablespace size we allocate to the table and for performance point of view how much should be the SGA size.
2)select client_id, count(*) from TCLIENT_NOTIFICATION_PACK where client_id=1620560178 group by client_id having count(*) > 40 order by 2 desc client_id count(*) ----------- --------- 16205601785128
3) select client_id,clnt_notification_pack_tid -- bulk collect into v_client_id,v_notif_tid from (select clnt_notification_pack_tid, client_id, clnt_notification_pack_typ_tid, crte_dt,
[code]....
4) Iam using the below proc to delete the rows from table, except the 4 rows returned above
declare v_clnt_notification_pack_tid TCLIENT_NOTIFICATION_PACK.CLNT_NOTIFICATION_PACK_TID%type; tYPE t_client_id is table of TCLIENT_NOTIFICATION_PACK.client_id%type; tYPE t_notif_tid is table of TCLIENT_NOTIFICATION_PACK.clnt_notification_pack_tid%type; v_client_id t_client_id; v_notif_tid t_notif_tid;
[code]....
5) After running this procedure, i shud see 5124 records, but i see zero records.
I am bit confused to display all the rows in a column using stored procedure like
In sqlserver: create procedure test as begin select * from table; end in sqlserver it is working perfectly.
In oracle: create or replace procedure test is begin select * from table; end;
But in oracle i am getting an error like select * into something like that.I understand the error. i.e. i need to move the column values into variables.if it a single row then there will be no problem.i want to display all the rows in the table at one time.i can not use cursors.
I have oracle database 11.2.0.3 OS Hp-ux I have one table with around 5 lac of rows I want to copy this table to another oracle database 11.2.0.3 with different OS windows server 2008 R2.
I create a second "DATE_PAIRS" table, and join a few subqueries. It works with conditions (ID=1), but scanning the whole table (2+ million records) is abysmal.
There are two tables like I posted below.I want a SQL query which selects all the rows from TABLE A which are not present in TABLE B. Also the select statement should pick all those rows which has updated value of COL_A2 in TABLE A.
TABLE A COL_A1COL_A2
AAAMOBILE BBBTABLET CCCDESKTOP DDDUNKNOWN
TABLE B COL_B1COL_B2
AAAMOBILE BBBUNKNOWN CCCDESKTOP
The select statement should return following rows from TABLE A
Error report: ORA-06550: line 3, column 19: PLS-00225: subprogram or cursor 'LOGIN_USER' reference is out of scope ORA-06550: line 3, column 19: PL/SQL: Item ignored ORA-06550: line 4, column 18:
Then I issue the following query to retrieve data based on a specific criteria (Actually I have condensed the table and data because in Production, I have large number of rows; so for simplicity I am using 8 rows with one table)
criteria : I then want to retrieve the following:
for srs = 62, all values that is greater than 10,000
Answer: select decode(srs,62,ab.amt)temp1 ,decode(srs,65,ab.amt)temp2 from ct_temp ab
where decode(srs,62,ab.amt)>10000
Works like a charm and retrives the results.
Now comes the big issue
I want to retrieve the values for srs = 62 which is greater than 10,000 and at the same time I also want to retrieve the values for srs = 65 which is less than srs = 62.
Typically I wrote the query as:
select decode(srs,62,ab.amt)temp1 ,decode(srs,65,ab.amt)temp2 from ct_temp ab where decode(srs,62,ab.amt)>10000 and decode(srs,65,ab.amt)<decode(srs,62,ab.amt)
I should atleast get one row for srs = 65 which is id # 8 but it displays blank rows or "no rows returned".
I am actually preparing a ad-hoc report for the business analyst and stuck at this step due to which I am unable to proceed any further. I have used DECODE function because of the requirement specified by Business Analyst.
I have tried the following in the office:
using EXISTS operator = no luck using INLINE VIEW = no luck
