Show all tables - how?

[BIS]

Hello again,
Another silly question probably. I have been doing a lot of testing lately, and have created quite a few test tables. Now I want to do some cleaning up. Is there a command I can use (in sqlplus?) to give me a list of all tables?

 

[babloosony]

you can use

1. select * from dba_objects
2. select * from user_objects
3. select * from all_tables
4. select * from dba_tables

 

[BIS]

Many thanks !

 

[rtefft]

If your test tables have something in common (like a TEST_ prefix), you can have SQL write your DROP code for you:

set pages 0
spool drop.sql

select 'drop table '||table_name||';'
from user_tables
where table_name like 'TEST_%';

spool off
(check drop.sql to make sure it only drops tables you want to get rid of, then...)
@drop.sql

____________________________
Rich Tefft
PL/SQL Programmer

 

[BIS]

Wow - thanks! They are actually all called Test_table, test1_table, test2_table etc.. This helps.

 

[rtefft]

BIS - I also have a script to destroy an entire schema (all objects in the schema but not the Oracle username itself) if you need it. It runs far faster than the "drop user" command if there are a lot of objects. It uses the same technique as above but for all objects. It generates the script, but does not run it (for safety). Here it is:

--  DESTROY_USER_OBJECTS.SQL   1/18/2001   R. Tefft
--  
--  This script generates a SQL script which will drop all database objects
--  owned by the specified user.  The resulting script must be executed manually
--  (for safety reasons).
--
column idx noprint
set pages 0 lines 100 verify off
spool destroy_&&owner._objects.sql

select 1 idx, 'alter table '||table_name||' drop constraint '||constraint_name||' cascade;'
from user_constraints where constraint_type='P'
union
select 2, 'truncate table '||object_name||';'
from user_objects
where object_type = 'TABLE'
union
select 3, 'drop '||object_type||' '||object_name||';'
from user_objects
where object_type not in ('INDEX', 'CONSTRAINT', 'PACKAGE BODY')
union
select 4, 'drop public synonym '||synonym_name||';'
from dba_synonyms
where owner='PUBLIC' and table_owner=upper('&&Owner')
order by 1, 2
/
spool off
prompt Destruction script is named destroy_&owner._objects.sql

____________________________
Rich Tefft
PL/SQL Programmer