SQL & PL/SQL :: Delete Redundant Table In Oracle?

Apr 23, 2012

Is there a SQL query to find out the redundant or unused table in Oracle.

Some tables shows no dependencies to itself or other schemas,but has got valid data in it, deleting it will affect the functionality of the system.

View 3 Replies


ADVERTISEMENT

PL/SQL :: Declaring Variable Twice In A Block Is Redundant Or It Gives Any Benefit

May 5, 2013

if table exists then, i whould like to delete it and re create it again.to do so i have two method pls tel me which one i should follow.

declaraion of variable v_strTN twoice in 1) query, does the twoice declaration gives any benefit or it is redundant?

1)
DECLARE
v_strTN VARCHAR2(30);
BEGIN
BEGIN
   SELECT TABLE_NAME INTO v_strTN FROM USER_TABLES
   WHERE UPPER(TABLE_NAME)='ABC';

[code]....

View 3 Replies View Related

Oracle 10g - Delete 1000 Table?

Dec 21, 2007

I have 1000+ tables in a DISCOVER tablespace in 10g.

I need to drop ALL tables in this area and leave the tblsp intact so that I can IMP a data update from 9i (prod) into 10g (test).

Is there an easy cmd to drop all the tbls, or am I missing someway to do this in DB Console for 10g.

View 3 Replies View Related

Oracle 9i - Delete Data One By One From Particular Table

Oct 28, 2008

I need to create a stored procedure in Oracle 9i which will automatically delete data one by one from a particular table and then by means of same procedure insert record one by one in same table.

View 6 Replies View Related

PL/SQL :: To Delete Child Records Manually Without Using Oracle Delete Cascade

Oct 9, 2012

I have to write a procedure that accepts schema name, table name and column value as parameters....I knew that i need to use metadata to do that deleting manually.

View 9 Replies View Related

How To Delete Table Information In SQL Loader Log File Output In Oracle

Jan 19, 2011

I have a SQL Loader Log File Output. I would like to delete Table Information from the output file.Following is my code.

//PATH OPENED AS TEXT BY STREAM READER
System.IO.StreamReader strm = File.OpenText(batchFileName);
//OUTPUT

[Code]....

View 8 Replies View Related

PL/SQL :: Delete Or Empty Single Field Containing BLOB In Oracle Table?

Jul 8, 2012

I have a table in schema with a BLOB field. I store employee's picture in this field. Fields in this table are emp_id (number) emp_name (varchar2) and emp_photo (BLOB). I want to ask if there is a way in pl / sql that i could empty this BLOB field to null or reset this field so that user can change the saved photo graph and save another one.

what i am looking for is something like

alter table employee set emp_photo = empty_blob() or alter table employee set emp_photo = null

View 3 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 :: Delete Duplication In A Table

Jul 7, 2011

I want to delete the duplicate rows in a table. there is no column which can be used to differentiate between the rows.

SELECT column_name1
FROM [table]
WHERE column_name2 = cond
GROUP BY column_name1
HAVING COUNT (column_name1) > 1

from the above query i can find the duplication in the table and can delete through it. But what i want one record of each duplication not to be deleted most probably the record added last to the table.

View 13 Replies View Related

SQL & PL/SQL :: Delete Table If Exist

Jan 18, 2013

delete table if exist

How do we write this code in oracle / sqlplus

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 :: Getting Error While Using Delete In Table

Feb 19, 2012

Declare
Cursor Cur_Emp Is
Select Empno,Ename
From Emp;
Type Type_Emp Is Table Of Cur_Emp%Rowtype Index By Binary_Integer;
[code].......

View 4 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

SQL & PL/SQL :: Delete Record From A Table

Oct 14, 2010

I have tried the following delete statement but it is taking long time,and it's not giving any result.

DELETE FROM hs_table WHERE sno=1234 and effdt='25-MAY-10';

The records in the table are 90000.And we are deleting only one report.

View 6 Replies View Related

SQL & PL/SQL :: Delete From A Table With Between Dates

Dec 7, 2011

I am trying to schedule a store procedure and wanted to remove some data from a table starting from a date and increase the delete by day until a specified date. I wrote the below function; but i can't get to work.

--Start
CREATE OR REPLACE FUNCTION remove_DateField return VARCHAR2 IS

i PLS_INTEGER;
startdate Date ;
endDate Date;
currentDate Date;
stopDate Date;
[code]....

View 8 Replies View Related

How To Delete Duplicate Entries In A Table

Sep 8, 2008

In oracle 9i ........How to delete duplicate entries in a table ?

if suppose in a table named office, one or more entry(row) is repeated more then twice or minimum twice.
I need a query to delete the multiple entries ....

Note:
--->No constraints applied on the table.
--->No Primary Key
--->You cannot create any object....that is no view or a duplicate table can be created

