Consultas SQL útiles para obtener información sobre Oracle Database

Consultas SQL útiles para obtener información sobre Oracle Database

• Vista que muestra el estado de la base de datos:

select * from v$instance

• Consulta que muestra si la base de datos está abierta:

select status from v$instance

• Vista que muestra los parámetros generales de Oracle:

select * from v$system_parameter

• Versión de Oracle:

select value 
from v$system_parameter 
where name = 'compatible'

• Ubicación y nombre del fichero spfile:

select value 
from v$system_parameter 
where name = 'spfile'

• Ubicación y número de ficheros de control:

select value 
from v$system_parameter 
where name = 'control_files'

• Nombre de la base de datos

select value 
from v$system_parameter 
where name = 'db_name'

• Vista que muestra las conexiones actuales a Oracle:

select osuser, username, machine, program 
  from v$session 
  order by osuser

• Vista que muestra el número de conexiones actuales a Oracle agrupado por aplicación que realiza la conexión

select program Aplicacion, count(program) Numero_Sesiones
from v$session
group by program 
order by Numero_Sesiones desc

• Vista que muestra los usuarios de Oracle conectados y el número de sesiones por usuario

select username Usuario_Oracle, count(username) Numero_Sesiones
from v$session
group by username
order by Numero_Sesiones desc

• Propietarios de objetos y número de objetos por propietario

select owner, count(owner) Numero 
  from dba_objects 
  group by owner 
  order by Numero desc

• Diccionario de datos (incluye todas las vistas y tablas de la Base de Datos):

select * from dictionary



select table_name from dictionary
  

• Muestra los datos de una tabla especificada (en este caso todas las tablas que lleven la cadena "EMPLO"):

select * 
from ALL_ALL_TABLES 
where upper(table_name) like '%EMPLO%'

• Muestra los disparadores (triggers) de la base de datos Oracle Database:

select *
from ALL_TRIGGERS 

• Tablas propiedad del usuario actual:

select * from user_tables

• Todos los objetos propiedad del usuario conectado a Oracle:

select * from user_catalog

• Consulta SQL para el DBA de Oracle que muestra los tablespaces, el espacio utilizado, el espacio libre y los ficheros de datos de los mismos:

Select t.tablespace_name  "Tablespace",  t.status "Estado",  
    ROUND(MAX(d.bytes)/1024/1024,2) "MB Tamaño",
    ROUND((MAX(d.bytes)/1024/1024) - 
    (SUM(decode(f.bytes, NULL,0, f.bytes))/1024/1024),2) "MB Usados",   
    ROUND(SUM(decode(f.bytes, NULL,0, f.bytes))/1024/1024,2) "MB Libres", 
    t.pct_increase "% incremento", 
    SUBSTR(d.file_name,1,80) "Fichero de datos"  
FROM DBA_FREE_SPACE f, DBA_DATA_FILES d,  DBA_TABLESPACES t  
WHERE t.tablespace_name = d.tablespace_name  AND 
    f.tablespace_name(+) = d.tablespace_name    
    AND f.file_id(+) = d.file_id GROUP BY t.tablespace_name,   
    d.file_name,   t.pct_increase, t.status ORDER BY 1,3 DESC

• Productos Oracle instalados y la versión:

select * from product_component_version 

• Roles y privilegios por roles:

select * from role_sys_privs

• Reglas de integridad y columna a la que afectan:

select constraint_name, column_name 
from sys.all_cons_columns

• Tablas de las que es propietario un usuario, en este caso "HR":

SELECT table_owner, table_name 
from sys.all_synonyms 
where table_owner like 'HR'

• Otra forma más efectiva (tablas de las que es propietario un usuario):

SELECT DISTINCT TABLE_NAME 
FROM ALL_ALL_TABLES 
WHERE OWNER LIKE 'HR' 

• Parámetros de Oracle, valor actual y su descripción:

SELECT v.name, v.value value, decode(ISSYS_MODIFIABLE, 'DEFERRED', 
     'TRUE', 'FALSE') ISSYS_MODIFIABLE,  decode(v.isDefault, 'TRUE', 'YES',
     'FALSE', 'NO') "DEFAULT",  DECODE(ISSES_MODIFIABLE,  'IMMEDIATE',  
     'YES','FALSE',  'NO',  'DEFERRED', 'NO', 'YES') SES_MODIFIABLE,   
     DECODE(ISSYS_MODIFIABLE, 'IMMEDIATE', 'YES',  'FALSE', 'NO',  
     'DEFERRED', 'YES','YES') SYS_MODIFIABLE ,  v.description  
FROM V$PARAMETER v 
WHERE name not like 'nls%'   ORDER BY 1
  

• Usuarios de Oracle y todos sus datos (fecha de creación, estado, id, nombre, tablespace temporal,...):

Select  * FROM dba_users

• Tablespaces y propietarios de los mismos:

select owner, decode(partition_name, null, segment_name, 
   segment_name || ':' || partition_name) name, 
   segment_type, tablespace_name,bytes,initial_extent, 
   next_extent, PCT_INCREASE, extents, max_extents 
from dba_segments 
Where 1=1 And extents > 1 order by 9 desc, 3 

• Últimas consultas SQL ejecutadas en Oracle y usuario que las ejecutó:

select distinct vs.sql_text, vs.sharable_mem, 
  vs.persistent_mem, vs.runtime_mem,  vs.sorts,
  vs.executions, vs.parse_calls, vs.module,  
  vs.buffer_gets, vs.disk_reads, vs.version_count, 
  vs.users_opening, vs.loads,  
  to_char(to_date(vs.first_load_time,
  'YYYY-MM-DD/HH24:MI:SS'),'MM/DD  HH24:MI:SS') first_load_time,  
  rawtohex(vs.address) address, vs.hash_value hash_value , 
  rows_processed  , vs.command_type, vs.parsing_user_id  , 
  OPTIMIZER_MODE  , au.USERNAME parseuser  
from v$sqlarea vs , all_users au   
where (parsing_user_id != 0)  AND 
(au.user_id(+)=vs.parsing_user_id)  
and (executions >= 1) order by   buffer_gets/executions desc 

• Todos los ficheros de datos y su ubicación:

select * from V$DATAFILE

• Ficheros temporales:

select * from V$TEMPFILE

• Tablespaces:

select * from V$TABLESPACE

• Otras vistas muy interesantes:

select * from V$BACKUP

select * from V$ARCHIVE   

select * from V$LOG   

select * from V$LOGFILE    

select * from V$LOGHIST          

select * from V$ARCHIVED_LOG    

select * from V$DATABASE

• Memoria Share_Pool libre y usada:

select name,to_number(value) bytes 
from v$parameter where name ='shared_pool_size'
union all
select name,bytes 
from v$sgastat where pool = 'shared pool' and name = 'free memory'

          select * from v$sgastat where name='free memory'; 

• Cursores abiertos por usuario:

select b.sid, a.username, b.value Cursores_Abiertos
      from v$session a,
           v$sesstat b,
           v$statname c
      where c.name in ('opened cursors current')
      and   b.statistic# = c.statistic#
      and   a.sid = b.sid 
      and   a.username is not null
      and   b.value >0
      order by 3

• Aciertos de la caché (no debe superar el 1 por ciento):

select sum(pins) Ejecuciones, sum(reloads) Fallos_cache,
  trunc(sum(reloads)/sum(pins)*100,2) Porcentaje_aciertos 
from v$librarycache
where namespace in ('TABLE/PROCEDURE','SQL AREA','BODY','TRIGGER');

• Sentencias SQL completas ejecutadas con un texto determinado en el SQL:

SELECT c.sid, d.piece, c.serial#, c.username, d.sql_text 
FROM v$session c, v$sqltext d 
WHERE  c.sql_hash_value = d.hash_value 
  and upper(d.sql_text) like '%WHERE CAMPO LIKE%'
ORDER BY c.sid, d.piece

