Purging Table Which Is Having 98 M Rows

Dec 3, 2012

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]....

View 4 Replies


ADVERTISEMENT

Adrci Purging Of Command?

Sep 27, 2011

I am using the following command within a shell scrript to purge files with adrci

adrci exec="show homes"|grep -v : | while read file_line
do
echo "adrci purging diagnostic destination " $file_line
echo "purging ALERT older than $Minutes ..."
adrci exec="set homepath $file_line;purge -age $Minutes -type ALERT"

[code]...

Is there a command I can use to see what will be purged or what has been purged?

View 2 Replies View Related

Implicit Commit While Purging

Oct 24, 2011

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.

View 1 Replies View Related

Alert Log And Listener Log File Purging In 11g?

Mar 6, 2013

how to purge the alert log and listner log file in 11g.

OS:IBM/AIX RISC System/6000
DB version: 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

View 8 Replies View Related

Archiving And Purging Data From Huge Tables?

Apr 22, 2013

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.

The database we are using is Oracle 10g.

View 1 Replies View Related

SQL & PL/SQL :: Primary Constraint On Table Affecting Procedure To Insert Rest Of Rows In Table?

Jun 12, 2012

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

[code]...

View 2 Replies View Related

SQL & PL/SQL :: Load 10 Million Rows In Table From Another Table Based On Multiple Joins

Sep 24, 2010

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.

View 11 Replies View Related

Delete 4 Millions Of Rows From A Table Having 25 Millions Of Rows?

Dec 14, 2007

I joined the forum just today, i need some tips on deleting the millions of rows from a huge table having 25 millions of rows.

View 4 Replies View Related

PL/SQL :: How To Transpose A Table From Rows To Columns Or Columns Into Rows

Aug 22, 2012

what are the collections available in Oracle Plsql, what are concepts of collection.

How to Transpose a Table from rows to columns or columns into rows.

DDL and DML concepts.

What is the concepts of statistics in Oracle Plsql.

View 4 Replies View Related

SQL & PL/SQL :: Rows From Each Table In DB

May 5, 2011

Scenario like this , I have a dB which contain 100 tables, i want to find out each table contain how many rows excluding

system tables ..

how can i write query for this.

View 2 Replies View Related

SQL & PL/SQL :: How To Delete Rows From Table

Oct 19, 2011

I am using the below proc to delete some records

1)select client_id,count(*) from TCLIENT_NOTIFICATION_PACK where client_id=1620560178 group by client_id order by 2 desc;

client_id count(*)
----------- ---------
16205601785128

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.

View 23 Replies View Related

SQL & PL/SQL :: How To Display All The Rows In A Table

Sep 8, 2010

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.

View 3 Replies View Related

SQL & PL/SQL :: Rows Of A Table Updated

Jul 17, 2012

write a query find which rows of a table is updated on 2 days before?

(OR)

In table there r so many rows write a query which two rows r updated last two day before?

View 4 Replies View Related

SQL & PL/SQL :: Delete Even Rows Of A Table

Oct 25, 2006

How to delete even rows of a table or rather alternate rows of a table.

View 31 Replies View Related

SQL & PL/SQL :: How To Delete 1% Of Rows From A Table

Jun 13, 2011

I have a table, in that i have "n" no of rows from that i want to delete 1% of rows from that table

View 26 Replies View Related

PL/SQL :: Reduce Rows In A Big Table

Dec 19, 2012

I have one very big table , says employee table, I need to keep less rows to do this

1. create table employee_backup as select * from employee

2. truncate table employee

3. insert into employee select * from employee_backup where rownum < 1000

is #3 sript is OK? if I only want 1000 rows ?

View 5 Replies View Related

PL/SQL :: Fetch 1 Lac Rows From Table

Dec 15, 2012

OS : widows 2003

Oracle 9i release 2

I need to Fetch 1 lakh rows from table (all rows) in fastest way.

How to do it?

View 6 Replies View Related

How To Select 10 Rows From Mission Table

Sep 2, 2008

i want the 10 most recent mission date from mission table . i have tried using rownum but could not get it. TOP does not work

select * from
(select length(code_name) leng from missions
where length(code_name) >7
order by mission_date)
where rownum = 10
;

this does not work ::: no rows are selected and i do rownum = 1 then gives me one record and when i do rownum>=1 gives me all records

View 10 Replies View Related

Convert Rows To Columns In A Table?

Apr 24, 2012

