Shrink segments

Script to shrink segments:
SELECT 'alter table '
  ||OWNER
  ||'.'
  ||TABLE_NAME
  ||' enable row movement;' sql1
FROM DBA_TABLES
WHERE TABLESPACE_NAME IN ('USERS')
AND TABLE_NAME NOT    IN
  (SELECT TABLE_NAME
  FROM DBA_TAB_PARTITIONS
  WHERE TABLESPACE_NAME IN ('USERS')
  UNION ALL
  SELECT TABLE_NAME
  FROM DBA_TAB_SUBPARTITIONS
  WHERE TABLESPACE_NAME IN ('USERS')
  )
UNION
SELECT 'alter table '
  ||OWNER
  ||'.'
  ||TABLE_NAME
  ||' shrink space compact;' sql1
FROM DBA_TABLES
WHERE TABLESPACE_NAME IN ('USERS')
AND TABLE_NAME NOT    IN
  (SELECT TABLE_NAME
  FROM DBA_TAB_PARTITIONS
  WHERE TABLESPACE_NAME IN ('USERS')
  UNION ALL
  SELECT TABLE_NAME
  FROM DBA_TAB_SUBPARTITIONS
  WHERE TABLESPACE_NAME IN ('USERS')
  )
UNION
SELECT 'alter table '
  ||OWNER
  ||'.'
  ||TABLE_NAME
  ||' shrink space;' sql1
FROM DBA_TABLES
WHERE TABLESPACE_NAME IN ('USERS')
AND TABLE_NAME NOT    IN
  (SELECT TABLE_NAME
  FROM DBA_TAB_PARTITIONS
  WHERE TABLESPACE_NAME IN ('USERS')
  UNION ALL
  SELECT TABLE_NAME
  FROM DBA_TAB_SUBPARTITIONS
  WHERE TABLESPACE_NAME IN ('USERS')
  )
UNION
SELECT 'alter table '
  ||TABLE_OWNER
  ||'.'
  ||TABLE_NAME
  ||' enable row movement;' sql1
FROM DBA_TAB_PARTITIONS
WHERE TABLESPACE_NAME IN ('USERS')
AND SUBPARTITION_COUNT =0
UNION
SELECT 'alter table '
  ||TABLE_OWNER
  ||'.'
  ||TABLE_NAME
  ||' modify partition '
  ||PARTITION_NAME
  ||' SHRINK SPACE;' sql1
FROM DBA_TAB_PARTITIONS
WHERE TABLESPACE_NAME IN ('USERS')
AND SUBPARTITION_COUNT =0
UNION
SELECT 'alter table '
  ||TABLE_OWNER
  ||'.'
  ||TABLE_NAME
  ||' enable row movement;' sql1
FROM dba_tab_subpartitions
WHERE tablespace_name IN ('USERS')
UNION
SELECT 'alter table '
  ||TABLE_OWNER
  ||'.'
  ||TABLE_NAME
  ||' modify subpartition '
  ||SUBPARTITION_NAME
  ||' SHRINK SPACE;' sql1
FROM dba_tab_subpartitions
WHERE tablespace_name IN ('USERS')
UNION
SELECT 'alter table '
  ||owner
  ||'.'
  ||table_name
  ||' modify lob('
  ||column_name
  ||') (shrink space);' sql1
FROM dba_lobs
WHERE TABLESPACE_NAME IN ('USERS')
AND PARTITIONED        ='NO'
UNION
SELECT 'ALTER TABLE '
  ||TABLE_OWNER
  ||'."'
  || TABLE_NAME
  || '" MOVE SUBPARTITION '
  || SUBPARTITION_NAME
  ||'  TABLESPACE '
  ||TABLESPACE_NAME
  ||' LOB ('
  ||COLUMN_NAME
  ||') STORE AS (TABLESPACE '
  ||TABLESPACE_NAME
  ||');'
FROM DBA_LOB_SUBPARTITIONS
WHERE TABLESPACE_NAME IN ('USERS')
union
SELECT 'ALTER TABLE '
  ||TABLE_OWNER
  ||'."'
  || TABLE_NAME
  || '" MOVE PARTITION '
  || PARTITION_NAME
  ||'  TABLESPACE '
  ||TABLESPACE_NAME
  ||' LOB ('
  ||COLUMN_NAME
  ||') STORE AS (TABLESPACE '
  ||TABLESPACE_NAME
  ||');'
