How to relocate the block change tracking file

To relocate the block change tracking file you have two options:

1) shutdown database, mount database, update control file, open database

sqlplus / as sysdba
shutdown immediate
exit
 -- Move the block change tracking file to the new location using the appropriate os utility. --
sqlplus / as sysdba
startup mount
ALTER DATABASE RENAME FILE 'ora_home/dbs/change_trk.f' TO '/new_disk/change_trk.f'; 
ALTER DATABASE OPEN;

OR

2) disable and re-enable block change tracking, and point to the new location when re-enabling.

ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE 'new_location';

See Section "4.4.4.3 Moving the Change Tracking File" in the Oracle Documentation regarding this feature.

Query the registry!

To view the different options installed in the database, you should use DBA_REGISTRY, as follows:

set lines 200 pages 100
col comp_name format a40
SELECT COMP_NAME,COMP_ID,VERSION,STATUS FROM DBA_REGISTRY;

Example output:

SQL> SELECT COMP_NAME,COMP_ID,VERSION,STATUS FROM DBA_REGISTRY;

COMP_NAME                                COMP_ID                        VERSION                        STATUS
---------------------------------------- ------------------------------ ------------------------------ --------------------------------------------
Oracle Text                              CONTEXT                        11.2.0.4.0                     VALID
Oracle Application Express               APEX                           3.2.1.00.12                    VALID
Oracle Multimedia                        ORDIM                          11.2.0.4.0                     VALID
Oracle XML Database                      XDB                            11.2.0.4.0                     VALID
Oracle Expression Filter                 EXF                            11.2.0.4.0                     VALID
Oracle Rules Manager                     RUL                            11.2.0.4.0                     VALID
Oracle Workspace Manager                 OWM                            11.2.0.4.0                     VALID
Oracle Database Catalog Views            CATALOG                        11.2.0.4.0                     VALID
Oracle Database Packages and Types       CATPROC                        11.2.0.4.0                     VALID
JServer JAVA Virtual Machine             JAVAVM                         11.2.0.4.0                     VALID
Oracle XDK                               XML                            11.2.0.4.0                     VALID
Oracle Database Java Packages            CATJAVA                        11.2.0.4.0                     VALID

12 rows selected.

How to use regexp_substring to extract the 5-digit version

SELECT BANNER, REGEXP_SUBSTR(BANNER, '[[:digit:]]+[[:punct:]]+.[^-]{1,}',1,1) "5 digit Version" 
FROM V$VERSION 
WHERE BANNER LIKE 'Oracle Database%';

Getting ORA-01031: insufficient privileges when data dictionary table is being used in a view

I must admit I have been consulted in these situations before, but since then I had forgotten how it worked and failed to take notes on how to solve it.

So here it is: a user is getting a run-time error ORA-01031: insufficient privileges when accessing his view.
The view is based on his own objects and a lookup to the dynamic performance view V$DATABASE.

Example:

connect scott/tiger

CREATE VIEW MYVIEW AS
SELECT 
FROM MYTABLE MT,
     V$DATABASE DB
WHERE....
AND... ;

If the user has only SELECT ANY TABLE, Oracle will return runtime error ORA-01031 when the view is compiled.

However, if you give user scott the SELECT privilege on the table directly:

GRANT SELECT ON V_$DATABASE TO SCOTT;

then Oracles rules for object creation is honored and the runtime error will disappear.

How to use multiple delimiters in awk

When creating a script for cloning, I wanted to perform some initial checks. One of them was to make sure the listener for the source database was up. For this, I wanted to use the tnsping utility and grep for the result:

tnsping mydb

where mydb corresponds to the tnsnames.ora alias of interest.

* If there was an error, exit script.
* If listener was up, continue.

I directed the output to a file, and after that grepped for the string that would indicate success or failure.
Problem was that the files would look very different and the interesting string would need to be grepped for using two different delimiters.

When successful, the file would look as follows:

OK (10 msec)

When not successful, the output would be:

TNS-03505: Failed to resolve name

