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.

Why are statements executed by SYS not found in the DB audit trail?

Even if Oracles default auditing is set up, it surprised me that audit information did not appear in the audit trail, for statements that should be covered by default auditing.

Below are my findings.


First, let's check which audit privileges that are set already:

-- DBA_PRIV_AUDIT_OPTS: describes current system privileges being audited across the system and by user.
SELECT * 
FROM DBA_PRIV_AUDIT_OPTS;

USER_NAME	PROXY_NAME	PRIVILEGE	SUCCESS	FAILURE
		GRANT ANY PRIVILEGE	BY ACCESS	BY ACCESS
		ALTER DATABASE	BY ACCESS	BY ACCESS
		ALTER USER	BY ACCESS	BY ACCESS
		CREATE USER	BY ACCESS	BY ACCESS
		CREATE SESSION	BY ACCESS	BY ACCESS
		ALTER SYSTEM	BY ACCESS	BY ACCESS

Let's audit CREATE USER by user SYSTEM:

SQL> connect / as sysdba
SQL> AUDIT CREATE USER BY SYSTEM;

Check again that the audit trail that is set

SELECT * 
FROM DBA_PRIV_AUDIT_OPTS;

USER_NAME	PROXY_NAME	PRIVILEGE	SUCCESS	FAILURE
		GRANT ANY PRIVILEGE	BY ACCESS	BY ACCESS
		ALTER DATABASE	BY ACCESS	BY ACCESS
		ALTER USER	BY ACCESS	BY ACCESS
SYSTEM		CREATE USER	BY ACCESS	BY ACCESS
		CREATE SESSION	BY ACCESS	BY ACCESS
		ALTER SYSTEM	BY ACCESS	BY ACCESS

Log in as user SYS, and create a user:

