How to use dbms_metadata to generate DDL for a Unified Auditing Policy

SQL> set long 5000
SQL> select DBMS_METADATA.GET_DDL('AUDIT_POLICY','ORA_LOGON_FAILURES')  from dual;

DBMS_METADATA.GET_DDL('AUDIT_POLICY','ORA_LOGON_FAILURES')
--------------------------------------------------------------------------------

   CREATE AUDIT POLICY "ORA_LOGON_FAILURES" ACTIONS  LOGON

How to generate dbms_application_info settings together with DDL in a sql script

Many DBAs forget to incoporate usage of dbms_application_info into their own scipts. This is particulary important if you start long-running maintenance jobs that could last for days or weeks.

By sending MODULE and ACTION to the databaser server, you allow for more granular searching using v$session, end-to-end tracing using trcsess. In addtion, more information is collected by any ongoing auditing so it's easier to see who did what later.

Here is a sniplet that can be used when generating a DDL script for multiple objects.

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;

-- your DDL statements generated here, for example:
select 'alter table ' || owner || '.' || table_name || ' move tablespace data2 online;'
from dba_tables
where owner='&&table_owner';

select 'exit' from dual;
exit

Identity columns in Oracle 12c

The identity column introduced in Oracle 12c can be used in the different manners, according to your needs. Here are some basic examples:

create table songs (
    song_id number generated always as identity,
    title varchar2(128)
);

The "generated always" clause is default and may be omitted.

In this scenario, no inserts may contain a value for the column song_id, even if the datatype is correct. Neither null values are accepted. Oracle will generate its own value and will not allow any manipulation of the value recorded in the identity column.


Allow for the possibility to override the auto generated numbers by specifying "by default" instead of "always":

create table songs (
    song_id number generated by default as identity,
    title varchar2(128)
);

In other words, the statements below would both be permissible:

insert into songs (song_id, title)values(10,'Born to run');
update songs set song_id=20 where song_id=10;

However, null cannot be specified as a legitimate value for the identity column:

insert into songs values(null, 'White wedding');

would return the error

ORA-01400: cannot insert NULL into ("MUSIC"."SONGS"."SONG_ID")

If you need to allow for NULL values in the identity column, you create the table as follows:

create table songs (
    song_id number generated by default on null as identity,
    title varchar2(128)
);

With the above definition, Oracle

* permits overriding the autogenerated numbers
* allows update of the identity column
* allows null values to be specified during inserts

All of the three statements below are thus valid:

insert into songs (song_id, title)values(10,'Born to run');
update songs set song_id=20 where song_id=10;
insert into songs values(null, 'White wedding');

Tim Hall has published a more in-depth article about the same topic, as usual of the highest quality

Will interrupting a "split partition"-command cause any harm to your database?

Will interrupting a "alter table split partition"-command cause any harm to your database?

No, it will not. It is safe to kill the session. Oracle will be able to recover and the table would be left in its original state, prior to time when the "alter table split partition" command was executed.

I was recently in contact with Oracle support regarding such a case. My goal was to split a sub-partition, in order to accommodate rows for years 2017 to 2020, but there was way too much concurrent activities in the database to complete the "alter table split partition"-command.
The database was almost completely non-responsive and there was pressure to finish within a certain time frame.

The supporting engineer confirmed that there would be some rollback activities in the wake of this command, how much depended on the activity level in the database during the time when the DDL was executing.

He added the following explanation to how Oracle handles the split internally:

As for kill the split partition, the way it works internally is to create temporary segments for the 2 new partitions it is splitting into, and when the operation is complete, these new temporary segments are attached to the original table and become the new partitions. So the result of this is that it's perfectly safe to terminate the split partition, and it simply stops its work, drops the temporary segments, and you are left with the original table in-tact, so there will be no ill-effects of this.

Syntax for setting the default undo tablespace for the instance

ALTER SYSTEM SET undo_tablespace='APPS_UNDOTS1' SCOPE=BOTH;

How to use dbms_metadata to generate DDL for profiles

An easy way to migrate your profiles from a source database to a target database during migration is to use the dbms_metadata package.

To generate one call for each profile:

SELECT UNIQUE 'SELECT DBMS_METADATA.GET_DDL(''PROFILE'',' || ''''|| PROFILE || ''') FROM DUAL;'
FROM DBA_PROFILES;