How do I convert rows in oracle table to columns by writing a query?

Here is my table.

EID QNo Qual YOP
101 1 B.Tech 2004
101 2 M.Tech 2006
102 1 B.Tech 2003
102 2 M.Tech 2005
102 3 MBA 2007

Now I want the above table in the following format......

EID Qual1 YOP1 Qual2 Yop2 Qual3 Yop3
101 B. Tech 2004 M.Tech 2006
102 B. Tech 2003 M.Tech 2005 MBA 2007

I have maximum of 8 Qualifications, how to achieve this in oracle.

View 3 Replies View Related

Import And Export Table With Around 5 Lac Of Rows

Sep 18, 2013

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.

View 3 Replies View Related

Displaying Rows Into Columns In A Table

Oct 20, 2012

I have table having below records.

empno ename deptno
101 a 10
102 b 20
103 c 10
104 d 20
105 e 30

Normal Output

deptno count(*)
-----------------
10 2
20 2
30 1

I want to display like this(rows into columns)
--------------------------------------------------------
Required Output
-------------
10 20 30
2 2 1

View 1 Replies View Related

SQL & PL/SQL :: Joining Subsequent Rows In A Table

Mar 29, 2010

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.

Table:
ID Date Price
---------------------
1 2010/1/1 100
1 2010/1/2 125
1 2010/1/5 115
2 2010/1/1 115
2 2010/1/2 105
2 2010/1/5 125

I'd like to return

ID Date1 Date2 Price1 Price2
--------------------------------
1 2010/1/1 2010/1/2 100 125
1 2010/1/2 2010/1/5 125 115

etc ..

View 11 Replies View Related

SQL & PL/SQL :: How To Remove Duplicate Rows From Table

May 8, 2013

how to remove duplicate rows from table?

View 6 Replies View Related

SQL & PL/SQL :: Select Updated Rows From One Table

Jul 13, 2013

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

COL_A1COL_A2
BBBTABLET
DDDUNKNOWN

View 2 Replies View Related

SQL & PL/SQL :: How To Retrieve Duplicate Rows From A Table

Jun 14, 2010

How to retrieve duplicate rows from a table, suppose i don't know any column names. Without hard-coding column names I need to find.

View 5 Replies View Related

SQL & PL/SQL :: Select Rows From Table With Same Particular Field?

Aug 20, 2012

select the rows from a table with the same particular field in PL/SQL. Actually I don't want to write two loops one inserted into another.

View 7 Replies View Related

SQL & PL/SQL :: Procedure To Display All The Rows From A Table?

Mar 25, 2013

how to create a procedure to display all the rows in a table.

I wrote the below PL/SQL but im hitting as below.

DECLARE
howmany NUMBER;
some_full_name login_user.full_name%TYPE;
some_login_id login_user.login_id%TYPE;
some_users login_user%ROWTYPE;

[code]....

error :

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:

[code]....

View 19 Replies View Related

SQL & PL/SQL :: Count Distinct Rows In Table

Feb 21, 2013

I think this is easier than I think but all I want to do is count the distinct rows in a table.

WITH data AS
(SELECT 1 id1, 1 id2, 111 val1
FROM dual
UNION ALL
SELECT 1, 2, 222
FROM dual
UNION ALL

[Code]..

seems to get what I want

ID1VAL1CNT1
111114
212224
321114
422224

but can the count be done in a stand alone query such as??

SELECT DISTINCT id1,
val1,
COUNT(*) over() cnt1,
COUNT(*) over(PARTITION BY id1) cnt2
FROM data
ORDER BY id1, val1

NOT what I want

ID1VAL1CNT1CNT2
1111152
2122252
3211153
4222253

View 5 Replies View Related

SQL & PL/SQL :: Unable To Retrieve Rows From A Table

Mar 7, 2012

I have created a table called ct_temp and it's structure is:

create table ct_temp(id char(2),srs number(3),amt number);

Result : Table Created

I now insert the following rows into the table:

id srs amt

1 62 30000
2 65 50000
3 65 70000
4 65 80000
5 62 16000
6 65 10000
7 65 100000
8 65 10

Commit

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

Is there any way around?

View 26 Replies View Related

SQL & PL/SQL :: Selected Number Of Rows From A Table

Aug 3, 2011

Can we select table's rows of 5 to 15, like

select * from emp
where rowid <=5;

It is through an error when i used the below method.

select * from emp
where rowid >=5 and rowid <=15;

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved