07:31:46 155 147 TEST@orcl> select start_scn, commit_scn, undo_sql from flashback_transaction_query where table_name = 'F';
ORA-01031: insufficient privileges
--
07:48:00 155 4294967295 SYS@orcl> grant select any transaction to test;
07:48:26 155 148 TEST@orcl> select start_scn, commit_scn, undo_sql from flashback_transaction_query where table_name = 'F';

START_SCN | COMMIT_SCN
------------------------------------ | ------------------------------------
UNDO_SQL
--------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------

646,961.0 | 646,963.0
delete from "TEST"."F" where ROWID = 'AAAMi7AAFAAAAANAAA';

648,154.0 | 648,155.0
delete from "TEST"."F" where ROWID = 'AAAMi7AAAAAAAAAAAA';

647,577.0 | 647,579.0
delete from "TEST"."F" where ROWID = 'AAAMi7AAFAAAAAMAAA';

648,011.0 | 648,013.0
insert into "TEST"."F"("ID") values ('3');

shrink lob

-- create table (should be lmt tablespace or ORA-10635: Invalid segment or tablespace type will raise)
create table t_lob (id clob);
-- insert data
insert into t_lob select to_clob(lpad('x', 4000))||to_clob(lpad('x', 4000)) from dual connect by rownum <= 3000;
-- check size
select segment_name from dba_lobs where table_name = 'T_LOB';
SEGMENT_NAME
------------------------------
SYS_LOB0000015701C00001$$
select bytes/1024/1024 m from dba_segments where segment_name = 'SYS_LOB0000015701C00001$$';
M
------------------------------------
24.0
-- delete rows
delete from t_lob;
commit;
-- shrink
alter table t_lob shrink space cascade;
ORA-10636: ROW MOVEMENT is not enabled
--
alter table t_lob enable row movement;
alter table t_lob shrink space cascade;
Table altered.
select bytes/1024/1024 m from dba_segments where segment_name = 'SYS_LOB0000015701C00001$$';

M
------------------------------------
.0625

How to find out sql for ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

-- First session set resumable timeout 30 sec
alter session enable resumable timeout 30;
-- or for all system
alter system set resumable_timeout=30;
-- Temp file should be 3 meg
select * from dba_objects, dba_tables order by 1,2,3,4,5,6,7,8,9;
-- Other session
select session_id, sql_text, error_msg from dba_resumable;
Output:
...
153.0
select * from dba_objects, dba_tables order by 1,2,3,4,5,6,7,8,9
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
...
-- Monitor of tempspace usage
select s.sid, t.blocks*p.value/1024/1024 m
from v$tempseg_usage t, v$parameter p , v$session s
where p.name = 'db_block_size' and
s.saddr = t.session_addr;
-- Abort resumed session
exec dbms_resumable.abort(153);

Validate structure partitioned table

-- Create table
create table t_p (id number)
partition by range (id)
(partition t_p_1 values less than (1)
, partition t_p_2 values less than (2)
, partition t_p_3 values less than(3));
-- First attempt
analyze table t_p partition (t_p_1) validate structure;
ORA-14508: specified VALIDATE INTO table not found
-- Need to create validate table
@rdbms\admin\utlvalid.sql;
-- Now works
analyze table t_p validate structure;
analyze table t_p partition (t_p_1) validate structure;

TPITR

1) Not possible for tablespaces with rollback segments in
select distinct tablespace_name from dba_rollback_segs;
2) Tablespace containging dependencies
select * from (select TS1_NAME from ts_pitr_check union select TS2_NAME from ts_pitr_check) where ts1_name <> '-1';

gurantee restore point without flashback on

-- sqlplus
shutdown immediate;
startup mount;
create restore point test_p guarantee flashback database;
-- in db_recovery_file_dest
$ls -la
total 8008
drwxrwxrwx 1 user group 0 Dec 15 16:00 .
drwxrwxrwx 1 user group 0 Dec 15 13:01 ..
-rw-rw-rw- 1 user group 8200192 Dec 15 16:21 O1_MF_6JKQ7HHV_.FLB
-- sqlplus
drop table SYS_TEMP_FBT;
-- rman
RMAN> shutdown immediate;
RMAN> startup mount;
RMAN> flashback database to restore point test_p;
-- sqlplus and table back :)
alter database open read only;
desc SYS_TEMP_FBT
Name Null? Type
------------------------------------------- --------
...

Advisor: log size

-- !!! fast_start_mttr_target should be set !!!
-- Optimal and actual size of log in MB
select distinct o.optimal_logfile_size omb, l.bytes/1024/1024 amb from v$instance_recovery o, v$log l;