In my case, the result was a total of three profiles. Use the resulting rows in the script below:

SET HEADING OFF
SET TRIMSPOOL ON
SET FEEDBACK OFF
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR', TRUE);
-- your calls to dbms_metadata here
SELECT DBMS_METADATA.GET_DDL('PROFILE','PROF') from dual;
SELECT DBMS_METADATA.GET_DDL('PROFILE','DEFAULT') from dual;
SELECT DBMS_METADATA.GET_DDL('PROFILE','ONLINEUSR') from dual;

Script example: create database

CREATE DATABASE "mydb"
    MAXINSTANCES 8
    MAXLOGHISTORY 1
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
DATAFILE '/u02/oradata/mydb/system01.dbf' SIZE 512M REUSE AUTOEXTEND ON NEXT  32M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL 
SYSAUX DATAFILE '/u02/oradata/mydb/sysaux01.dbf' SIZE 512M REUSE AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED 
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u02/oradata/mydb/temp01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE '/u02/oradata/mydb/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('/u03/oradata/mydb/redo01.log') SIZE 2048M,
             GROUP 2 ('/u03/oradata/mydb/redo02.log') SIZE 2048M,
             GROUP 3 ('/u03/oradata/mydb/redo03.log') SIZE 2048M
USER SYS IDENTIFIED BY "&&sysPassword"
USER SYSTEM IDENTIFIED BY "&&systemPassword";

Note 1:
You can exchange "SMALLFILE" with "BIGFILE" for any tablespace, as long as they are locally managed with automatic segment space management.

There are three exceptions to this rule: locally managed undo tablespaces, temporary tablespaces, and the SYSTEM tablespace does not have to use automatic segment space management; they *must* use manual segment space management but can still be created as BIGFILE. In one of my databases, I have the following setup:

SELECT tablespace_name,bigfile,extent_management,segment_space_management 
FROM dba_tablespaces;

TABLESPACE_NAME                          BIGFILE   EXTENT_MANAGEMENT              SEGMENT_SPACE_MANA
---------------------------------------- --------- ------------------------------ ------------------
SYSTEM                                   NO        LOCAL                          MANUAL
SYSAUX                                   NO        LOCAL                          AUTO
UNDOTBS1                                 YES       LOCAL                          MANUAL
TEMP                                     YES       LOCAL                          MANUAL
USERS                                    NO        LOCAL                          AUTO

Note 2:
The NATIONAL CHARACTER SET clause specifies the "national character set used to store data in columns specifically defined as NCHAR, NCLOB, or NVARCHAR2. Valid values are AL16UTF16 and UTF8. The default is AL16UTF16."

How to use the dbms_metadata package to generate DDL for a user

-- Arg #1 schema name
-- Arg #2 DB Directory
whenever sqlerror exit 1
whenever oserror  exit 2
set long 100000 verify off feedback off
accept user_name prompt 'User name: '
accept directory_name prompt 'Directory name to write to: '
declare
begin
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
declare
no_grant      EXCEPTION;
PRAGMA EXCEPTION_INIT (no_grant, -31608);
CURSOR get_username
IS
SELECT username
FROM all_users
WHERE username = UPPER('&user_name');

file_handle   UTL_FILE.file_type;
stmt          CLOB;
BEGIN
file_handle := UTL_FILE.fopen(UPPER('&directory_name'), 'cr_user_&user_name..sql', 'w', 32767);

FOR l_user IN get_username
LOOP