• Una sentencia SQL concreta (filtrado por sid):

SELECT c.sid, d.piece, c.serial#, c.username, d.sql_text 
FROM v$session c, v$sqltext d 
WHERE  c.sql_hash_value = d.hash_value and sid = 105
ORDER BY c.sid, d.piece

• Tamaño ocupado por la base de datos

select sum(BYTES)/1024/1024 MB 
from DBA_EXTENTS  

• Tamaño de los ficheros de datos de la base de datos:

select sum(bytes)/1024/1024 MB 
from dba_data_files

• Tamaño ocupado por una tabla concreta sin incluir los índices de la misma

select sum(bytes)/1024/1024 MB 
from user_segments
where segment_type='TABLE' and segment_name='NOMBRETABLA'

• Tamaño ocupado por una tabla concreta incluyendo los índices de la misma

select sum(bytes)/1024/1024 Table_Allocation_MB 
from user_segments
where segment_type in ('TABLE','INDEX') and
  (segment_name='NOMBRETABLA' or segment_name in
    (select index_name 
     from user_indexes 
     where table_name='NOMBRETABLA'))

• Tamaño ocupado por una columna de una tabla:

select sum(vsize('NOMBRECOLUMNA'))/1024/1024 MB 
from NOMBRETABLA

• Espacio ocupado por usuario:

SELECT owner, SUM(BYTES)/1024/1024 
FROM DBA_EXTENTS MB
GROUP BY owner

• Espacio ocupado por los diferentes segmentos (tablas, índices, undo, rollback, cluster, ...):

SELECT SEGMENT_TYPE, SUM(BYTES)/1024/1024 
FROM DBA_EXTENTS MB
GROUP BY SEGMENT_TYPE

• Espacio ocupado por todos los objetos de la base de datos, muestra los objetos que más ocupan primero:

SELECT SEGMENT_NAME, SUM(BYTES)/1024/1024 
FROM DBA_EXTENTS MB
GROUP BY SEGMENT_NAME
ORDER BY 2 DESC

• Obtener todas las funciones de Oracle: NVL, ABS, LTRIM, ...:

SELECT distinct object_name 
FROM all_arguments 
WHERE package_name = 'STANDARD'
order by object_name

• Obtener los roles existentes en Oracle Database:

select * from DBA_ROLES

• Obtener los privilegios otorgados a un rol de Oracle:

select privilege 
from dba_sys_privs 
where grantee = 'NOMBRE_ROL'

• Obtener la IP del servidor de la base de datos Oracle Database:

select utl_inaddr.get_host_address IP
from dual 

• 
  Mostrar datos de auditoría de la base de datos Oracle (inicio y desconexión de sesiones):

select username, action_name, priv_used, returncode
from dba_audit_trail

• 
  Comprobar si la auditoría de la base de datos Oracle está activada:

select name, value
from v$parameter
where name like 'audit_trail'

BITCOIN HALVING

HALVING 

# HALVING	# BLOQUE	RECOMPENSA ACTIVA	# BTC INTRODUCIDO	AÑO
0	0	50	10500000,00000000	2008
1	210000	25	5250000,00000000	2012
2	420000	12,5	2625000,00000000	2016
3	630000	6,25	1312500,00000000	2020
4	840000	3,125	656250,00000000	2024
5	1050000	1,5625	328125,00000000	2028
10	2100000	0,04882813	10253,90625000	2048
15	3150000	0,00152588	320,43457031	2068
20	4200000	0,00004768	10,01358032	2088
25	5250000	0,00000149	0,31292439	2108
30	6300000	0,00000005	0,00977889	2128
34	7140000	0,00000000	0,00061118	2144
		TOTAL DE BITCOINS	20.999.999,99938880

DELETE INCIDENTES

Si deseamos borrar todo el historial de notificaciones:
SQL> begin
2 delete from mgmt_current_severity;
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.

begin
delete from mgmt_current_severity;
end;

The Driving Table

The table of interest in the SYSMAN schema is named MGMT_SEVERITY.

USER is "SYSTEM"
SQL> conn sysman/oracle
Connected.
USER is "SYSMAN"
SQL> desc mgmt_severity
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------
 TARGET_GUID                               NOT NULL RAW(16)
 METRIC_GUID                               NOT NULL RAW(16)
 KEY_VALUE                                 NOT NULL VARCHAR2(256)
 COLLECTION_TIMESTAMP                      NOT NULL DATE
 LOAD_TIMESTAMP                                     DATE
 SEVERITY_CODE                             NOT NULL NUMBER
 SEVERITY_TYPE                                      NUMBER
 SEVERITY_DURATION                                  NUMBER
 SEVERITY_GUID                                      RAW(16)
 ANNOTATED_FLAG                                     NUMBER
 NOTIFICATION_STATUS                                NUMBER
 MESSAGE                                            VARCHAR2(4000)
 MESSAGE_NLSID                                      VARCHAR2(64)
 MESSAGE_PARAMS                                     VARCHAR2(4000)
 ACTION_MESSAGE                                     VARCHAR2(4000)
 ACTION_NLSID                                       VARCHAR2(64)
 ACTION_MESSAGE_PARAMS                              VARCHAR2(4000)
 ADVISORY_ID                                        VARCHAR2(64)
 USER_NAME                                          VARCHAR2(64)

Aside from the RAW datatypes, working with data in this table should be fairly straightforward, that is, once you know what to look for. The table’s primary key is a composite key, and uses TARGET_GUID, METRIC_GUID, KEY_VALUE, COLLECTION_TIMESTAMP, and SEVERITY_CODE.

You won’t find the RAW values anywhere on the page, even if you look at the page’s source. If you try that approach, mainly as an exercise to see what the underlying HTML code looks like, all you’re going to see (at the bottom of the text editor page) are a few lines of code, with one of them stretching out well over 30,000 characters.

The two best telltales in the table are KEY_VALUE and COLLECTION_TIMESTAMP. The KEY_VALUE values have a pattern to them. If the message is about a tablespace having crossed a space threshold, then the KEY_VALUE is simply the name of the tablespace. If the message is about SYS having logged on, the KEY_VALUE will be a mixture of “SYS” plus “_.” The recovery area message is identified by a value of RECOVERY AREA. The blocking message starts with SID, and the invalid objects in a schema begins with the schema name.

For the most part, the date and time shown under Alert Triggered will appear as the value for COLLECTION_TIMESTAMP. Between that time and the “key values” in the KEY_VALUE column, you should be sufficiently armed to invoke a procedure named DELETE_CURRENT_SEVERITY in the EM_SEVERITY package.

PROCEDURE DELETE_CURRENT_SEVERITY
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_TARGET_GUID                  RAW                     IN
 P_METRIC_GUID                  RAW                     IN
 P_KEY_VALUE                    VARCHAR2                IN

This procedure deletes data from a table named MGMT_CURRENT_SEVERITY.

  BEGIN
    DELETE FROM MGMT_CURRENT_SEVERITY
     WHERE target_guid = p_target_guid
       AND metric_guid = p_metric_guid
       AND key_value = p_key_value;
  END delete_current_severity;

On the face of it, this approach seems like a safe way to go about clearing stale messages, but there is a better way, and that way is to delete directly from the MGMT_SEVERITY table and let the SEVERITY_DELETE trigger do all the work for you.

PRIVILEGIOS PLANES DE EJECUCION PLAN TABLE

 Rol SELECT_CATALOG_ROLE:

SQL> grant select_catalog_role to user1;

Grant succeeded.

Privilegio SELECT ANY DICTIONARY:

SQL> grant select any dictionary to user1;

Grant succeeded.

trcsess and tkprof.

 trcsess output=orcl_trace.txt service=orcl orcl_ora_4143.trc orcl_ora_4186.trc orcl_ora_4196.trc

$ tkprof orcl_trace.trc resultado.txt sys=no

