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