View 3 Replies View Related

SQL & PL/SQL :: Delete Duplicates From Table Without Using Rowid?

Mar 30, 2010

I am trying to delete duplicates from table without using rowid.

here is data:-

create table test(col1 number(3),col2 varchar2(20));
insert into test values(100,'rocky');
insert into test values(100,'rocky');

[Code]....

I know i am perfoming dml on view. IT wont allow me to perform DML on view which contain columns with expression. IS there any way to delete duplicates without using rowid?

View 36 Replies View Related

SQL & PL/SQL :: Parent And Child Table - Delete Row

May 18, 2010

I have a parent table and child table. I want a row to be deleted from the parent table which is referenced by a child row. Is there a way to achieve this. I dont have permission to re create the table or alter the table using delete cascade option. Is there a way to do it in sql.

SQL> create table t1(a number primary key, b number);
SQL> create table t2(c number, d number references t1(a));
SQL> insert into t1 values(1,2);
SQL> insert into t1 values(2,3);
SQL> insert into t1 values(3,4);
SQL> insert into t2 values(10,3);
SQL> insert into t2 values(20,2);
SQL> delete from t1 where a=2;
delete from t1 where a=2
*

ERROR at line 1:
ORA-02292: integrity constraint (CISBATCH.SYS_C00763501) violated - child
record found

View 11 Replies View Related

SQL & PL/SQL :: Delete Duplicate Records From A Table

Apr 13, 2011

I need to delete duplicate records from a table (indeed they are multiple duplicates).

Table Data

IDGroupQty
1KK30
1KK0
1KK19
2AA0
2AA30
3AA0
3AA30
3AA30
3AA9

My aim is to delete duplicates out of above data, with the below condtions.

1) first record with value 30 and then with value 0.
2) if there are 3 duplicate records ex: ID is 1 and Group KK, then i have to delete both 30 & 0 qty records.
3) If there are more than 3 duplicate records ex: ID is 3 and Group is AA, the i have to delete all the records with qty value either 30 or 0 and.

I have written a query like below.

SELECT id,
unit,
RANK ()
OVER (PARTITION BY id, unit
ORDER BY id, unit)
num
FROM temp;

with the above query, i am unable to mark this dynamic duplications.

View 10 Replies View Related

SQL & PL/SQL :: How To Delete Parent Table Record

Jul 10, 2012

how to delete Parent table records without affecting to child table dependent records?..

View 5 Replies View Related

SQL & PL/SQL :: Delete Duplicate Records From Table

Aug 30, 2010

How can i delete duplicate records from the table.

View 2 Replies View Related

SQL & PL/SQL :: Script To Delete Partition On Table?

Nov 14, 2012

I am looking for a script to delete partition on table.

View 3 Replies View Related

SQL & PL/SQL :: Delete Duplicate Record From Table?

May 12, 2011

I have written this below code. The logic behind the code is, Delete the duplicate record from a table, and delete those record from other 7 table based on the SL_NUMBER.

But Problem is After delete the duplicate record When I have use Below statement

RETURNING SL_NUMBER BULK COLLECT INTO rec_sl_number;

This statement unable to return approx 40 Lakhs SL_NUMBER

DECLARE
rec_sl_number dbms_sql.number_table;
BEGIN

[Code]....

View 6 Replies View Related

Forms :: Delete Record To Table

Jun 5, 2011

How can I delete a record simultaneously to table?

View 30 Replies View Related

PL/SQL :: How To Fast Delete Lot Of Data In A Table

Jan 9, 2013

I have a table with around 650,000,000 rows and we need to delete about 60,000,000 rows at the end every month and same amount of rows accumulate throughout the month. The deletion usually takes overnight to delete. We are using 10r2 in IBM AIX. The procedure we are using to delete is:

declare
ln_count number:=0;
begin
for i in (select rowid from table1 where some_id<2012090000)
loop
delete from table1

[code]...

When this procedure is started I mostly see that the session is busy in user i/o wait for db sequencial file read. Will using cursor instead will give better results.

View 13 Replies View Related

PL/SQL :: Delete Duplicate Values From A Table?

Aug 22, 2012

I have a table like this

table:

id name   plan   code
1   sam  normal   5
1   sam  normal   6
1   sam  special  5
1   sam  Special  6

I need to delete data in such a way that one entry with normal and one entry with special plan should remain and should be with different code. Does not matter whether normal stays with 5 or 6 code.

I tried with rowid but it deletes either both normal or both special or returns same code for normal and special.

View 8 Replies View Related

Performance Tuning :: Delete No Of Records From A Table

Aug 3, 2010

I am using one script to delete the records from a table, its taking 1hr to delete.

