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.
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]....
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.
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 RelatedI 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 RelatedI 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]....
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
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.
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.
delete table if exist
How do we write this code in oracle / sqlplus
How to delete even rows of a table or rather alternate rows of a table.
View 31 Replies View RelatedDeclare
Cursor Cur_Emp Is
Select Empno,Ename
From Emp;
Type Type_Emp Is Table Of Cur_Emp%Rowtype Index By Binary_Integer;
[code].......
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 RelatedI 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.
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]....
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
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?
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
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.
how to delete Parent table records without affecting to child table dependent records?..
View 5 Replies View RelatedHow can i delete duplicate records from the table.
View 2 Replies View RelatedI am looking for a script to delete partition on table.
View 3 Replies View RelatedI 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]....
How can I delete a record simultaneously to table?
View 30 Replies View RelatedI 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.
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.
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
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].......
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
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.