|
|
| Re: how can i delete all tables in a user [message #7903 is a reply to message #7901] |
Wed, 16 July 2003 08:44   |
Geoffrey
Messages: 32
Registered: February 2002
|
Member |
|
|
you can create a script using this sql
SELECT
'drop ' || object_type || ' ' || object_name || decode(object_type,
'TABLE',
' CASCADE CONSTRAINTS') || ';'
FROM
dba_objects
WHERE
object_type = 'TABLE' and
owner = 'USERNAME'
|
|
|
|
|
|
|
|
| Re: how can i delete all tables in a user [message #7931 is a reply to message #7912] |
Thu, 17 July 2003 02:25 |
Rehman
Messages: 19
Registered: February 2000
|
Junior Member |
|
|
while iam in system/manger
-----------------------------
1 SELECT
2 'drop ' || object_type || ' ' || object_name || decode(object_type,
3 'TABLE',
4 ' CASCADE CONSTRAINTS') || ';'
5 FROM
6 dba_objects
7 WHERE
8 object_type = 'TABLE' and
9* owner = 'rehman'
SQL> /
no rows selected
-------------------------
In current User The result is
------------------------------------------------------------------------
'DROP'||OBJECT_TYPE||''||OBJECT_NAME||DECODE(OBJECT_TYPE,'TABLE','CASCADECONSTRA
--------------------------------------------------------------------------------
drop TABLE RRT CASCADE CONSTRAINTS;
drop TABLE RRTR CASCADE CONSTRAINTS;
drop TABLE TT CASCADE CONSTRAINTS;
tables are not delted
|
|
|
|
| Re: how can i delete all tables in a user [message #7949 is a reply to message #7931] |
Thu, 17 July 2003 18:10 |
 |
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You need to spool to a sql file, then start that file, so that the commands that you see are run and the tables are dropped:
STORE SET saved_settings REPLACE
SET ECHO OFF FEEDBACK OFF HEADING OFF PAGESIZE 0 SERVEROUTPUT ON VERIFY OFF WRAP ON
SPOOL drop_tables.sql
SELECT 'DROP ' || object_type || ' ' || object_name
|| DECODE (object_type, 'TABLE', ' CASCADE CONSTRAINTS' || ';'
FROM dba_objects WHERE object_type = 'TABLE' AND owner = 'rehman';
SPOOL OFF
START saved_settings.sql
START drop_tables.sql
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