declare
cursor c1 is select ownerid,ownertype from nightly_metric_projects
;
v1 c1%rowtype;
open c1;
loop
fetch c1 into v1;
exit when c1%notfound;
DELETE FROM DGT_ITEMEFFORTDATA WHERE OWNERTYPE = c1.OWNERTYPE
AND OWNERID = c1.OWNERID;
end loop;
close c1;
commit;

nightly_metric_projects--1200 records
DGT_ITEMEFFORTDATA--13200000

View 14 Replies View Related

SQL & PL/SQL :: Insert Into Target Table And Delete From Source

Jan 2, 2013

We have a requirement to archive and purge the tables dynamically based on the control table input. For that we have to design a control table to gather the necessary information and passed to generate the queries.

I have designed the table as below.But in this case I am not able to handle the parent and child relation ship.

Suppose one table needs to be archived and purged and that table is parent table and it is having 2 child tables, so first required data will be inserted into target table and delete from source parent and child tables. so before deleting from parent we have to delete data from all 2 child tables.

Suppose one table needs to be purged and that table is parent table and it is having 5 child tables, so before deleting from parent we have to delete data from all 5 child tables.

To handle this scenario how can I design my control table.

For archive and purge the query like this.
INSERT INTO towner_name.ttable_name
(SELECT * FROM sowner_name.stable_name WHERE condition_column<=(sysdate-30));
DELETE FROM sowner_name.stable_name WHERE condition_column<=(sysdate-30);

for purge the quey is like this.
DELETE FROM sowner_name.stable_name WHERE condition_column<=(sysdate-30);

This is my control table and I have 300 tables list to archive and purge.

CID SOWNER_NAME STABLE_NAME TOWNER_NAME TTABLE_NAME CONDITION_COLUMN PERIOD UNIT TYPE
1 wedb_au OFFER_HEADER wedb_au OFFER_HEADER LAST_DATE 30 D A
1 wedb_sa OFFER_CUSTOMER wedb_sa OFFER_CUSTOMER LAST_DATE 60 D A
1 wedb_au OFFER_SERVICE LAST_DATE 1 Y P
1 wedb_us OFFER_CUSTOMER LAST_DATE 90 D P
1 wedb_cn OFFER_CARDS UPDATE_DT 2 Y P
2 wedb_au ORDER_HEAD wedb_au ORDER_HEAD LAST_DATE 120 D A
2 wedb_us ORDER_CUSTOMER wedb_us ORDER_CUSTOMER LAST_DATE 150 D A
2 wedb_sa ORDER_HEAD wedb_sa ORDER_HEAD CREATION_DT 1 Y A
3 wedb_us DELIVERY_HEAD wedb_us DELIVERY_HEAD UPDATE_DT 50 D A
3 wedb_au DELIVERY_CARDS wedb_au DELIVERY_CARDS UPDATE_DT 200 D A
3 wedb_au DELIVERY_SERVICE wedb_au DELIVERY_SERVICE LAST_DT 100 D A

WHERE TYPE=P means insert and delete
TYPE=A means only delete

wedb_au.OFFER_HEADER is Parent Table.
child tables for wedb_au.OFFER_HEADER are wedb_au.OFFER_SERVICE,wedb_au.OFFER_BODY,wedb_au.OFFER_EMAIL,OFFER_TAX.
wedb_au.OFFER_SERVICE is child table and parent for this table is wedb_au.OFFER_HEADER
wedb_sa.OFFER_CUSTOMER Stand alone table no relationship
wedb_us.OFFER_CUSTOMER Stand alone table no relationship
[code].......

View 2 Replies View Related

SQL & PL/SQL :: Delete The Duplicate Rows In A Table Without Using ROWID?

Mar 1, 2010

I want to delete the duplicate rows in a table without using ROWID.

I have the following data.

SNO SNAME SECTION
1 RAM A
2 SHYAM B
2 SHYAM B
3 KISHOR C
3 KISHOR D
4 RAMESH E
5 RAJESH F
5 RAJESH F
The Output Should be like this.

SNO SNAME SECTION
1 RAM A
2 SHYAM B
3 KISHOR C
3 KISHOR D
4 RAMESH E
5 RAJESH F

View 8 Replies View Related

SQL & PL/SQL :: Delete Records From Child And Parent Table

Jan 2, 2013

I want to delete records from parent table which are less than 2 years. Before deleting records from parent table we have to delete records from child table. How can we delete those records. I don't want to use ON DELETE CASCADE.

MASS_MASTER --parent table.
MASS_CHILD --child table.

The below query is used to delete records from parent table.

DELETE FROM mass_master WHERE last_date<=ADD_MONTHS(sysdate,-24);

The child table MASS_CHILD is not having last_date column. provide me the query to delete same records from child table.

View 21 Replies View Related







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