Testato su Oracle 12c. Cancellare un tablespace di un pluggable database e recuperarlo. SQL> show pdbs;     CON_ID CON_NAME                       OPEN MODE  RESTRICTED ---------- ------------------------------ ---------- ----------          2 PDB$SEED                       READ ONLY  NO          3 S12AVA1                        READ WRITE NO          4 S12AVA1_CLONE                  READ WRITE NO SQL> alter session set container=s12ava1; Session altered. SQL> create tablespace tbs_dc datafile '/u01/app/oracle/oradata/CDB12S01/s12ava1/tbs_dc0101' size 500M autoextend on next 50 M maxsize 1024M extent management local autoallocate blocksize 8k segment space management auto; /  Tablespace created. SQL> create user bruce identified by bruce default tablespace tbs_dc account unlock temporary tablespace temp quota unlimited on tbs_dc profile default; User created. SQL> grant connect  to bruce; Grant succeeded. SQL> alter s

Oracle 12c Supponiamo di voler aprire un pluggable database e termina in errore. SQL> alter pluggable database S12AVA1 open; ERROR at line 1: ORA-01113: file 9 needs media recovery ORA-01110: data file 9: '/u01/app/oracle/oradata/CDB12S01/s12ava1/system01.dbf' SQL> recover datafile 9; ORA-00279: change 3747608 generated at 12/17/2018 10:54:49 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/archive/CDB12S01/1_92_993571574.dbf ORA-00280: change 3747608 for thread 1 is in sequence #92 Controllare se la sequence 92 e' presente nel Backup, o in locale o nelle directory dei redolog file. Aprire un secondo terminale: RMAN>  list backup of archivelog all; starting full resync of recovery catalog full resync complete List of Backup Sets =================== BS Key  Size       Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 98      105.00M    SBT_TAPE    00:00:05     28-NOV-18         BP Key:

-- Query individuazione dei ruoli select * from dba_roles -- Query utente select * from dba_users   where       username like upper('%&username%') -- Query ruoli associati ad un utente select  *   from  dba_role_privs   where upper (grantee) like upper('%&username%'); Grantee è il nome dell'utente che riceve le grant -- Query privilegi di sistema associat ai ruoli select * from DBA_SYS_PRIVS  where upper (grantee)  in ( 'ROLE_OWNER_SCHEMA', 'ROLE_READ_ONLY','ROLE_READ_WRITE') SELECT * FROM ROLE_SYS_PRIVS  where lower(role) in  ('role_manager','role_submanager'); -- Query privilegi sugli oggetti associat ai ruoli select * from role_tab_privs  where role like 'R_FISC2020_ALL%' -- Query oggetti associat ai ruoli select * from  dba_tab_privs  where granteee like 'R_FISC2020_ALL%' Grantee è il nome dell'utente che riceve le grant SELECT   A.GRANTEE AS UTENTE,          A.GRANTED_ROLE AS RUO

Accedere come SYS e creare un user di esempio. create user tizio identified by tizio default tablespace USERS temporary tablespace TEMP PROFILE DEFAULT account unlock; grant connect to tizio; Di seguito la procedura --script_grant_to_all_objects.sql set serveroutput on spool log_script_grant.log prompt ============================ prompt Start script prompt ============================ BEGIN FOR cur IN ( select owner, object_name FROM all_objects where object_type in ('VIEW', 'TABLE','MATERIALIZED VIEW') and owner not in ( select username from dba_users where default_tablespace IN ('SYSAUX', 'TOOLS','TEMP','SYSTEM','CARTPLSQL','DRSYS','XDB')) ) LOOP DBMS_OUTPUT.put_line ('GRANT SELECT ON ' || cur.OWNER || '.' || cur.OBJECT_NAME || ' TO tizio'); EXECUTE IMMED