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;

Hash cluster

Hash function applied to key and locates the needed block (for select or update)

-- When to use
1) Rows with the same key are located in the same area on disk
2) Mostly equal predicates used for the key

Better than table/index because for that needed:
1) One or more I/O to find entry in index
2) One or more I/O to find entry in table

FRA

-- Space usage
select name, space_used/1024/1024 um, space_reclaimable/1024/1024 m, space_limit/1024/1024 ml from v$recovery_file_dest;
-- Delete unneeded archivelogs from OS
rmam
-- Crosscheck
crosscheck archivelog all;
-- List
list expired archivelog all;
-- Delete
delete expired archivelog all

resync catalog, error in doc 10g

doc

RMAN> resync catalog;

new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> resync catalog;

starting full resync of recovery catalog
full resync complete

-- And that form does not work (but would be really useful)
RMAN> RESYNC CONTROLFILE FROM CONTROLFILECOPY '/tmp/cfile.dbf';

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "controlfile": expecting one of: "catalog"
RMAN-01007: at line 1 column 8 file: standard input

set initial consumer group for user

-- Create consumer group
exec dbms_resource_manager.create_pending_area;
exec dbms_resource_manager.create_consumer_group('test', 'test');
-- Try to set for user test
09:36:37 159 4294967295 SYS@orcl> exec dbms_resource_manager.set_initial_consumer_group('test', 'test');
ORA-29368: consumer group TEST does not exist
-- Need to submit pending area
exec dbms_resource_manager.submit_pending_area;
-- But...
exec dbms_resource_manager.set_initial_consumer_group('test', 'test');
ORA-29399: user TEST does not have privilege to switch to consumer group TEST
-- Grant privilege
exec dbms_resource_manager_privs.grant_switch_consumer_group('test', 'test', false);
-- And we are done
exec dbms_resource_manager.set_initial_consumer_group('test', 'test');

resumable session

08:21:50 159 67 TEST@orcl> alter session enable resumable timeout 60;
ORA-01031: insufficient privileges
--
08:29:00 159 4294967295 SYS@orcl> grant resumable to test;
Grant succeeded.
--
08:29:22 159 69 TEST@orcl> alter session enable resumable timeout 60;
Session altered.

online validate structure

-- error will raise if something wrong
analyze table test validate structure online;

scn controlfile/datafile

set lines 300
column name format a51
select d.name, d.checkpoint_change# control_file_change, h.checkpoint_change# file_change
from v$datafile d, v$datafile_header h
where h.file# = d.file#
order by 1
/

1) control_file_change = file_change => database can be opened
2) control_file_change > file_change => recovery is needed
3) control_file_change < file_change => recovery using backup controlfile needed