So how to pull out either "TNS" or "OK" and use these values programmatically further on in the script?

I accomplished this task simply by using the notation '[-(]'in my awk statement:

REMOTE_LSNR_STATUS=`cat ${LOG_DIR}/test_tnsping.log | egrep -e 'TNS-|OK' | awk -F '[-(]' '{ print $1 }'`

How to move LOB segments in a sub-partitioned table to a new tablespace

Find the sub-partitions that you would like to relocate:

SELECT TABLE_OWNER,TABLE_NAME,COLUMN_NAME,LOB_NAME,SUBPARTITION_NAME,LOB_PARTITION_NAME,SECUREFILE,TABLESPACE_NAME
FROM DBA_LOB_SUBPARTITIONS 
WHERE TABLE_OWNER = 'SCOTT'
AND TABLESPACE_NAME = 'USERS';

TABLE_OWNER	TABLE_NAME	COLUMN_NAME	LOB_NAME	SUBPARTITION_NAME	LOB_PARTITION_NAME	SECUREFILE	TABLESPACE_NAME
SCOTT	TMP_POSK_LEVERANSER	TOTALER	SYS_LOB0000093760C00010$$	P_2013_P_AMELDING	SYS_LOB_P441	NO	USERS
SCOTT	TMP_POSK_LEVERANSER	TOTALER	SYS_LOB0000093760C00010$$	P_2013_P_DEFAULT	SYS_LOB_P441	NO	USERS
SCOTT	TMP_POSK_LEVERANSER	XML_INNHOLD	SYS_LOB0000093760C00020$$	P_2013_P_AMELDING	SYS_LOB_P447	NO	USERS
SCOTT	TMP_POSK_LEVERANSER	XML_INNHOLD	SYS_LOB0000093760C00020$$	P_2013_P_DEFAULT	SYS_LOB_P447	NO	USERS
SCOTT	TMP_POSK_LEVERANSER	OPPSUMMERING	SYS_LOB0000093760C00021$$	P_2013_P_AMELDING	SYS_LOB_P453	NO	USERS
SCOTT	TMP_POSK_LEVERANSER	OPPSUMMERING	SYS_LOB0000093760C00021$$	P_2013_P_DEFAULT	SYS_LOB_P453	NO	USERS

See this post for a more advanced variant that will also show the size of each LOB subpartition

Some examples of correct syntax to move such LOB segments are given below:

-- move a basicfile LOB
alter table SCOTT.TMP_POSK_LEVERANSER move subpartition P_2013_P_AMELDING lob (TOTALER) store as basicfile (tablespace DATA1);

-- move a securefile LOB. Allow for DML and make sure global indexes are not rendered unusable
alter table SCOTT.TMP_POSK_LEVERANSER 
move subpartition P_2013_P_DEFAULT 
lob (OPPSUMMERING) store as securefile (tablespace DATA1) ONLINE UPDATE GLOBAL INDEXES;

-- move a securefile LOB and compress it
alter table SCOTT.TMP_POSK_LEVERANSER 
move subpartition P_2013_P_MELDING 
lob (PDFATTACHMENT) store as securefile (tablespace LOB_DATA COMPRESS MEDIUM);'

Generate the DDLs:

SELECT 'alter table ' || TABLE_OWNER || '.' || TABLE_NAME || ' move subpartition ' || SUBPARTITION_NAME || ' lob (' || COLUMN_NAME || ') store as securefile|basicfile(tablespace DATA1 compress LOW|MEDIUM|HIGH) ONLINE UPDATE GLOBAL INDEXES;' 
FROM DBA_LOB_SUBPARTITIONS 
WHERE TABLE_OWNER = 'SCOTT'
AND TABLESPACE_NAME = 'USERS'
-- to limit your script to certain subpartitions only, uncomment the line below
-- AND SUBPARTITION_NAME='MY_SUBPART_NAME'
;

I have used this script successfully in the past, which generates a log file and sets MODULE and ACTION etc in its session:

accept table_owner prompt 'Table owner: '
accept table_name prompt 'Table name: '
accept new_tabspc prompt 'New tablespace: '
accept old_tabspc prompt 'Old tablespace: '

set lines 300
set heading off
set feedback off
set verify off
set echo off
set pages 0
set trimspool on
spool exec_move_lob_&&table_owner..&&table_name..sql
select 'alter session set nls_language=''american'';' from dual;
select 'alter session force parallel ddl;' from dual;
select 'set timing on' from dual;
select 'set lines 200' from dual;
select 'set trimspool on' from dual;
select 'spool exec_move_lob_&&table_owner..&&table_name..log' from dual;
select 'exec DBMS_APPLICATION_INFO.SET_MODULE(module_name=>''Move'', action_name=>''MoveLOB_&&table_name'');' from dual;
select 'exec DBMS_APPLICATION_INFO.SET_CLIENT_INFO (client_info=>''MoveLOB_&&table_name'');' from dual;
select 'exec DBMS_SESSION.SET_IDENTIFIER (client_id=>''MoveLOB_&&table_name''); ' from dual;


SELECT 'alter table ' || ts.TABLE_OWNER || '.' || ts.TABLE_NAME || ' move subpartition ' || lsp.subpartition_name || ' lob (' || lsp.column_name || ') store as securefile (tablespace &&new_tabspc COMPRESS MEDIUM) ONLINE UPDATE GLOBAL INDEXES;'
FROM dba_segments s JOIN dba_lob_subpartitions lsp
ON (s.partition_name = lsp.lob_subpartition_name)
   JOIN DBA_TAB_SUBPARTITIONS ts
        ON (TS.SUBPARTITION_NAME = lsp.SUBPARTITION_NAME)
WHERE lsp.table_name    = '&&table_name'
AND   ts.table_name     = '&&table_name'
-- To limit the output to a specific tablespace, uncomment the line below
--AND   s.tablespace_name = '&&old_tabspc'
AND   ts.table_owner    = '&&table_owner'
-- To limit output to specific table subpartitions only, uncomment the following row
--AND   lsp.subpartition_name like 'SYS_SUBP186786%'
AND s.segment_name IN ( SELECT lpt.lob_name
                        FROM dba_lob_partitions lpt
                        WHERE lpt.table_name IN ( '&&table_name' ) )
GROUP BY ts.table_owner,ts.table_name,s.SEGMENT_NAME,s.SEGMENT_TYPE,s.SEGMENT_SUBTYPE,s.PARTITION_NAME,lsp.COMPRESSION,lsp.subpartition_name, lsp.column_name,ts.num_rows,lsp.tablespace_name
-- To limit output to lob subpartitions with a specific size, uncomment the restriction above and change the operator to suit your needs ( <, >, =, =>, <= or use BETWEEN x AND y)
--HAVING ROUND(sum(s.bytes)/1024/1024/1024) between 1 and 10
ORDER BY lsp.subpartition_name DESC;

select 'exit' from dual;
exit

How to move LOB segments in a partitioned table to a new tablespace

During an attempt to move a partition that contained a LOB segment from one tablespace to another, I hit the infamous ORA-14511 "Cannot perform operation on a partitioned object" more than once. Neither is the Oracle documentation very clear in the matter.

Here is how I relocated LOB segments in a partitioned table from tablespace USERS to the correct tablespace DATA1.

The original DDL for the was as follows:

CREATE TABLE SCOTT.MYTABLE
(
  BQID                    VARCHAR2(36 BYTE)     NOT NULL,
  KLM_ID                  VARCHAR2(36 BYTE),
  PANUM                   NUMBER(18),
  IAA                     NUMBER(16),
  OPPGVTYP                VARCHAR2(50 BYTE),
  BSTAT                   VARCHAR2(60 BYTE),
  LEVREF                  VARCHAR2(50 BYTE)     NOT NULL,
  KSYSTEM                 VARCHAR2(255 BYTE),
  ANT_OPPG                NUMBER(18),
  TOTALER                 CLOB,
  OPPR                    TIMESTAMP(6)          NOT NULL,
  OPP_KI                  VARCHAR2(30 BYTE)     NOT NULL,
  SIST_ENDRET             TIMESTAMP(6)          NOT NULL,
  XML_INNHOLD             CLOB,
  OPPSUMMERING            CLOB,
  ERSTATTERREFERANSE      VARCHAR2(150 CHAR),
  LEVERANSETIDSPUNKT      TIMESTAMP(6),
  LEVERANSETYPE           VARCHAR2(150 BYTE),
  TMP_MD5_OPPSUMMERING    VARCHAR2(32 BYTE)
)
LOB (TOTALER) STORE AS (
  TABLESPACE  DATA1
 )
