001
002
003
004
005
006
007
sqlplus
008
009
010
SET
HEAD
OFF
011
SPOOL /tmp/
database
/drop_tables.sql
012
013
014
015
select
'drop table '
|| table_name ||
';'
||chr(13)||chr(10)
from
user_tables;
016
017
select
'drop view '
|| view_name||
';'
||chr(13)||chr(10)
from
user_views;
018
019
020
021
select
'drop sequence '
|| sequence_name||
';'
||chr(13)||chr(10)
from
user_sequences;
022
023
024
025
select
'drop function '
|| object_name||
';'
||chr(13)||chr(10)
from
user_objects
where
object_type=
'FUNCTION'
;
026
027
028
029
select
'drop procedure '
|| object_name||
';'
||chr(13)||chr(10)
from
user_objects
where
object_type=
'PROCEDURE'
;
030
031
032
033
select
'drop package '
|| object_name||
';'
||chr(13)||chr(10)
from
user_objects
where
object_type=
'PACKAGE'
;
034
035
036
SELECT
'drop TRIGGER "'
||SYS_CONTEXT(
'USERENV'
,
'CURRENT_USER'
)||
'"."'
|| TRIGGER_NAME ||
'";'
||CHR(13) ||CHR(10)
FROM
USER_TRIGGERS
037
038
039
spool
off
;
040
@/tmp/
database
/drop_tables.sql;
041
042
purge recyclebin;
043
044
045
sqlplus
046
asus
047
asus
048
SET
HEAD
OFF
049
SPOOL c:/drop_tables.sql
050
051
052
053
select
'drop table '
|| table_name ||
';'
||chr(13)||chr(10)
from
user_tables;
054
055
select
'drop view '
|| view_name||
';'
||chr(13)||chr(10)
from
user_views;
056
057
select
'drop sequence '
|| sequence_name||
';'
||chr(13)||chr(10)
from
user_sequences;
058
059
select
'drop function '
|| object_name||
';'
||chr(13)||chr(10)
from
user_objects
where
object_type=
'FUNCTION'
;
060
061
select
'drop procedure '
|| object_name||
';'
||chr(13)||chr(10)
from
user_objects
where
object_type=
'PROCEDURE'
;
062
063
select
'drop package '
|| object_name||
';'
||chr(13)||chr(10)
from
user_objects
where
object_type=
'PACKAGE'
;
064
065
SELECT
'drop TRIGGER "'
||SYS_CONTEXT(
'USERENV'
,
'CURRENT_USER'
)||
'"."'
|| TRIGGER_NAME ||
'";'
||CHR(13) ||CHR(10)
FROM
USER_TRIGGERS
066
067
spool
off
;
068
@c:/drop_tables.sql;
069
purge recyclebin;
070
quit
071
072
073
074
075
076
077
078
079
080
081
1
select
Drop
table
||table_name||;
082
from
all_tables
083
where
owner=要删除的用户名(注意要大写);
084
2、
085
删除所有表
086
以用户test为例
087
for
example:
088
declare
089
cursor
cur1
is
select
table_name
from
dba_tables
where
owner=TEST;
090
begin
091
for
cur2
in
cur1 loop
092
execute
immediate
drop
table
test.||cur2.table_name;
093
end
loop;
094
end
;
095
3、这个删除当前用户的所有对象(表、视图、触发器、存储过程、函数)
096
DECLARE
097
TYPE name_list
IS
TABLE
OF
VARCHAR2(40);
098
TYPE type_list
IS
TABLE
OF
VARCHAR2(20);
099
Tab_name name_list:=name_list();
100
Tab_type type_list:=type_list();
101
sql_str VARCHAR2(500);
102
BEGIN
103
sql_str :=
select
uo.object_name,uo.object_type
from
user_objects uo
where
uo.object_type
not
in
(
INDEX
,LOB)
order
by
uo.object_type
desc
;
104
EXECUTE
IMMEDIATE sql_str BULK COLLECT
INTO
tab_name,tab_type;
105
FOR
i
IN
Tab_name.
FIRST
.. Tab_name.
LAST
LOOP
106
sql_str :=
DROP
|| Tab_type(i) || || Tab_name(i);
107
EXECUTE
IMMEDIATE sql_str;
108
END
LOOP;
109
END
;
110
111
112
113
114
115
116
117
118