FROM DBA_LOB_PARTITIONS
WHERE TABLESPACE_NAME IN ('USERS')
UNION
SELECT 'ALTER INDEX '
  ||INDEX_OWNER
  ||'.'
  || INDEX_NAME
  ||' rebuild partition '
  || PARTITION_NAME
  ||' online;' sql1
FROM DBA_IND_PARTITIONS
WHERE TABLESPACE_NAME IN ('USERS')
AND SUBPARTITION_COUNT =0
UNION
SELECT 'ALTER INDEX '
  ||INDEX_OWNER
  ||'.'
  || INDEX_NAME
  ||' rebuild subpartition '
  || SUBPARTITION_NAME
  ||' online;' sql1
FROM DBA_IND_SUBPARTITIONS
WHERE TABLESPACE_NAME IN ('USERS')
UNION
SELECT 'ALTER INDEX '
  ||OWNER
  ||'.'
  || INDEX_NAME
  ||' rebuild online;' sql1
FROM DBA_INDEXES
WHERE TABLESPACE_NAME IN ('USERS')
AND PARTITIONED        ='NO' ;

RMAN - unregister not existed database from catalog

Normally when I tried unregister non-existed database in RMAN catalog I received error:

execute dbms_rcvcat.unregisterdatabase(818673443,879389610);
BEGIN dbms_rcvcat.unregisterdatabase(818673443,879389610); END;

*
ERROR at line 1:
ORA-02292: integrity constraint (RMAN.TSATT_F2) violated - child record found
ORA-06512: at "RMAN.DBMS_RCV

Probably some informations about backups are still in catalog for this database but without database I can't unregister this datbase.

Solution:

select name,DB_KEY,DBINC_KEY,dbid from rman.rc_database where name=<database_name>;
delete rman.tsatt where DBINC_KEY=<DBINC_KEY for db>;

commit;

execute dbms_rcvcat.unregisterdatabase(<DB_KEY for db>,<DBID for db>);

PL/SQL procedure successfully completed.


 

Changing database link definition in pl/sql

To change database link definition in Oracle database we have to connect as a owner of this database link. In most cases it's possible by using output from dba_db_links and connect from sqlplus as a owner.

But if we use undocumented function dbms_sys_sql.parse_as_user, then it's possible to change definition from pl/sql:

-- if we use specific password schema
define pass_prefix = 'pre'
define pass_postfix = 'post'

DECLARE
  sqltext  VARCHAR2(1000);
  l_result NUMBER;
  l_cursor INTEGER;
  l_cursors dbms_sql.Number_Table;
BEGIN
  -- modify db_links
  FOR cur IN
  (SELECT u.user_id,
    d.OWNER,
    d.DB_LINK,
    d.USERNAME,
    d.HOST
  FROM dba_db_links d,
    dba_users u
  WHERE d.owner=u.username
  )
  LOOP
    --parse the cursor only if we haven't already
    sqltext:= 'alter database link "'||cur.DB_LINK||'" connect to '||cur.USERNAME||' identified by &&pass_prefix'||cur.USERNAME||'&&pass_postfix';
    IF ( NOT l_cursors.exists(cur.user_id) ) THEN
      l_cursors(cur.user_id):=dbms_sys_sql.open_cursor;
      --parsing anonymous PL/SQL block as a job owner
      dbms_sys_sql.parse_as_user( c => l_cursors(cur.user_id), STATEMENT => sqltext, language_flag => dbms_sql.native, userid => cur.user_id );
    END IF;
    --bind the job number
    --remove the job by executing
    l_result:=sys.dbms_sys_sql.execute(l_cursors(cur.user_id));
  END LOOP;
END;
/

RMAN - restore process failed

On test server I had to restore database (3TB).
Due to network issue RESTORE (not recovery) process  failed. In normal situation I have to  start restore process from beginning once again but I already have almost 2.5 TB restored. How to use this data?

Database is in mount mode and in view V$DATAFILE_COPY we have information about file_id and datafile name (this information is needfull if OMF is used).

select 'set newname for datafile '||FILE#||' to '''||name||''';' from V$DATAFILE_COPY where name is not null order by file#;


This query generate set newname for all datafiles already restored.

run {

set until time "to_date('2015-01-05 00:11:00','yyyy-mm-dd hh24:mi:ss')";
<generated set newname> 

restore database;
SWITCH DATAFILE ALL;
SWITCH TEMPFILE ALL;
recover database DELETE ARCHIVELOG MAXSIZE 90G;
}

and if we run this script, rman continue restore process.

If in v$datafile_copy you don't have information for some datafiles, then RMAN not restored then already and for these datafiles you have to created fake name.