LOB (XML_INNHOLD) STORE AS (
  TABLESPACE  DATA1
 )
LOB (OPPSUMMERING) STORE AS (
  TABLESPACE  DATA1
 )
TABLESPACE DATA1
PARTITION BY LIST (TASKTYPE)
(  
  PARTITION P_MESSAGE VALUES ('EZAP')
    TABLESPACE DATA1
    LOB (TOTALER) STORE AS (
      TABLESPACE  USERS
    )
    LOB (XML_INNHOLD) STORE AS (
      TABLESPACE  USERS
    )
    LOB (OPPSUMMERING) STORE AS (
      TABLESPACE  USERS
    )
    ,  
  PARTITION P_DEFAULT VALUES (DEFAULT)
    TABLESPACE DATA1
    LOB (TOTALER) STORE AS (
      TABLESPACE  USERS
    )
    LOB (XML_INNHOLD) STORE AS (
      TABLESPACE  USERS
    )
    LOB (OPPSUMMERING) STORE AS (
      TABLESPACE  USERS
    )
)
;

A query against DBA_LOB_PARTITIONS shows that they are indeed placed in tablespace USERS:

SELECT TABLE_OWNER,TABLE_NAME,COLUMN_NAME,LOB_NAME,PARTITION_NAME,LOB_PARTITION_NAME,SECUREFILE,TABLESPACE_NAME
FROM DBA_LOB_PARTITIONS 
WHERE TABLE_OWNER = 'SCOTT' 
AND TABLESPACE_NAME = 'USERS'
AND PARTITION_NAME = 'P_AMELDING';

TABLE_OWNER	TABLE_NAME	COLUMN_NAME	LOB_NAME	PARTITION_NAME	LOB_PARTITION_NAME	SECUREFILE	TABLESPACE_NAME
SCOTT	MYTABLE	TOTALER	SYS_LOB0000093789C00010$$	P_AMELDING	SYS_LOB_P701	NO	USERS
SCOTT	MYTABLE	XML_INNHOLD	SYS_LOB0000093789C00020$$	P_AMELDING	SYS_LOB_P703	NO	USERS
SCOTT	MYTABLE	OPPSUMMERING	SYS_LOB0000093789C00021$$	P_AMELDING	SYS_LOB_P705	NO	USERS

The correct syntax to move these LOB segments is:

ALTER TABLE SCOTT.MYTABLE
MOVE PARTITION P_AMELDING
LOB (TOTALER) STORE AS BASICFILE (TABLESPACE DATA1);

ALTER TABLE SCOTT.MYTABLE
MOVE PARTITION P_AMELDING
LOB (OPPSUMMERING) STORE AS BASICFILE (TABLESPACE DATA1);

ALTER TABLE SCOTT.MYTABLE
MOVE PARTITION P_AMELDING
LOB (XML_INNHOLD) STORE AS BASICFILE (TABLESPACE DATA1);

To generate DDL statements:

SELECT 'alter table ' || TABLE_OWNER || '.' || TABLE_NAME || ' move partition ' || partition_name || ' lob (' || COLUMN_NAME || ') store as basicfile (tablespace data1);'
FROM DBA_LOB_PARTITIONS 
WHERE TABLE_OWNER = 'SCOTT' 
AND TABLESPACE_NAME = 'USERS'
AND SECUREFILE='NO';

If the table is also sub-partitioned, you may want to check this post.