$ tkprof orcl_trace.trc resultado.txt sys=no sort=execpu

tkprof orcl_trace.trc resultado.txt sys=no sort=exeela

tkprof orcl_ora_4196.trc resultado.txt sys=no

ACTIVAR TRACE 

EXEC  DBMS_MONITOR.session_trace_enable(waits=>TRUE, binds=>TRUE);
EXEC  DBMS_MONITOR.session_trace_disable;

VISTA VER TRACES DBA_EANBLED_TRACES

RMAN LIST BACKUP ARCHIVELOG

 RMAN>

RMAN> list backup of archivelog logseq=120316;
RMAN> list backup of archivelog logseq 120316;
RMAN> list backup of archivelog sequence 120316;

To view backups of archivelog between two sequences:

RMAN> list backup of archivelog sequence between 120316 and 120317;
RMAN> list backup of archivelog from logseq 412593 until logseq 412656;

Use the SUMMARY directive to view only the backupsets affected:

RMAN> list backup of archivelog from logseq 412593 until logseq 412656 summary;
RMAN> list backup of archivelog sequence between 120316 and 120317 summary;

To view backups completed before a specific date:

RMAN> list backup completed before "to_date( '18.12.2009 18:00:00', 'DD.MM.YYYY HH24:MI:SS')";
RMAN> list backup of archivelog all summary completed before "to_date( '18.12.2009 18:00:00', 'DD.MM.YYYY HH24:MI:SS')";

To list archivelogs recognized by the controlfile:

list archivelog sequence between 110880 and 110881;

VISTAS METRICAS ALERTAS ALERT_HISTORY

 VISTA

select * from dba_alert_history

select * from dba_outstanding_alerts

select * from dba_thresholds

select * from v$alert_types

REGISTROS TABLA FILTRADO X TABLESPACE

SQL> exec dbms_stats.gather_schema_stats('HR');

SQL> select owner,table_name,TABLESPACE_NAME,last_analyzed,num_rows from dba_tables

where owner='HR' 

and TABLESPACE_NAME='EXAMPLE'

order by num_rows desc

AWR CONFIGURACION RETENTION

 

Snapshots and AWR Snapshot Retention

By default snapshots of the relevant data are taken every hour and retained for 7 days. The default values for these settings can be altered using the following procedure.

BEGIN
  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
    retention => 43200,        -- Minutes (= 30 Days). Current value retained if NULL.
    interval  => 60);          -- Minutes. Current value retained if NULL.
END;
/

CONSULTAR VISTA DBA_HIST_SNAPSHOT
EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

BEGIN
  DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (
    low_snap_id  => 22, 
    high_snap_id => 32);
END;
/

execute dbms_workload_repository.DROP_SNAPSHOT_RANGE(1959,1966);

CREAR SNAPSHOT AWR

 CREAR UN SNAPSHOT TOMAR FOTO

SQL> exec  dbms_workload_repository.create_snapshot();

select SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME
from DBA_HIST_SNAPSHOT
order by 1;

SQL>execute dbms_workload_repository.DROP_SNAPSHOT_RANGE(1959,1966);

RMAN RESTORE TABLE TPITR

 # SCN

RECOVER TABLE TEST.T1
  UNTIL SCN 1853267
  AUXILIARY DESTINATION '/u01/backup'  
  REMAP TABLE 'TEST'.'T1':'T1_PREV';

RECOVER TABLE TEST.T1
  UNTIL SEQUENCE 200
  AUXILIARY DESTINATION '/u01/backup'  
  REMAP TABLE 'TEST'.'T1':'T1_PREV';

# TIME
RECOVER TABLE TEST.T1
  UNTIL TIME "TO_DATE('2020-12-14 19:00', 'YYYY-MM-DD:HH24:MI:SS')"
  AUXILIARY DESTINATION '/u01/backup'  
  REMAP TABLE 'TEST'.'T1':'T1_PREV';

TECNOLOGIA TPITR

LABORATORIO RESTORE TABLE UNTIL TIME

SQL> !date

Mon Dec 14 19:13:47 -05 2020

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

    6003465

SQL> connect hr/hr;

Connected.

SQL> 

SQL> update hr.lolito set salary=0, email='NELSONJAVIER63@GMAIL.COM';

107 rows updated.

SQL> commit;

Commit complete.

RMAN> RECOVER TABLE HR.LOLITO UNTIL TIME "TO_DATE('2020-12-14 19:13:47', 'YYYY-MM-DD:HH24:MI:SS')" AUXILIARY DESTINATION '/u01/backup/';

RMAN> RECOVER TABLE HR.LOLITO UNTIL TIME "TO_DATE('2020-12-14 19:13:47', 'YYYY-MM-DD:HH24:MI:SS')" AUXILIARY DESTINATION '/u01/backup/';

Starting recover at 2020-12-14:19:23:07

current log archived

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 12/14/2020 19:23:09

RMAN-05063: Cannot recover specified tables

RMAN-05112: table "HR"."LOLITO" already exists

RMAN> RECOVER TABLE HR.LOLITO UNTIL TIME "TO_DATE('2020-12-14 19:13:47', 'YYYY-MM-DD:HH24:MI:SS')" AUXILIARY DESTINATION '/u01/backup/';

Starting recover at 2020-12-14:19:23:49

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=72 device type=DISK

RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time

List of tablespaces expected to have UNDO segments

Tablespace SYSTEM

Tablespace UNDOTBS1

Creating automatic instance, with SID='pfuh'

initialization parameters used for automatic instance:

db_name=ACME

db_unique_name=pfuh_pitr_ACME

compatible=12.1.0.0.0

db_block_size=8192

db_files=200

sga_target=1G

processes=80

diagnostic_dest=/u01/app/oracle

db_create_file_dest=/u01/backup/

log_archive_dest_1='location=/u01/backup/'

#No auxiliary parameter file used

starting up automatic instance ACME

Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2296576 bytes

Variable Size                281019648 bytes

Database Buffers             780140544 bytes

Redo Buffers                   5480448 bytes

Automatic instance created

contents of Memory Script:

{

# set requested point in time

set until  time "TO_DATE('2020-12-14 19:13:47', 'YYYY-MM-DD:HH24:MI:SS')";

# restore the controlfile

restore clone controlfile;

# mount the controlfile

sql clone 'alter database mount clone database';

# archive current online log 

sql 'alter system archive log current';

}

executing Memory Script

executing command: SET until clause

Starting restore at 2020-12-14:19:24:01

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=7 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece +FRA/ACME/AUTOBACKUP/2020_12_14/s_1059158709.366.1059158711

channel ORA_AUX_DISK_1: piece handle=+FRA/ACME/AUTOBACKUP/2020_12_14/s_1059158709.366.1059158711 tag=TAG20201214T184509

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:06

output file name=/u01/backup/ACME/controlfile/o1_mf_hxj0k64p_.ctl

Finished restore at 2020-12-14:19:24:07

sql statement: alter database mount clone database

sql statement: alter system archive log current

contents of Memory Script:

{

# set requested point in time

set until  time "TO_DATE('2020-12-14 19:13:47', 'YYYY-MM-DD:HH24:MI:SS')";

# set destinations for recovery set and auxiliary set datafiles

set newname for clone datafile  1 to new;

set newname for clone datafile  4 to new;

set newname for clone datafile  3 to new;

set newname for clone tempfile  1 to new;

# switch all tempfiles

switch clone tempfile all;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile  1, 4, 3;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u01/backup/ACME/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 2020-12-14:19:24:13

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/backup/ACME/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/backup/ACME/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/backup/ACME/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece +FRA/ACME/BACKUPSET/2020_12_14/nnndf0_tag20201214t184339_0.362.1059158619

channel ORA_AUX_DISK_1: piece handle=+FRA/ACME/BACKUPSET/2020_12_14/nnndf0_tag20201214t184339_0.362.1059158619 tag=TAG20201214T184339

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45

Finished restore at 2020-12-14:19:24:59

datafile 1 switched to datafile copy

input datafile copy RECID=11 STAMP=1059161099 file name=/u01/backup/ACME/datafile/o1_mf_system_hxj0kg5n_.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=12 STAMP=1059161099 file name=/u01/backup/ACME/datafile/o1_mf_undotbs1_hxj0kg5t_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=13 STAMP=1059161099 file name=/u01/backup/ACME/datafile/o1_mf_sysaux_hxj0kg5h_.dbf

contents of Memory Script:

{

# set requested point in time

set until  time "TO_DATE('2020-12-14 19:13:47', 'YYYY-MM-DD:HH24:MI:SS')";

# online the datafiles restored or switched

sql clone "alter database datafile  1 online";

sql clone "alter database datafile  4 online";

sql clone "alter database datafile  3 online";

# recover and open database read only

recover clone database tablespace  "SYSTEM", "UNDOTBS1", "SYSAUX";

sql clone 'alter database open read only';

}

executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  1 online

sql statement: alter database datafile  4 online

sql statement: alter database datafile  3 online

Starting recover at 2020-12-14:19:24:59

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 14 is already on disk as file +FRA/ACME/ARCHIVELOG/2020_12_14/thread_1_seq_14.367.1059158705

archived log for thread 1 with sequence 15 is already on disk as file +FRA/ACME/ARCHIVELOG/2020_12_14/thread_1_seq_15.266.1059160987

archived log file name=+FRA/ACME/ARCHIVELOG/2020_12_14/thread_1_seq_14.367.1059158705 thread=1 sequence=14

archived log file name=+FRA/ACME/ARCHIVELOG/2020_12_14/thread_1_seq_15.266.1059160987 thread=1 sequence=15

media recovery complete, elapsed time: 00:00:09

Finished recover at 2020-12-14:19:25:11

sql statement: alter database open read only

contents of Memory Script:

{

   sql clone "create spfile from memory";

   shutdown clone immediate;

   startup clone nomount;

   sql clone "alter system set  control_files = 

  ''/u01/backup/ACME/controlfile/o1_mf_hxj0k64p_.ctl'' comment=

 ''RMAN set'' scope=spfile";

   shutdown clone immediate;

   startup clone nomount;

# mount database

sql clone 'alter database mount clone database';

}

executing Memory Script

sql statement: create spfile from memory

database closed

database dismounted

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2296576 bytes

Variable Size                285213952 bytes

Database Buffers             775946240 bytes

Redo Buffers                   5480448 bytes

sql statement: alter system set  control_files =   ''/u01/backup/ACME/controlfile/o1_mf_hxj0k64p_.ctl'' comment= ''RMAN set'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2296576 bytes

Variable Size                285213952 bytes

Database Buffers             775946240 bytes

Redo Buffers                   5480448 bytes

sql statement: alter database mount clone database

contents of Memory Script:

{

# set requested point in time

set until  time "TO_DATE('2020-12-14 19:13:47', 'YYYY-MM-DD:HH24:MI:SS')";

# set destinations for recovery set and auxiliary set datafiles

set newname for datafile  6 to new;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile  6;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 2020-12-14:19:25:46

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=27 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/backup/PFUH_PITR_ACME/datafile/o1_mf_users_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece +FRA/ACME/BACKUPSET/2020_12_14/nnndf0_tag20201214t184339_0.362.1059158619

channel ORA_AUX_DISK_1: piece handle=+FRA/ACME/BACKUPSET/2020_12_14/nnndf0_tag20201214t184339_0.362.1059158619 tag=TAG20201214T184339

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 2020-12-14:19:25:49

datafile 6 switched to datafile copy

input datafile copy RECID=15 STAMP=1059161149 file name=/u01/backup/PFUH_PITR_ACME/datafile/o1_mf_users_hxj0nd73_.dbf

contents of Memory Script:

{

# set requested point in time

set until  time "TO_DATE('2020-12-14 19:13:47', 'YYYY-MM-DD:HH24:MI:SS')";

# online the datafiles restored or switched

sql clone "alter database datafile  6 online";

# recover and open resetlogs

recover clone database tablespace  "USERS", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;

alter clone database open resetlogs;

}

executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  6 online

Starting recover at 2020-12-14:19:25:49

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 14 is already on disk as file +FRA/ACME/ARCHIVELOG/2020_12_14/thread_1_seq_14.367.1059158705

archived log for thread 1 with sequence 15 is already on disk as file +FRA/ACME/ARCHIVELOG/2020_12_14/thread_1_seq_15.266.1059160987

archived log file name=+FRA/ACME/ARCHIVELOG/2020_12_14/thread_1_seq_14.367.1059158705 thread=1 sequence=14

archived log file name=+FRA/ACME/ARCHIVELOG/2020_12_14/thread_1_seq_15.266.1059160987 thread=1 sequence=15

media recovery complete, elapsed time: 00:00:00

Finished recover at 2020-12-14:19:25:51

database opened

contents of Memory Script:

{

# create directory for datapump import

sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/u01/backup/''";

# create directory for datapump export

sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/u01/backup/''";

}

executing Memory Script

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/backup/''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/backup/''

Performing export of tables...

   EXPDP> Starting "SYS"."TSPITR_EXP_pfuh_nDAC":  

   EXPDP> Estimate in progress using BLOCKS method...

   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

   EXPDP> Total estimation using BLOCKS method: 64 KB

   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE

   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

   EXPDP> ORA-39127: unexpected error from call to export_string :=SYS.DBMS_TRANSFORM_EXIMP.INSTANCE_INFO_EXP('AQ$_ORDERS_QUEUETABLE_S','IX',1,1,'12.01.00.00.00',newblock) 

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: '+DATA/ACME/DATAFILE/example.259.1058124367'

ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197

ORA-06512: at line 1

ORA-06512: at "SYS.DBMS_METADATA", line 9901

ORA-39127: unexpected error from call to export_string :=SYS.DBMS_TRANSFORM_EXIMP.INSTANCE_INFO_EXP('AQ$_STREAMS_QUEUE_TABLE_S','IX',1,1,'12.01.00.00.00',newblock) 

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: '+DATA/ACME/DATAFILE/example.259.1058124367'

ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197

ORA-06512: at line 1

ORA-06512: at "SYS.DBMS_METADATA", line 9901

   EXPDP> . . exported "HR"."LOLITO"                               17.07 KB     107 rows

   EXPDP> Master table "SYS"."TSPITR_EXP_pfuh_nDAC" successfully loaded/unloaded

   EXPDP> ******************************************************************************

   EXPDP> Dump file set for SYS.TSPITR_EXP_pfuh_nDAC is:

   EXPDP>   /u01/backup/tspitr_pfuh_82509.dmp

   EXPDP> Job "SYS"."TSPITR_EXP_pfuh_nDAC" completed with 2 error(s) at Mon Dec 14 19:27:12 2020 elapsed 0 00:00:52

Export completed

contents of Memory Script:

{

# shutdown clone before import

shutdown clone abort

}

executing Memory Script

Oracle instance shut down

Performing import of tables...

   IMPDP> Master table "SYS"."TSPITR_IMP_pfuh_pkrj" successfully loaded/unloaded

   IMPDP> Starting "SYS"."TSPITR_IMP_pfuh_pkrj":  

   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE

   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

   IMPDP> . . imported "HR"."LOLITO"                               17.07 KB     107 rows

   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

   IMPDP> Job "SYS"."TSPITR_IMP_pfuh_pkrj" successfully completed at Mon Dec 14 19:28:00 2020 elapsed 0 00:00:30

Import completed

Removing automatic instance

Automatic instance removed

auxiliary instance file /u01/backup/ACME/datafile/o1_mf_temp_hxj0m83n_.tmp deleted

auxiliary instance file /u01/backup/PFUH_PITR_ACME/onlinelog/o1_mf_3_hxj0nj8c_.log deleted

auxiliary instance file /u01/backup/PFUH_PITR_ACME/onlinelog/o1_mf_2_hxj0nhnq_.log deleted

auxiliary instance file /u01/backup/PFUH_PITR_ACME/onlinelog/o1_mf_1_hxj0nh58_.log deleted

auxiliary instance file /u01/backup/PFUH_PITR_ACME/datafile/o1_mf_users_hxj0nd73_.dbf deleted

auxiliary instance file /u01/backup/ACME/datafile/o1_mf_sysaux_hxj0kg5h_.dbf deleted

auxiliary instance file /u01/backup/ACME/datafile/o1_mf_undotbs1_hxj0kg5t_.dbf deleted

auxiliary instance file /u01/backup/ACME/datafile/o1_mf_system_hxj0kg5n_.dbf deleted

auxiliary instance file /u01/backup/ACME/controlfile/o1_mf_hxj0k64p_.ctl deleted

auxiliary instance file tspitr_pfuh_82509.dmp deleted

Finished recover at 2020-12-14:19:28:06

RMAN> 

TECNOLOGIA TPITR

LABORATORIO RESTORE TABLE SCN 

SQL> create table hr.lolito as (select * from hr.employees); 

RMAN> RECOVER TABLE HR.LOLITO UNTIL SCN 6223707 AUXILIARY DESTINATION '/u01/backup/';

Starting recover at 14-DEC-20

using target database control file instead of recovery catalog

current log archived

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 12/14/2020 18:58:42

RMAN-05063: Cannot recover specified tables

RMAN-05112: table "HR"."LOLITO" already exists

RMAN> RECOVER TABLE HR.LOLITO UNTIL SCN 6223707 AUXILIARY DESTINATION '/u01/backup/';

Starting recover at 14-DEC-20

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=50 device type=DISK

RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time

List of tablespaces expected to have UNDO segments

Tablespace SYSTEM

Tablespace UNDOTBS1

Creating automatic instance, with SID='haer'

initialization parameters used for automatic instance:

db_name=ORCL

db_unique_name=haer_pitr_ORCL

compatible=12.1.0.0.0

db_block_size=8192

db_files=200

sga_target=1G

processes=80

diagnostic_dest=/u01/app/oracle

db_create_file_dest=/u01/backup/

log_archive_dest_1='location=/u01/backup/'

#No auxiliary parameter file used

starting up automatic instance ORCL

Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2296576 bytes

Variable Size                281019648 bytes

Database Buffers             780140544 bytes

Redo Buffers                   5480448 bytes

Automatic instance created

contents of Memory Script:

{

# set requested point in time

set until  scn 6223707;

# restore the controlfile

restore clone controlfile;

# mount the controlfile

sql clone 'alter database mount clone database';

# archive current online log 

sql 'alter system archive log current';

}

executing Memory Script

executing command: SET until clause

Starting restore at 14-DEC-20

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=7 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/autobackup/2020_12_14/o1_mf_s_1059158574_hxhy3zj0_.bkp

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2020_12_14/o1_mf_s_1059158574_hxhy3zj0_.bkp tag=TAG20201214T184254

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/backup/ORCL/controlfile/o1_mf_hxhz3two_.ctl

Finished restore at 14-DEC-20

sql statement: alter database mount clone database

sql statement: alter system archive log current

contents of Memory Script:

{

# set requested point in time

set until  scn 6223707;

# set destinations for recovery set and auxiliary set datafiles

set newname for clone datafile  1 to new;

set newname for clone datafile  5 to new;

set newname for clone datafile  4 to new;

set newname for clone datafile  3 to new;

set newname for clone tempfile  1 to new;

# switch all tempfiles

switch clone tempfile all;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile  1, 5, 4, 3;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u01/backup/ORCL/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 14-DEC-20

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/backup/ORCL/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/backup/ORCL/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/backup/ORCL/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/backup/ORCL/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/backupset/2020_12_14/o1_mf_nnnd0_TAG20201214T184159_hxhy27q3_.bkp

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2020_12_14/o1_mf_nnnd0_TAG20201214T184159_hxhy27q3_.bkp tag=TAG20201214T184159

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05

Finished restore at 14-DEC-20

datafile 1 switched to datafile copy

input datafile copy RECID=7 STAMP=1059159666 file name=/u01/backup/ORCL/datafile/o1_mf_system_hxhz41lk_.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=8 STAMP=1059159666 file name=/u01/backup/ORCL/datafile/o1_mf_system_hxhz41lw_.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=9 STAMP=1059159666 file name=/u01/backup/ORCL/datafile/o1_mf_undotbs1_hxhz41lo_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=10 STAMP=1059159666 file name=/u01/backup/ORCL/datafile/o1_mf_sysaux_hxhz41l5_.dbf

contents of Memory Script:

{

# set requested point in time

set until  scn 6223707;

# online the datafiles restored or switched

sql clone "alter database datafile  1 online";

sql clone "alter database datafile  5 online";

sql clone "alter database datafile  4 online";

sql clone "alter database datafile  3 online";

# recover and open database read only

recover clone database tablespace  "SYSTEM", "UNDOTBS1", "SYSAUX";

sql clone 'alter database open read only';

}

executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  1 online

sql statement: alter database datafile  5 online

sql statement: alter database datafile  4 online

sql statement: alter database datafile  3 online

Starting recover at 14-DEC-20

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 12 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_12_14/o1_mf_1_12_hxhz1klk_.arc

archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_12_14/o1_mf_1_12_hxhz1klk_.arc thread=1 sequence=12

media recovery complete, elapsed time: 00:00:00

Finished recover at 14-DEC-20

sql statement: alter database open read only

contents of Memory Script:

{

   sql clone "create spfile from memory";

   shutdown clone immediate;

   startup clone nomount;

   sql clone "alter system set  control_files = 

  ''/u01/backup/ORCL/controlfile/o1_mf_hxhz3two_.ctl'' comment=

 ''RMAN set'' scope=spfile";

   shutdown clone immediate;

   startup clone nomount;

# mount database

sql clone 'alter database mount clone database';

}

executing Memory Script

sql statement: create spfile from memory

database closed

database dismounted

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2296576 bytes

Variable Size                285213952 bytes

Database Buffers             775946240 bytes

Redo Buffers                   5480448 bytes

sql statement: alter system set  control_files =   ''/u01/backup/ORCL/controlfile/o1_mf_hxhz3two_.ctl'' comment= ''RMAN set'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2296576 bytes

Variable Size                285213952 bytes

Database Buffers             775946240 bytes

Redo Buffers                   5480448 bytes

sql statement: alter database mount clone database

contents of Memory Script:

{

# set requested point in time

set until  scn 6223707;

# set destinations for recovery set and auxiliary set datafiles

set newname for datafile  6 to new;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile  6;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 14-DEC-20

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=20 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/backup/HAER_PITR_ORCL/datafile/o1_mf_users_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/backupset/2020_12_14/o1_mf_nnnd0_TAG20201214T184159_hxhy27q3_.bkp

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2020_12_14/o1_mf_nnnd0_TAG20201214T184159_hxhy27q3_.bkp tag=TAG20201214T184159

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 14-DEC-20

datafile 6 switched to datafile copy

input datafile copy RECID=12 STAMP=1059159701 file name=/u01/backup/HAER_PITR_ORCL/datafile/o1_mf_users_hxhz743t_.dbf

contents of Memory Script:

{

# set requested point in time

set until  scn 6223707;

# online the datafiles restored or switched

sql clone "alter database datafile  6 online";

# recover and open resetlogs

recover clone database tablespace  "USERS", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;

alter clone database open resetlogs;

}

executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  6 online

Starting recover at 14-DEC-20

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 12 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_12_14/o1_mf_1_12_hxhz1klk_.arc

archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_12_14/o1_mf_1_12_hxhz1klk_.arc thread=1 sequence=12

media recovery complete, elapsed time: 00:00:00

Finished recover at 14-DEC-20

database opened

contents of Memory Script:

{

# create directory for datapump import

sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/u01/backup/''";

# create directory for datapump export

sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/u01/backup/''";

}

executing Memory Script

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/backup/''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/backup/''

Performing export of tables...

   EXPDP> Starting "SYS"."TSPITR_EXP_haer_peEB":  

   EXPDP> Estimate in progress using BLOCKS method...

   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

   EXPDP> Total estimation using BLOCKS method: 64 KB

   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE

   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

   EXPDP> ORA-39127: unexpected error from call to export_string :=SYS.DBMS_TRANSFORM_EXIMP.INSTANCE_INFO_EXP('AQ$_ORDERS_QUEUETABLE_S','IX',1,1,'12.01.00.00.00',newblock) 

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_hwjdb5y7_.dbf'

ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197

ORA-06512: at line 1

ORA-06512: at "SYS.DBMS_METADATA", line 9901

ORA-39127: unexpected error from call to export_string :=SYS.DBMS_TRANSFORM_EXIMP.INSTANCE_INFO_EXP('AQ$_STREAMS_QUEUE_TABLE_S','IX',1,1,'12.01.00.00.00',newblock) 

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_hwjdb5y7_.dbf'

ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197

ORA-06512: at line 1

ORA-06512: at "SYS.DBMS_METADATA", line 9901

   EXPDP> . . exported "HR"."LOLITO"                               17.07 KB     107 rows

   EXPDP> Master table "SYS"."TSPITR_EXP_haer_peEB" successfully loaded/unloaded

   EXPDP> ******************************************************************************

   EXPDP> Dump file set for SYS.TSPITR_EXP_haer_peEB is:

   EXPDP>   /u01/backup/tspitr_haer_59331.dmp

   EXPDP> Job "SYS"."TSPITR_EXP_haer_peEB" completed with 2 error(s) at Mon Dec 14 19:03:12 2020 elapsed 0 00:00:50

Export completed

contents of Memory Script:

{

# shutdown clone before import

shutdown clone abort

}

executing Memory Script

Oracle instance shut down

Performing import of tables...

   IMPDP> Master table "SYS"."TSPITR_IMP_haer_pAbb" successfully loaded/unloaded

   IMPDP> Starting "SYS"."TSPITR_IMP_haer_pAbb":  

   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE

   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

   IMPDP> . . imported "HR"."LOLITO"                               17.07 KB     107 rows

   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

   IMPDP> Job "SYS"."TSPITR_IMP_haer_pAbb" successfully completed at Mon Dec 14 19:03:56 2020 elapsed 0 00:00:30

Import completed

Removing automatic instance

Automatic instance removed

auxiliary instance file /u01/backup/ORCL/datafile/o1_mf_temp_hxhz666l_.tmp deleted

auxiliary instance file /u01/backup/HAER_PITR_ORCL/onlinelog/o1_mf_3_hxhz774g_.log deleted

auxiliary instance file /u01/backup/HAER_PITR_ORCL/onlinelog/o1_mf_2_hxhz76pt_.log deleted

auxiliary instance file /u01/backup/HAER_PITR_ORCL/onlinelog/o1_mf_1_hxhz76cr_.log deleted

auxiliary instance file /u01/backup/HAER_PITR_ORCL/datafile/o1_mf_users_hxhz743t_.dbf deleted

auxiliary instance file /u01/backup/ORCL/datafile/o1_mf_sysaux_hxhz41l5_.dbf deleted

auxiliary instance file /u01/backup/ORCL/datafile/o1_mf_undotbs1_hxhz41lo_.dbf deleted

auxiliary instance file /u01/backup/ORCL/datafile/o1_mf_system_hxhz41lw_.dbf deleted

auxiliary instance file /u01/backup/ORCL/datafile/o1_mf_system_hxhz41lk_.dbf deleted

auxiliary instance file /u01/backup/ORCL/controlfile/o1_mf_hxhz3two_.ctl deleted

auxiliary instance file tspitr_haer_59331.dmp deleted

Finished recover at 14-DEC-20

RMAN> 

BACKUP INCREMENTAL UNTIL TIME

 RMAN> backup incremental level 1 format '/u01/backup/dev_%t_%s_level1.dbk' database plus archivelog delete input until time "to_date(to_char(trunc(sysdate) || ':13:00:00'), 'DD-MON-YY:hh24:mi:ss')" ; 

FLASHBACK DATABASE TO RESTORE POINT GUARANTEE.

LABORATORIO.

ACTIVAR FLASHBACK DATABASE

CREAR PUNTO DE RESTAURACION

SQL> create restore point before_upgrade guarantee flashback database

SQL> /

Restore point created.

SQL> update hr.prueba set salary=0;

107 rows updated.

SQL> commit;

Commit complete.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  835104768 bytes

Fixed Size     2293880 bytes

Variable Size   658509704 bytes

Database Buffers   171966464 bytes

Redo Buffers     2334720 bytes

Database mounted.

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@localhost ~]$ 

RMAN> FLASHBACK DATABASE TO RESTORE POINT 'BEFORE_UPGRADE';

Starting flashback at 11-DEC-20

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:01

Finished flashback at 11-DEC-20

Finished flashback at 11-DEC-20

RMAN> alter database open resetlogs;

Statement processed

RMAN> 

RMAN> list restore point all;

using target database control file instead of recovery catalog

SCN              RSP Time  Type       Time      Name

---------------- --------- ---------- --------- ----

6032442                    GUARANTEED 11-DEC-20 BEFORE_UPGRADE

RMAN> drop restore point before_upgrade;

Statement processed

RMAN> 

FLASHBACK DATABASE

LABORATORIO.

SQL*Plus: Release 12.1.0.1.0 Production on Fri May 14 21:14:29 2021

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics

and Real Application Testing options

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> 

SQL> startup mount;

ORACLE instance started.

Total System Global Area  835104768 bytes

Fixed Size     2293880 bytes

Variable Size   658509704 bytes

Database Buffers   171966464 bytes

Redo Buffers     2334720 bytes

Database mounted.

SQL> flashback database to timestamp TO_TIMESTAMP('2021-05-14 21:11:00','YYYY-MM-DD HH24:MI:SS');

Flashback complete.

SQL>

SQL> alter system set undo_retention=1800 scope=both;

System altered.

SQL> show parameter undo;

NAME      TYPE  VALUE

------------------------------------ ----------- ------------------------------

temp_undo_enabled      boolean  FALSE

undo_management       string  AUTO

undo_retention      integer  1800

undo_tablespace       string  UNDOTBS1

ESTO SE DEBE REALIZAR CON LAS INSTANCIA EN MODO OPEN

SQL> select tablespace_name,retention from dba_tablespaces;

TABLESPACE_NAME         RETENTION

------------------------------ -----------

SYSTEM        NOT APPLY

SYSAUX        NOT APPLY

UNDOTBS1        NOGUARANTEE

TEMP        NOT APPLY

USERS        NOT APPLY

EXAMPLE         NOT APPLY

TBS_1        NOT APPLY

7 rows selected.

SQL> 

SQL> alter tablespace undotbs1 retention guarantee;

SQL> shutdown immediate; 
SQL> startup mount; 
ORACLE instance started. 

CONFIGURACION EN MINUTOS


SQL> alter system set DB_FLASHBACK_RETENTION_TARGET=1440;
System altered. 

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=10G; 
System altered. 

SQL> alter database flashback on; 
Database altered. 

SQL> alter database open; 
Database altered. 

SQL>alter system set recyclebin=on scope=both; 

SQL> select open_mode,log_mode,flashback_on ,current_scn from v$database; 


SQL> shutdown immediate; 
Base de datos cerrada. 
Base de datos desmontada. 
Instancia ORACLE cerrada. 
SQL> 

SQL> start mount; 
SP2-0310: no se ha podido abrir el archivo "mount.sql" 
SQL> startup mount; 
Instancia ORACLE iniciada. 

Total System Global Area 285212672 bytes 
Fixed Size 1267068 bytes 
Variable Size 92277380 bytes 
Database Buffers 188743680 bytes 
Redo Buffers 2924544 bytes 
Base de datos montada. 
SQL> 

SQL> flashback database to scn 70812; 
flashback database to scn 70812 
* 
ERROR en linea 1: 
ORA-38726: El registro de flashback de la base de datos no esta activado. 

SQL> 

LABORATORIO FLASHBACK DATABASE 

ACTIVACION FLASHBACK 

[oracle@oracle11g ~]$ sqlplus '/as sysdba' 

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jul 9 14:39:51 2010 

Copyright (c) 1982, 2007, Oracle. All Rights Reserved. 

Connected to an idle instance. 

SQL> startup mount; 
ORACLE instance started. 

Total System Global Area 285212672 bytes 
Fixed Size 1267068 bytes 
Variable Size 92277380 bytes 
Database Buffers 188743680 bytes 
Redo Buffers 2924544 bytes 
Base de datos montada. 
SQL> 
SQL> archive log list; 
Modo log de la base de datos Modo de Archivado 
Archivado automatico Activado 
Destino del archivo USE_DB_RECOVERY_FILE_DEST 
Secuencia de log en linea mas antigua 2 
Siguiente secuencia de log para archivar 4 
Secuencia de log actual 4 
SQL> 
SQL> alter database flashback on; 

Base de datos modificada. 

SQL> show parameter db_flashback; 

NAME TYPE VALUE 
------------------------------------ ----------- ------------------------------ 
db_flashback_retention_target integer 1440 
SQL> select current_scn,flashback_on from v$database; 

CURRENT_SCN FLASHBACK_ON 
----------- ------------------ 
0 YES 

SQL> alter database flashback off; 

Base de datos modificada. 

SQL> select current_scn,flashback_on from v$database; 

CURRENT_SCN FLASHBACK_ON 
----------- ------------------ 
0 NO 

SQL> alter database flashback on; 

Base de datos modificada. 

SQL> select * from v$instance; 

INSTANCE_NUMBER INSTANCE_NAME 
--------------- ---------------- 
HOST_NAME 
---------------------------------------------------------------- 
VERSION STARTUP_ STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT 
----------------- -------- ------------ --- ---------- ------- --------------- 
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO 
---------- --- ----------------- ------------------ --------- --- 
1 acme 
oracle11g.localdomain 
10.2.0.4.0 09/07/10 MOUNTED NO 1 STARTED 
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO 


SQL> alter database open; 

Base de datos modificada. 

EMPEZAMOS EL LABORATORIO FLASHBACK DATABASE 

SQL> connect hr/hr; 
Conectado. 
SQL> 
SQL> 
SQL> select * from tab; 

TNAME TABTYPE CLUSTERID 
------------------------------ ------- ---------- 
REGIONS TABLE 
COUNTRIES TABLE 
LOCATIONS TABLE 
DEPARTMENTS TABLE 
JOBS TABLE 
EMPLOYEES TABLE 
JOB_HISTORY TABLE 
EMP_DETAILS_VIEW VIEW 
COPIA_REGIONS TABLE 


SQL> drop table COPIA_REGIONS; 

Tabla borrada. 

SQL> 

SQL> create table COPIA_EMPLOYEES as (select * from employees); 

Tabla creada. 

SQL> select * from tab; 

TNAME TABTYPE CLUSTERID 
------------------------------ ------- ---------- 
REGIONS TABLE 
COUNTRIES TABLE 
LOCATIONS TABLE 
DEPARTMENTS TABLE 
JOBS TABLE 
EMPLOYEES TABLE 
JOB_HISTORY TABLE 
EMP_DETAILS_VIEW VIEW 
BIN$ivm+7nDFpazgQAB/AQALUg==$0 TABLE 
COPIA_EMPLOYEES TABLE 

SQL> delete from COPIA_EMPLOYEES where 1=1; 

107 filas suprimidas. 

SQL> commit; 

Confirmacion terminada. 

SQL> 

SQL> exit 
Desconectado de Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production 
With the Partitioning, OLAP, Data Mining and Real Application Testing options 
[oracle@oracle11g ~]$ sqlplus '/as sysdba' 

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jul 9 14:55:59 2010 

Copyright (c) 1982, 2007, Oracle. All Rights Reserved. 


Conectado a: 
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production 
With the Partitioning, OLAP, Data Mining and Real Application Testing options 

CURRENT ACTUAL 

SQL> select current_scn from v$database; 

CURRENT_SCN 
----------- 
708510 


SQL> desc hr.regions; 
Nombre ?Nulo? Tipo 
----------------------------------------- -------- ---------------------------- 
REGION_ID NOT NULL NUMBER 
REGION_NAME VARCHAR2(25) 

SQL> insert into hr.regions values(6,'COLOMBIA'); 

1 fila creada. 

SQL> insert into hr.regions values(7,'PERU'); 

1 fila creada. 

SQL> COMMIT; 

Confirmacion terminada. 

SQL> select * from hr.regions; 

REGION_ID REGION_NAME 
---------- ------------------------- 
1 Europe 
2 Americas 
3 Asia 
4 Middle East and Africa 
6 COLOMBIA 
7 PERU 

6 filas seleccionadas. 

COMO HAGO PARA DEVOLVERME A UN ESTADO ANTERIOR DE LA BASE DE DATOS ANTES DEL DELETE 

SQL> shutdown immediate; 
Base de datos cerrada. 
Base de datos desmontada. 
Instancia ORACLE cerrada. 
SQL> 
SQL> 
SQL> startup mount; 
Instancia ORACLE iniciada. 

Total System Global Area 285212672 bytes 
Fixed Size 1267068 bytes 
Variable Size 92277380 bytes 
Database Buffers 188743680 bytes 
Redo Buffers 2924544 bytes 
Base de datos montada. 
SQL> select * from v$instance; 

INSTANCE_NUMBER INSTANCE_NAME 
--------------- ---------------- 
HOST_NAME 
---------------------------------------------------------------- 
VERSION STARTUP_ STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT 
----------------- -------- ------------ --- ---------- ------- --------------- 
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO 
---------- --- ----------------- ------------------ --------- --- 
1 acme 
oracle11g.localdomain 
10.2.0.4.0 09/07/10 MOUNTED NO 1 STARTED 
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO 


SQL> select current_scn from v$database; 

CURRENT_SCN 
----------- 
0 

VALIDAR EL RESULTADO DE ESTE QUERY 

SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG; 


SQL> flashback database to scn 708358; 

Flashback terminado. 

SQL> alter database open; 
alter database open 
* 
ERROR en linea 1: 
ORA-01589: debe utilizar la opcion RESETLOGS o NORESETLOGS para abrir la base 
de datos 


SQL> alter database open resetlogs; 

Base de datos modificada. 

SQL> 

VER NUEVA INCARNATION GENERADA EN LA INSTANCIA 

[oracle@oracle11g ~]$ rman target / 

Recovery Manager: Release 10.2.0.4.0 - Production on Fri Jul 9 15:02:27 2010 

Copyright (c) 1982, 2007, Oracle. All rights reserved. 

connected to target database: ACME (DBID=1908099333, not open) 

RMAN> list incarnation; 

using target database control file instead of recovery catalog 

List of Database Incarnations 
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time 
------- ------- -------- ---------------- --- ---------- ---------- 
1 1 ACME 1908099333 PARENT 584972 06-JUL-10 
2 2 ACME 1908099333 CURRENT 654164 07-JUL-10 

RMAN> list incarnation; 


List of Database Incarnations 
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time 
------- ------- -------- ---------------- --- ---------- ---------- 
1 1 ACME 1908099333 PARENT 584972 06-JUL-10 
2 2 ACME 1908099333 PARENT 654164 07-JUL-10 
3 3 ACME 1908099333 CURRENT 708361 09-JUL-10 

RMAN> 

SI TOCA DEVOLVER AL ULTIMO RESETLOGS.

RMAN> FLASHBACK DATASBE TO BEFORE RESETLOGS;

VALIDAR LA DATA A VER SI QUEDO BIEN 

SQL> connect hr/hr; 
Conectado. 
SQL> select * from tab; 

TNAME TABTYPE CLUSTERID 
------------------------------ ------- ---------- 
REGIONS TABLE 
COUNTRIES TABLE 
LOCATIONS TABLE 
DEPARTMENTS TABLE 
JOBS TABLE 
EMPLOYEES TABLE 
JOB_HISTORY TABLE 
EMP_DETAILS_VIEW VIEW 
COPIA_REGIONS TABLE 

9 filas seleccionadas. 

SQL> select * from regions; 

REGION_ID REGION_NAME 
---------- ------------------------- 
1 Europe 
2 Americas 
3 Asia 
4 Middle East and Africa 

SQL> 

NOTA: REALIZAR BACKUP VIA RMAN FULL DATABASE ARCHIVELOG 


5 LABORATORIO FLASHBACK QUERY 

[oracle@oracle11g ~]$ sqlplus '/as sysdba' 

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jul 9 15:23:03 2010 

Copyright (c) 1982, 2007, Oracle. All Rights Reserved. 


Conectado a: 
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production 
With the Partitioning, OLAP, Data Mining and Real Application Testing options 

SQL> connect hr/hr; 
Conectado. 
SQL> CREATE TABLE emp 
(empno NUMBER PRIMARY KEY, 
empname VARCHAR2(16), 
salary NUMBER); 
2 3 4 
Tabla creada. 

SQL> INSERT INTO emp VALUES (111, 'Mike', 555); 

1 fila creada. 

SQL> commit; 

Confirmacion terminada. 

SQL> CREATE TABLE dept 
(deptno NUMBER, 
deptname VARCHAR2(32)); 
2 3 
Tabla creada. 

SQL> INSERT INTO dept VALUES (10, 'Accounting'); 

1 fila creada. 

SQL> commit; 

Confirmacion terminada. 

SQL> UPDATE emp SET salary = salary + 100 WHERE empno = 111; 

1 fila actualizada. 

SQL> COMMIT; 

Confirmacion terminada. 

SQL> select * from emp; 

EMPNO EMPNAME SALARY 
---------- ---------------- ---------- 
111 Mike 655 

SQL> 
SQL> INSERT INTO dept VALUES (20, 'Finance'); 

1 fila creada. 

SQL> DELETE FROM emp WHERE empno = 111; 

1 fila suprimida. 

SQL> commit; 

Confirmacion terminada. 

SQL> 


REALIZAN UNA NUEVA TRANSACCION PARA EL CODIGO 111 

a new transaction reinserts employee id 111 with a new employee name into the emp table. 

[oracle@oracle11g ~]$ sqlplus '/as sysdba' 

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jul 9 15:30:54 2010 

Copyright (c) 1982, 2007, Oracle. All Rights Reserved. 

Conectado a: 
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production 
With the Partitioning, OLAP, Data Mining and Real Application Testing options 


SQL> SELECT versions_xid XID, versions_startscn START_SCN, 
versions_endscn END_SCN, versions_operation OPERATION, 
empname, salary FROM hr.emp 
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE 
where empno = 111; 
2 3 4 5 
XID START_SCN END_SCN O EMPNAME SALARY 
---------------- ---------- ---------- - ---------------- ---------- 
0800230006010000 710161 I Tom 927 
0600220049010000 710077 D Mike 655 
0500290027010000 710057 710077 U Mike 655 
04000800DB000000 709971 710057 I Mike 555 

SQL> 

VALIDAMOS EL undo_sql 

SELECT xid, start_scn, commit_scn, 
operation, logon_user, 
undo_sql FROM flashback_transaction_query 
WHERE xid = HEXTORAW('0600220049010000') 
/ 

XID START_SCN COMMIT_SCN OPERATION LOGON_USER 
---------------- ---------- ---------- -------------------------------- ------------------------------ 
UNDO_SQL 
0600220049010000 710065 710077 DELETE HR 
insert into "HR"."EMP"("EMPNO","EMPNAME","SALARY") values ('111','Mike','655'); 

0600220049010000 710065 710077 INSERT HR 
delete from "HR"."DEPT" where ROWID = 'AAAM7pAAEAAAAGcAAB'; 

0600220049010000 710065 710077 BEGIN HR 

•To obtain an SCN to use later with a flashback feature, use 

DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER. 


6 MANEJO PAPELERA RECICLAJE 
SELECT * FROM RECYCLEBIN; 

ALTER SESSION SET recyclebin = OFF; 
ALTER SYSTEM SET recyclebin = OFF; 

VISTAS 

USER_RECYCLEBIN 
This view can be used by users to see their own dropped objects in the recycle bin. It has a synonym RECYCLEBIN, for ease of use. 

DBA_RECYCLEBIN 
This view gives administrators visibility to all dropped objects in the recycle bin 

PURGE TABLE nombre_tabla; 
Elimina la tabla indicada de la papelera. El nombre de la tabla puede ser el nombre original o el renombrado. 

PURGE INDEX nombre_índice; 
Elimina el índice indicado de la papelera. El nombre del índice es el nombre original y no el renombrado. 

PURGE RECYCLEBIN; 
Elimina todos los objetos (del usuario que lanza la orden) de la papelera. 

PURGE DBA_RECYCLEBIN; 
Elimina todos los objetos (de todos los usuarios) de la papelera. Solo un SYSDBA puede lanzar este comando. 

PURGE TABLESPACE nombre_tablespace; 
Elimina todos los objetos (del usuario) de la papelera que residan en el tablespace indicado. 

PURGE TABLESPACE nombre_tablespace USERS nombre_usuario; 
Elimina todos los objetos de la papelera que residan en el tablespace indicado y pertenezcan el usuario indicado. 

SQL> show recyclebin;ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME---------------- ------------------------------ ------------ -------------------PENDIENTES BIN$WJ4MAU6RU7XgRAAUT2gJUg==$0 TABLE 2008-10-06:17:17:21PRUEBA BIN$WMDKHy9/HiHgRAAUT2gJUg==$0 TABLE 2008-10-08:10:44:19PRUEBA BIN$WL+UR7qDG8/gRAAUT2gJUg==$0 TABLE 2008-10-08:09:17:41PRUEBA BIN$WL83vpCuGmTgRAAUT2gJUg==$0 TABLE 2008-10-08:08:51:49 

SQL> flashback table pendientes to before drop; 
Flashback complete. 
SQL> 


SQL> show recyclebin 
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME 
---------------- ------------------------------ ------------ ------------------- 
EMPLOYEES BIN$yrMKlZaVMhfgNAgAIMenRA==$0 TABLE 2003-10-27:14:00:19 

Tmabien se puede realizar un query con el nombre de la tabla de esta forma. 

example: SELECT * FROM "BIN$yrMKlZaVMhfgNAgAIMenRA==$0"; 

PURGE TABLE BIN$jsleilx392mk2=293$0; 

PURGE TABLESPACE example; 

PURGE TABLESPACE example USER oe; 

Limpia toda la papelera reciclaje 

PURGE RECYCLEBIN;

CONSULTAS FLASHBACK

SQL> select estimated_flashback_size,flashback_size from v$flashback_database_log;

ESTIMATED_FLASHBACK_SIZE FLASHBACK_SIZE

------------------------ --------------

11039744      104857600

SQL> select oldest_flashback_scn, oldest_flashback_time from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_F

-------------------- --------

     5799514 11/12/20

SQL>select * from v$flashback_database_stat;