-- USERS
stmt:=DBMS_METADATA.get_ddl('USER', l_user.username)||chr(10);
begin
stmt:=stmt||'---------------------------------------'||chr(10);
stmt:=stmt||'-- SYSTEM_GRANT'||chr(10);
stmt:=stmt||'---------------------------------------'||chr(10);
stmt:=stmt||DBMS_METADATA.get_granted_ddl('SYSTEM_GRANT', l_user.username)||chr(10);
exception
when no_grant then stmt := stmt||'-- no system grants'||chr(10);
end;
begin
stmt:=stmt||'---------------------------------------'||chr(10);
stmt:=stmt||'-- OBJECT_GRANT'||chr(10);
stmt:=stmt||'---------------------------------------'||chr(10);
stmt:=stmt||DBMS_METADATA.get_granted_ddl('OBJECT_GRANT', l_user.username)||chr(10);
exception
when no_grant then stmt := stmt||'-- no object grants'||chr(10);
end;
begin
stmt:=stmt||'---------------------------------------'||chr(10);
stmt:=stmt||'-- ROLE_GRANT'||chr(10);
stmt:=stmt||'---------------------------------------'||chr(10);
stmt := stmt||DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', l_user.username)||chr(10);
exception
when no_grant then stmt := stmt||'-- no role grants'||chr(10);
end;
begin
stmt:=stmt||'---------------------------------------'||chr(10);
stmt:=stmt||'-- TABLESPACE_QUOTA'||chr(10);
stmt:=stmt||'---------------------------------------'||chr(10);
stmt:=stmt||DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA',l_user.username)||chr(10);
exception
when no_grant then stmt := stmt||'-- no tablespace quota'||chr(10);
end;
begin
stmt:=stmt||'---------------------------------------'||chr(10);
stmt:=stmt||'-- DEFAULT_ROLE'||chr(10);
stmt:=stmt||'---------------------------------------'||chr(10);
stmt := stmt||DBMS_METADATA.GET_GRANTED_DDL('DEFAULT_ROLE', l_user.username)||chr(10);
exception
when no_grant then stmt := stmt||'-- no default role'||chr(10);
end;

UTL_FILE.put (file_handle, stmt);

end loop;
UTL_FILE.fclose (file_handle);
END;
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',FALSE);
end;

/
exit

How to use the dbms_metadata package to generate DDL for table

accept table_name prompt 'Table name: '
accept owner prompt 'Table owner: '

set long 90000
set longchunksize 300
set pagesize 0
set linesize 300
set trimspool on
set heading off
set verify off
set echo off
set feedback off
spool table_ddl.lst

exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',FALSE);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',TRUE);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
select dbms_metadata.get_ddl('TABLE',UPPER('&&table_name'),UPPER('&&owner'))
from dba_tables where table_name = UPPER('&&table_name');
exit

How to set a timeout for DDL operations in oracle11g - to avoid "resource busy" error

ALTER SESSION SET DDL_LOCK_TIMEOUT = 10;

when a DDL statement in the session does not get the exclusive lock, it will not error out.
Instead, it will wait for 10 seconds. In that 10 seconds, it continually re-tries the DDL operation until it is successful or the time expires, whichever comes first.

For exammple, if the following query is executed:


SQL> alter table sales add (tax_code varchar2(10));

the statement hangs and does not error out.

Arup Nanda puts it as

somewhat like a telephone programmed to re-try a busy number

To set it database-wide:

ALTER SYSTEM SET DDL_LOCK_TIMEOUT = 10;

The reason for "ORA-12052: cannot fast refresh materialized view" during Online Redefinition

Online Redefinition of an object relies on Material View (MView) logs to keep track of all data changes in the table under redefinition *while* the redefintion is going on.

If there is any change in the definition (DDL), then there is no guarantee that the Mview logs can capture all changes (they can only track DML )

The internal process works like this:

- Set up Mview log to track any changes on the table and consider the temp table as an MView of the original table
- Transfer all the data in the original table to the temporary table
- WHILE this is going on, any DATA changes to the table will be logged in the MView log
- If you perform a SYNC, Oracle essentially performs a "MVIEW FAST REFRESH" using the MView log to replicating the data changes. The MView log is then cleared.
- Once the data transfer is complete, the MView log (if not empty) is processed - the "MView" / temp table is now up-to-date.
- Upon executing dbms_redefinition.finish_redef_table, the MView log is dropped, the MView is converted back to simple table.
- After that, the names of the original and temp table are switched automatically.

If at any point during this process, the structure of the table is changed or DDL in general is executed (say, TRUNCATE, adding a column, dropping a partition, exchanging a partition), then the MView log cannot in itself guarantee it has all the information needed to "replicate" this change of the original table.

Hence, the MView log is regarded as "unusable" and the error you observed occurs:


ORA-42009: error occurred while synchronizing the redefinition
ORA-12052: cannot fast refresh materialized view BWALM.TOMBA
ORA-06512: at "SYS.DBMS_REDEFINITION", line 119
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1740
ORA-06512: at line 2


Oracle therefore recommend to avoid all DDL on the original table while ONLINE REDEFINITION is ongoing.