Deleting all tables in one shot 2

[cyberdyne]

Hi there! I have some questions for which i need some help.

1. How do i delete all the tables in database with one /
some command ? I dont want to delete each table by
giving seperate command. I hv some 78 tables.

2. How do I view userlist ?

3. How do i delete a user ?

 

[sem]

1. You may drop schema owner.
2. select username from all_users
3. drop user <username> [cascade]

But you'd better read just any book/documentation on Oracle

 

[cyberdyne]

Thank u sem. it worked.
I am new to oracle. Ty for suggesions.

Also can u pls reply my remaining queries ? that will of great help.

 

[sem]

What queries? I suppose I've answered all...
Maybe the 2nd, if you asked about the users currently connectd, try to select from v$sessions.

 

[cyberdyne]

sem first one .

here it is again

1. How do i delete all the tables in database with one or
few commands ? I dont want to delete each table by
giving seperate command. I hv some 78 tables so can
understand how tiering it can be.

 

[carp]

You can use SQL to write a SQL that will drop the tables while you drink the adult beverage of your choice:

SQL> SELECT 'DROP TABLE '||owner||'.'||table_name||' CASCADE CONSTRAINTS;' FROM dba_tables WHERE owner NOT IN ('SYS','SYSTEM');
SQL> SPOOL DROPPER
SQL> /
SQL> SPOOL OFF
SQL> @dropper.lst

A word of caution - you might want to review the dropper.lst to make sure you are not dropping tables you really want to keep file before you run the script.

 

[karluk]

In my opinion, it should be automatic to run an export, or some other form of database backup, prior to dropping the tables. You don't want to risk transforming routine maintenance into an unrecoverable disaster.

 

[carp]

Absolutely!!