SQL> connect sys/****@proddb01 as sysdba
SQL> connected.
SQL> CREATE USER VKAASA IDENTIFIED BY password 
SQL> DEFAULT TABLESPACE USERS
SQL> TEMPORARY TABLESPACE TEMP;

User created.

Log in as user SYSTEM, and create a user:

SQL> connect system/****
SQL> CREATE USER SCOTT IDENTIFIED BY password 
SQL> DEFAULT TABLESPACE USERS
SQL> TEMPORARY TABLESPACE TEMP;

User created.

To find the audit entry, check the DBA_COMMON_AUDIT_TRAIL. This view displays all standard and fine-grained audit trail entries, mandatory and SYS audit records written in XML format.

SELECT AUDIT_TYPE,EXTENDED_TIMESTAMP, DB_USER, OS_USER,USERHOST,OBJECT_NAME,STATEMENT_TYPE,SCN,SQL_TEXT,PRIV_USED
FROM DBA_COMMON_AUDIT_TRAIL 
WHERE PRIV_USED = 'CREATE USER'
AND TO_CHAR(extended_timestamp AT TIME ZONE DBTIMEZONE, 'YYYY-MM-DD HH24:MI:SS TZD') > TO_CHAR((SYSDATE-1),'YYYY-MM-DD HH24:MI:SS');

AUDIT_TYPE	EXTENDED_TIMESTAMP	DB_USER	OS_USER	USERHOST	OBJECT_NAME	STATEMENT_TYPE	SCN	SQL_TEXT	PRIV_USED
Standard Audit	17.12.2014 13:05:06,515954 +01:00	SYSTEM	oracle	myserver	scott	CREATE USER	153820329960	create user scott identified by * default tablespace users temporary tablespace temp	CREATE USER

The same audit entry can be found in DBA_AUDIT_TRAIL, which displays all standard audit trail entries.

SELECT OS_USERNAME,USERNAME,USERHOST,TIMESTAMP,OBJ_NAME,ACTION_NAME,RETURNCODE,PRIV_USED,SCN,SQL_TEXT
FROM DBA_AUDIT_TRAIL
WHERE PRIV_USED = 'CREATE USER'
AND TO_CHAR(extended_timestamp AT TIME ZONE DBTIMEZONE, 'YYYY-MM-DD HH24:MI:SS TZD') > TO_CHAR((SYSDATE-1),'YYYY-MM-DD HH24:MI:SS');

OS_USERNAME	USERNAME	USERHOST	TIMESTAMP	OBJ_NAME	ACTION_NAME	RETURNCODE	PRIV_USED	SCN	SQL_TEXT
oracle	SYSTEM	myserver	17.12.2014 13:05:06	scott	CREATE USER	0	CREATE USER	153820329960	create user scott identified by * default tablespace users temporary tablespace temp

So what happened to the other user, vkaasa, created by SYS? Shouldn't that leave an entry in the audit trail as well?

- You cannot audit actions by sys the same way. Trying to enable audit for user SYS will throw ORA-00983:

SQL> audit create user by sys;
audit create user by sys
*
ERROR at line 1:
ORA-00983: cannot audit or noaudit SYS user actions

Any user creation by sys, which has the sysdba privilege will not be audited. Donald Burleson at Burleson Consulting puts it this way:

"When a user with SYSDBA privileges connects to the database, the action is expected to be for administrative reasons only, such as shutdown, startup, add a service to the listener, etc. Generally, we would not expect these actions to be audited and therefore not picked up by the auditing mechanism.

However, the user who is connected as SYSDBA is a regular user, just like any other user, but with all powerful do anything privileges. This user could potentially alter the data in a table in any schema. Since the action is not audited, the trace of this data manipulation is hidden. In case of an investigation later, the audit trails will have no record of such manipulations ? a huge security hole"

However, any CREATE USER action executed by sys is recorded in the audit trail on disk:

SQL> show parameter audit_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/oracle/admin/proddb01/adump

cd /u01/oracle/admin/proddb01/adump
ls -latr 

-rw-r-----    1 oracle   dba            2754 Dec 17 13:03 proddb01_ora_56099020_20141217130109908038143795.aud
-rw-r-----    1 oracle   dba            1429 Dec 17 13:24 proddb01_ora_56426540_20141217132347629420143795.aud
-rw-r-----    1 oracle   dba           66871 Dec 17 13:27 proddb01_ora_58458244_20141217075116217921143795.aud
drwxrwxr-x    2 oracle   dba           73728 Dec 17 13:33 .
-rw-r-----    1 oracle   dba            2612 Dec 17 13:33 proddb01_ora_57475318_20141217133329412931143795.aud

Open the file proddb01_ora_56426540_20141217132347629420143795.aud:

Wed Dec 17 13:24:37 2014 +01:00
LENGTH : '243'
ACTION :[85] 'create user vkaasa identified by * default tablespace users temporary tablespace temp'
DATABASE USER:[3] 'SYS'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'myuser'
CLIENT TERMINAL:[7] 'myterminal'
STATUS:[1] '0'
DBID:[10] '3707088731'

How can I check which tables that are being audited in my database?

The experiences in this article is applicable to all Oracle versions using classic auditing setup.


To check which tables are currently being audited, use the views DBA_OBJ_AUDIT_OPTS and DBA_PRIV_AUDIT_OPTS.

DBA_PRIV_AUDIT_OPTS describes current system privileges being audited across the system and by user. USERNAME can hold three different values: 

1. The actual user name if by user auditing
2. ANY CLIENT if access by a proxy on behalf of a client is being audited; 
3. NULL for system-wide auditing

DBA_OBJ_AUDIT_OPTS describes auditing options on all objects. 

If no objects or system privileges is being audited, the queries will return zero rows as in the examples below:

SELECT *
FROM DBA_PRIV_AUDIT_OPTS;

no rows selected

SELECT *
FROM DBA_OBJ_AUDIT_OPTS;

no rows selected

Let's start auditing an object:

AUDIT ALL ON SCOTT.BASELINE_STG_TBL;

Audit succeeded.

You can also be more specific and only update certain actions:

AUDIT UPDATE,DELETE,INSERT ON SCOTT.BASELINE_STG_TBL;

Audit succeeded.

Verify that audit is enabled:

SELECT *
FROM DBA_OBJ_AUDIT_OPTS
WHERE OWNER = 'SCOTT'
AND object_name='BASELINE_STG_TBL';

OWNER	OBJECT_NAME	OBJECT_TYPE	ALT	AUD	COM	DEL	GRA	IND	INS	LOC	REN	SEL	UPD	REF	EXE	CRE	REA	WRI	FBK
SCOTT	BASELINE_STG_TBL	TABLE	S/S	S/S	S/S	S/S	S/S	S/S	S/S	S/S	S/S	S/S	S/S	-/-	-/-	-/-	-/-	-/-	S/S

The notation 'S/S' in the different columns of the DBA_OBJ_AUDIT_OPTS is "frequency" where frequency is either "By Session" (S) or "By Access" (A).

The placement of the hyphen, the "S" or the "A" indicates under what circumstances the auditing should be performed, as outlined below:

-/-: no auditing
S/-: auditing whenever successful
-/S: auditing whenever not successful

As can be seen by the result above, by default, auditing is done by SESSION, both for successful and unsuccessful attempts.
Let's turn off auditing for SELECTs

NOAUDIT SELECT ON SCOTT.BASELINE_STG_TBL;

Noaudit succeeded.

Verify that queries on the table are now not being audited:

SELECT *
 FROM DBA_OBJ_AUDIT_OPTS
 WHERE OWNER = 'SCOTT'
 AND object_name='BASELINE_STG_TBL';

OWNER	OBJECT_NAME	OBJECT_TYPE	ALT	AUD	COM	DEL	GRA	IND	INS	LOC	REN	SEL	UPD	REF	EXE	CRE	REA	WRI	FBK
SCOTT	BASELINE_STG_TBL	TABLE	S/S	S/S	S/S	S/S	S/S	S/S	S/S	S/S	S/S	-/-	S/S	-/-	-/-	-/-	-/-	-/-	S/S

Notice that the SEL column now shows a hyphen (-) since the privilege is not audited anymore.

The following statement

AUDIT SELECT ON SCOTT.BASELINE_STG_TBL BY ACCESS WHENEVER NOT SUCCESSFUL;

Would result in

OWNER	OBJECT_NAME	OBJECT_TYPE	ALT	AUD	COM	DEL	GRA	IND	INS	LOC	REN	SEL	UPD	REF	EXE	CRE	REA	WRI	FBK
SCOTT	BASELINE_STG_TBL	TABLE	S/S	S/S	S/S	S/S	S/S	S/S	S/S	S/S	S/S	-/A	S/S	-/-	-/-	-/-	-/-	-/-	S/S

and

AUDIT SELECT ON SCOTT.COUNTRY_TABLE BY ACCESS WHENEVER SUCCESSFUL;

would result in

OWNER	OBJECT_NAME	OBJECT_TYPE	ALT	AUD	COM	DEL	GRA	IND	INS	LOC	REN	SEL	UPD	REF	EXE	CRE	REA	WRI	FBK
SCOTT	BASELINE_STG_TBL	TABLE	S/S	S/S	S/S	S/S	S/S	S/S	S/S	S/S	S/S	A/-	S/S	-/-	-/-	-/-	-/-	-/-	S/S

Keep in mind that mandatory auditing is always turned on. 

Oracle 12.2 Documentation for DBA_OBJ_AUDIT_OPTS can be found here