SET LINESIZE 120

SET SQLN OFF
SET VERIFY OFF

SET FEEDBACK OFF
SET TRIMSPOOL ON

DEFINE USUARIO     = ' '
DEFINE BASEDEDATOS     = ' '
DEFINE SALIDA     = ' '
COLUMN AUSUARIO NOPRINT NEW_VALUE USUARIO
COLUMN ADATABASE NOPRINT NEW_VALUE BASEDEDATOS
COLUMN ASALIDA NOPRINT NEW_VALUE SALIDA

SELECT TRIM(UPPER('&1')) AUSUARIO FROM DUAL
/
SELECT TRIM(NAME) ADATABASE FROM V$DATABASE
/

SELECT LOWER('&BASEDEDATOS')||'_'||LOWER('&USUARIO') ASALIDA FROM DUAL
/



-- CUOTAS
COLUMN USERNAME FORMAT A30
COLUMN TABLESPACE_NAME FORMAT A30
COLUMN MB FORMAT 999999999,99
COLUMN CUOTA FORMAT A30

SET HEAD ON

SPOOL /tmp/cuotas\_&SALIDA\.log

SELECT USERNAME,TABLESPACE_NAME,ROUND(BYTES/1024/1204,2) MB, DECODE (MAX_BYTES,'-1',' ILIMITADA') CUOTA
FROM DBA_TS_QUOTAS
WHERE USERNAME = ('&USUARIO')
/

SPOOL OFF;

SET HEAD OFF;

SPOOL /tmp/roles\_&SALIDA\.sql

-- ROLES

SELECT 'CREATE ROLE '||ROLE||DECODE (R.PASSWORD_REQUIRED,'YES',' IDENTIFIED BY VALUES '''||PASSWORD||''';',' NOT IDENTIFIED;')
FROM SYS.DBA_ROLES R,SYS.USER$ U
WHERE R.ROLE IN (
	SELECT GRANTED_ROLE
	FROM DBA_ROLE_PRIVS
	WHERE GRANTEE  = ('&USUARIO')
	)
AND R.ROLE=U.NAME
UNION
SELECT 'CREATE ROLE '||ROLE||DECODE (R.PASSWORD_REQUIRED,'YES',' IDENTIFIED BY VALUES '''||PASSWORD||''';',' NOT IDENTIFIED;')
FROM SYS.DBA_ROLES R,SYS.USER$ U
WHERE R.ROLE IN (
	SELECT DISTINCT GRANTEE
	FROM DBA_TAB_PRIVS
	WHERE OWNER = ('&USUARIO')
	)
AND R.ROLE=U.NAME
UNION
SELECT 'GRANT  '||GRANTED_ROLE||' TO '||GRANTEE||DECODE (ADMIN_OPTION,'YES',' WITH ADMIN OPTION;',';')
FROM DBA_ROLE_PRIVS
WHERE GRANTEE = ('&USUARIO')
/


SPOOL OFF;


SPOOL /tmp/sinonimos\_&SALIDA\.sql

SELECT 'CREATE OR REPLACE '||DECODE (OWNER,'PUBLIC','PUBLIC SYNONYM ','SYNONYM '||OWNER||'.')||SYNONYM_NAME||' FOR '||TABLE_OWNER||'.'||TABLE_NAME||';'
FROM DBA_SYNONYMS
WHERE TABLE_OWNER = ('&USUARIO')
OR OWNER = ('&USUARIO')
/

SPOOL OFF;