A reader, February 27, 2006 - 12:09 pm UTC
Nice.
Joe Fabiano, March 04, 2006 - 4:42 pm UTC
Thank you. I'm trying to take your solution and place it into a procedure so the application can provide a username dynamically. Below you'll see my many attempts. I can't get past the following error:
CREATE OR REPLACE procedure drop_user(u_name in varchar2) is
-- exec dbms_job.submit( :x, 'execute immediate ''drop user x1 cascade'';' );
job_no number;
user_exists varchar2(30);
drop_stmt varchar2(500) := 'drop user'||u_name||' cascade';
plsql_block varchar2(500);
lock_stmt varchar2(500) := 'alter user '||u_name||' account lock';
d_u_name varchar2(30) := u_name;
begin
SELECT USERNAME INTO USER_EXISTS FROM DBA_USERS
WHERE USERNAME = u_name;
plsql_block :='DECLARE X NUMBER; BEGIN dbms_job.submit(X,''execute immediate ''''DROP USER :1
CASCADE'''';''); END;';
DBMS_OUTPUT.PUT_LINE(plsql_block);
EXECUTE IMMEDIATE PLSQL_BLOCK USING D_U_NAME;
COMMIT;
--dbms_job.submit(job_no,'execute immediate ''drop user :uid cascade'' USING d_u_name;');
--dbms_job.submit(job_no,'execute immediate drop_stmt;');
dbms_output.put_line(job_no);
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
/
SQL>EXECUTE DROP_USER('X1');
DECLARE X NUMBER; BEGIN dbms_job.submit(X,'execute immediate ''DROP USER :1
CASCADE'';'); END;
BEGIN DROP_USER('X1'); END;
*
ERROR at line 1:
ORA-01006: bind variable does not exist
ORA-06512: at "JOE.DROP_USER", line 22
ORA-06512: at line 1
March 05, 2006 - 1:32 pm UTC
you cannot bind DDL, you have to concatenate (so be very very careful about accepting inputs from users - watch out for SQL Injection!)
Joe Fabiano, March 04, 2006 - 9:05 pm UTC
I couldn't submit the job with an EXECUTE IMMEDIATE clause and a parameter. I was able to get it to work using an alternative method:
CREATE OR REPLACE procedure drop_schema(uid in varchar2) is
begin
execute immediate 'drop user '||uid||' cascade';
end;
/
CREATE OR REPLACE procedure drop_user(u_name in varchar2) is
job_no number;
user_exists varchar2(30);
lock_stmt varchar2(500) := 'alter user '||u_name||' account lock';
d_u_name varchar2(30) := u_name;
begin
SELECT USERNAME INTO USER_EXISTS FROM DBA_USERS
WHERE USERNAME = u_name;
execute immediate lock_stmt;
dbms_job.submit(job_no,'drop_schema(''' || d_u_name || ''');');
commit;
dbms_output.put_line(job_no);
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
/
March 05, 2006 - 1:34 pm UTC
this looks OK (watch the permissions on drop_schema - maybe add the username check in there to avoid any chance of sql injection as well)
Not a fan of the "no_data_found then null" - seems like it should be an ERROR, you just requested to drop a user that doesn't exist and the invoking routine will *never know*. It'll think "everything is OKEY DOKEY, the requested user will be dropped"
Also might want to validate that the user to be dropped is one that should be (eg: not system for example :)
"Clear schema"
A reader, June 23, 2006 - 9:39 am UTC
Tom,
I am searching asktom for the script to delete all objects in the user schema and leave in place all privileges/roles granted to him that are out of scope of his own schema (drop all objects from user_objects for the current user)
Do you already have such script?
June 23, 2006 - 10:26 am UTC
nope, you would just loop over things and issue the drop or drop cascade constraints...
Drop all objects
Michel Cadot, June 23, 2006 - 10:47 am UTC
Hi,
I wrote one (and called it delMe.sql) to drop all my objects.
Very useful when you make many and many tests to recover a clean environment. :)
set heading off
set pagesize 0
set feedback off
set timing off
set time off
set trimspool on
set trimout on
set linesize 100
set echo off
spool t
select 'drop '||object_type||' '||object_name||
decode(object_type,'CLUSTER',' including tables cascade constraints',
'TABLE',' cascade constraints',
'')||';'
from user_objects
where object_type in ('CLUSTER','CONTEXT','DATABASE LINK','DIMENSION',
'DIRECTORY','FUNCTION','INDEX TYPE',
'JAVA','LIBRARY','MATERIALIZED VIEW','OPERATOR',
'OUTLINE','PACKAGE','PROCEDURE','SEQUENCE',
'SYNONYM','TABLE','TYPE','VIEW')
order by object_type, object_name
/
spool off
@t.lst
Regards
Michel