Home » SQL & PL/SQL » SQL & PL/SQL » to drop tables
to drop tables [message #220382] Tue, 20 February 2007 08:29 Go to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
hello team

i have run following query to know about the table.

select * from user_tables
order by created
/

OBJECT_NAME
------------------------------------------------------------------------------------------------
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE        CREATED   LAST_DDL_
------------------------------ ---------- -------------- ------------------ --------- ---------
TIMESTAMP           STATUS  T G S
------------------- ------- - - -
BONUS
                                    32122          32122 TABLE              04-SEP-01 04-SEP-01
2001-09-04:19:21:21 VALID   N N N

DEPT
                                    32118          32118 TABLE              04-SEP-01 16-FEB-07
2001-09-04:19:21:21 VALID   N N N

EMP
                                    32120          32120 TABLE              04-SEP-01 16-FEB-07
2001-09-04:19:21:21 VALID   N N N

SALGRADE
                                    32123          32123 TABLE              04-SEP-01 04-SEP-01
2001-09-04:19:21:22 VALID   N N N

EMP1
                                    32128          32128 TABLE              02-FEB-07 15-FEB-07
2007-02-02:22:28:43 VALID   N N N

EMP11
                                    32129                VIEW               02-FEB-07 14-FEB-07
2007-02-02:22:49:09 VALID   N N N

ACCOUNTS
                                    32130          32130 TABLE              02-FEB-07 03-FEB-07
2007-02-03:00:15:05 VALID   N N N

CIRCLE
                                    32137          32137 TABLE              03-FEB-07 03-FEB-07
2007-02-03:09:31:01 VALID   N N N

AREA
                                    32138          32138 TABLE              03-FEB-07 03-FEB-07
2007-02-03:09:36:16 VALID   N N N
SMP_VDJ_JOB_TARGET
                                    32382          32382 TABLE              18-FEB-07 18-FEB-07
2007-02-18:10:19:29 VALID   N N N

SMP_VDJ_JOB_OUTPUT
                                    32385          32385 TABLE              18-FEB-07 18-FEB-07
2007-02-18:10:19:29 VALID   N N N

SMP_VDJ_JOB_ID_SEQ
                                    32393                SEQUENCE           18-FEB-07 18-FEB-07
2007-02-18:10:19:30 VALID   N N N

SMP_VDJ_JOB_OUTPUT_SEQ
                                    32394                SEQUENCE           18-FEB-07 18-FEB-07
2007-02-18:10:19:30 VALID   N N N

SYS_C002845
                                    32390          32390 INDEX              18-FEB-07 18-FEB-07
2007-02-18:10:19:30 VALID   N Y N


i want to drop object_types which was created on 18-feb-07.

how can i do this?

i dnt want to drop these one by one.
simultaneously i want to drop.


regards
Re: to drop tables [message #220394 is a reply to message #220382] Tue, 20 February 2007 09:13 Go to previous messageGo to next message
pmaupoil
Messages: 40
Registered: February 2007
Location: France
Member
When you say "simultaneously", I take it as you want to delete them in one go but one after each other.

Why don't you try to generate some drop statements. Something like this (should work for most objects), spool it:

SELECT 'DROP ' || object_type || object_name || ';'
FROM user_objects uo
WHERE uo.created = to_date('18/02/07','dd/mm/yyyy');

And then run the output.
Re: to drop tables [message #220407 is a reply to message #220382] Tue, 20 February 2007 10:13 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
hi pmaupoil

can u tell me how to run it ?
while doing your provided queries i m getting
"no rows selected". if you dont mind, can u please
elaborate it properly.
i m facing problem while doing it.

thanx

regards
Re: to drop tables [message #220410 is a reply to message #220407] Tue, 20 February 2007 10:29 Go to previous messageGo to next message
pmaupoil
Messages: 40
Registered: February 2007
Location: France
Member
The idea is in sql plus, run:

spool test.sql

SELECT 'DROP ' || object_type || ' ' || object_name || ';'
FROM user_objects uo
WHERE trunc(uo.created) = to_date('18/02/2007','dd/mm/yyyy');

spool off

You end up with one file (test.sql) that contains all the drop statements.
Re: to drop tables [message #220412 is a reply to message #220407] Tue, 20 February 2007 10:37 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
"Pmau"

SQL> column object_name format a20
SQL> /

OBJECT_NAME          CREATED
-------------------- ---------
PK_DEPT              30-JUN-05
DEPT                 30-JUN-05
EMP                  30-JUN-05
PK_EMP               30-JUN-05
BONUS                30-JUN-05
SALGRADE             30-JUN-05
T1                   20-FEB-07
T2                   20-FEB-07

8 rows selected.

SQL> set heading off echo off feedback off
SQL> spool /home/drop.sql
SP2-0606: Cannot create SPOOL file "/home/drop.sql"
SQL> spool /home/oracle/drop.sql
SQL> select 'drop '||object_type||' '||object_name||';'
  2  from user_objects
  3  where trunc(created) = to_date('20/02/2007','dd/mm/yyyy');

drop TABLE T1;
drop TABLE T2;
SQL> spool off
SQL> @/home/oracle/drop.sql
SP2-0734: unknown command beginning "SQL> selec..." - rest of line ignored.
SP2-0734: unknown command beginning "SQL> spool..." - rest of line ignored.
SQL>
SQL> select object_name,created
  2  from user_objects
  3  /

OBJECT_NAME          CREATED
-------------------- ---------
PK_DEPT              30-JUN-05
DEPT                 30-JUN-05
EMP                  30-JUN-05
PK_EMP               30-JUN-05
BONUS                30-JUN-05
SALGRADE             30-JUN-05
BIN$KewFpnl41p3gQAB/ 20-FEB-07
AQAhVA==$0

BIN$KewFpnly1p3gQAB/ 20-FEB-07
AQAhVA==$0

OBJECT_NAME          CREATED
-------------------- ---------


8 rows selected.


Don't forget to add "PURGE" if you want to drop parmanently.


regards
Taj
Re: to drop tables [message #220420 is a reply to message #220382] Tue, 20 February 2007 10:52 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
thanx pmaupoil

u solved my problem.Cool

hello Mohammad Taj..
i m using 9.0.1.0 so i dnt need to use purge. drop can do the same.

thanx to u tooo
Re: to drop tables [message #220421 is a reply to message #220420] Tue, 20 February 2007 10:57 Go to previous message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
Actually i didn't notice your Oracle Version.

Flashback ( drop table ...purge) introduce in 10gr1.

regards
Taj
Previous Topic: Merging BLOBs
Next Topic: Correlated Subquery ?
Goto Forum:
  


Current Time: Thu Dec 08 06:07:18 CST 2016

Total time taken to generate the page: 0.05685 seconds