Friday, June 24, 2011

Getting a user's DDL

If you want to copy an user and you don't have a frog, then you could use the DBMS_METADATA package to get the DLL you need to recreate it:

SQL> exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR', true);

PL/SQL procedure successfully completed.

SQL> SET PAGESIZE 0
SQL> SET LONG 90000
SQL> SELECT DBMS_METADATA.GET_DDL('USER',username) from dba_users where username='MYUSER';

CREATE USER "MYUSER" IDENTIFIED BY VALUES '123456B563D86FE2'
DEFAULT TABLESPACE "MYTABLESPACE"
TEMPORARY TABLESPACE "TEMP"
PROFILE "MYPROFILE";


SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',GRANTEE) FROM DBA_ROLE_PRIVS
WHERE GRANTEE='MYUSER' group by GRANTEE;

GRANT "MYROLE" TO "MYUSER";


SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('DEFAULT_ROLE','MYUSER') from dual;

ALTER USER "MYUSER" DEFAULT ROLE ALL;


SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',GRANTEE) FROM DBA_SYS_PRIVS
WHERE GRANTEE='MYUSER' group by GRANTEE;

GRANT CREATE TABLE TO "MYUSER";


SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',GRANTEE) FROM DBA_TAB_PRIVS
WHERE GRANTEE='MXS320A' group by GRANTEE;

no rows selected

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA','MYUSER') from dual;

DECLARE
TEMP_COUNT NUMBER;
SQLSTR VARCHAR2(200);
BEGIN
SQLSTR := 'ALTER USER "MYUSER" QUOTA UNLIMITED ON "MYTABLESPACE"';
EXECUTE IMMEDIATE SQLSTR;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -30041 THEN
SQLSTR := 'SELECT COUNT(*) FROM USER_TABLESPACES
WHERE TABLESPACE_NAME = ''MYTABLESPACE'' AND CONTENTS = ''TEMPORARY''';
EXECUTE IMMEDIATE SQLSTR INTO TEMP_COUNT;
IF TEMP_COUNT = 1 THEN RETURN;
ELSE RAISE;
END IF;
ELSE
RAISE;
END IF;
END;
/

DECLARE
TEMP_COUNT NUMBER;
SQLSTR VARCHAR2(200);
BEGIN
SQLSTR := 'ALTER USER "MYUSER" QUOTA 10485760 ON "SYSTEM"';
EXECUTE IMMEDIATE SQLSTR;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -30041 THEN
SQLSTR := 'SELECT COUNT(*) FROM USER_TABLESPACES
WHERE TABLESPACE_NAME = ''SYSTEM'' AND CONTENTS = ''TEMPORARY''';
EXECUTE IMMEDIATE SQLSTR INTO TEMP_COUNT;
IF TEMP_COUNT = 1 THEN RETURN;
ELSE RAISE;
END IF;
ELSE
RAISE;
END IF;
END;
/

As you can see, you don't get simple ALTER USER QUOTA sentences with the TABLESPACE_QUOTA option so you can execute the entire PL/SQL block or just pick up the ALTER USER QUOTA sentences.

More information:

Copying Oracle Users
57 DBMS_METADATA

No comments:

Post a Comment