Exchange partitions with local unique and primary keys

The following steps are a typical approach for bulk loading a large set of data into a partitioned table;

1. SQL Loader or bulk INSERT into non partitioned table with no indexes
2. Create indexes and/or primary key constraints (if need be, in parallel)
3. Exchange partitions
4. Rebuild local and/or global indexes
5. Gather optimizer statistics

Exchanging partitions with a non-partitioned tables requires a little care when dealing with the partitioned tables indexes.

Firstly, create a sample partitioned table:

SQL> create table Trade_Account
  2  (
  3     Acc_ID           number not null
  4    ,Date_Created     date   not null
  5    ,is_Joint_Account varchar2(1) default 'N' not null
  6    ,Acc_Type_ID      number
  7  )
  8  partition by range (date_created)
  9  (
 10  partition p131109 values less than(to_date('2013-11-10', 'YYYY-MM-DD')),
 11  partition p131110 values less than(to_date('2013-11-11', 'YYYY-MM-DD')),
 12  partition p131111 values less than(to_date('2013-11-12', 'YYYY-MM-DD')),
 13  partition p131112 values less than(to_date('2013-11-13', 'YYYY-MM-DD')),
 14  partition p131113 values less than(to_date('2013-11-14', 'YYYY-MM-DD')),
 15  partition p131114 values less than(to_date('2013-11-15', 'YYYY-MM-DD')),
 16  partition pmax values less than (maxvalue)
 17  );

Table created.

Next, create a sample loading table:

SQL> create table trade_account_load as (select * from trade_account);

Table created.

Create sample data in both tables:

SQL> insert into Trade_Account values
  2  (1,to_date('20131112','yyyymmdd'),'Y',1);

1 row created.

SQL> insert into Trade_Account values
  2  (2,to_date('20131113','yyyymmdd'),'N',1);

1 row created.
SQL> insert into Trade_Account_load values
  2  (3,to_date('20131113','yyyymmdd'),'Y',1);

1 row created.

SQL> insert into Trade_Account_load values
  2  (4,to_date('20131113','yyyymmdd'),'N',1);

1 row created.

SQL> commit;

Gather stats and show how many rows exist in each partition:

SQL> exec dbms_stats.gather_table_stats(sys_context('userenv','current_schema'), 'TRADE_ACCOUNT');

PL/SQL procedure successfully completed.

SQL> select partition_name,num_rows
  2  from user_tab_partitions
  3  where table_name = 'TRADE_ACCOUNT';

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
P131109                                 0
P131110                                 0
P131111                                 0
P131112                                 1
P131113                                 1
P131114                                 0
PMAX                                    0

7 rows selected.
 
Create a global index first and then attempt an exchange:

SQL> alter table trade_account
  2  add constraint trade_account_pk primary key (acc_id);

Table altered.

SQL> alter table trade_account
  2  exchange partition P131113 with table trade_account_load;
alter table trade_account
*
ERROR at line 1:
ORA-14130: UNIQUE constraints mismatch in ALTER TABLE EXCHANGE PARTITION


The reason for  the exception is that the unique index that is used to enforce the primary key constraint is not local. Retry with a local index:

SQL> alter table trade_account drop constraint trade_account_pk;

Table altered.

SQL> alter table trade_account
  2  add constraint trade_account_pk primary key (acc_id) using index local;
alter table trade_account
*
ERROR at line 1:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index

  

Attempting to create a local unique index, regardless of whether the index is used to enforce a primary key constraint, without the partition key generates an exception. Another exception will occur if the primary key on the non-partitioned table does not exist, for example:

 SQL> alter table trade_account
  2  add constraint trade_account_pk
  3  primary key (acc_id,date_created) using index local;

Table altered.

SQL> alter table trade_account
  2  exchange partition P131113 with table trade_account_load;
alter table trade_account
*
ERROR at line 1:
ORA-14130: UNIQUE constraints mismatch in ALTER TABLE EXCHANGE PARTITION

 The partitioning alignment will be shown as either PREFIXED or NON-PREFIXED, for example:

PREFIXED

SQL> alter table trade_account
  2  add constraint trade_account_pk
  3  primary key (date_created, acc_id) using index local;

Table altered.

SQL> select upi.table_name
  2        ,upi.index_name
  3        ,upi.partitioning_type
  4        ,upi.locality,alignment
  5  from
  6         user_part_indexes upi
  7  where
  8         upi.table_name = 'TRADE_ACCOUNT';

TABLE_NAME      INDEX_NAME        PARTITION LOCALI ALIGNMENT
--------------- ----------------- --------- ------ ------------
TRADE_ACCOUNT   TRADE_ACCOUNT_PK  RANGE     LOCAL  PREFIXED

NON-PREFIXED

SQL> alter table trade_account drop constraint trade_account_pk;

Table altered.

SQL> alter table trade_account
  2  add constraint trade_account_pk
  3  primary key (acc_id, date_created) using index local;

Table altered.

It is worth creating unqiue/primary key constraints separately to the unique index, since one can disable and re-enable the constraint without dropping the index. When loading a large volume of rows the constraint can be disabled, but the index will be maintained. There are several ways to create them seperately;

Simplest way:

SQL> alter table trade_account
  2  add constraint trade_account_pk primary key(acc_id, date_created)
  3  using index
  4  (create unique index trade_account_pk
  5   on trade_account(acc_id,date_created) local
  6  );

Table altered.

Or, as two seperate DDL statements:

SQL> alter table trade_account drop constraint trade_account_pk;

Table altered.

SQL> create unique index trade_account_pk
  2  on trade_account(acc_id,date_created) local;

Index created.

SQL> alter table trade_account
  2  add constraint trade_account_pk primary key (acc_id,date_created)
  3  using index trade_account_pk;

Table altered.

The following indicates how all the local partitioned indexes remain usable even though the constraint has been disabled.

SQL> alter table trade_account drop constraint trade_account_pk;

Table altered.

SQL> create unique index trade_account_pk
  2  on trade_account(acc_id,date_created) local;

Index created.

SQL> alter table trade_account
  2  add constraint trade_account_pk primary key (acc_id,date_created)
  3  using index trade_account_pk;

Table altered.

SQL> alter table trade_account disable constraint trade_account_pk;

Table altered.

SQL>
SQL> select uip.index_name
  2        ,uip.partition_name
  3        ,uip.status
  4  from   user_ind_partitions uip
  5  where  uip.index_name = 'TRADE_ACCOUNT_PK'
  6  order by 2;

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
TRADE_ACCOUNT_PK               P131109                        USABLE
TRADE_ACCOUNT_PK               P131110                        USABLE
TRADE_ACCOUNT_PK               P131111                        USABLE
TRADE_ACCOUNT_PK               P131112                        USABLE
TRADE_ACCOUNT_PK               P131113                        USABLE
TRADE_ACCOUNT_PK               P131114                        USABLE
TRADE_ACCOUNT_PK               PMAX                           USABLE

7 rows selected.

SQL> select status from user_constraints
  2  where constraint_name = 'TRADE_ACCOUNT_PK';

STATUS
--------
DISABLED


The index will now appear as NON_PREFIXED in the data dictionary.

 SQL> select upi.table_name
  2        ,upi.index_name
  3        ,upi.partitioning_type
  4        ,upi.locality,alignment
  5  from
  6         user_part_indexes upi
  7  where
  8         upi.table_name = 'TRADE_ACCOUNT';

TABLE_NAME      INDEX_NAME        PARTITION LOCALI ALIGNMENT
--------------- ----------------- --------- ------ ------------
TRADE_ACCOUNT   TRADE_ACCOUNT_PK  RANGE     LOCAL  NON_PREFIXED

Be aware that there are performance implications for non-prefixed local indexes. Refer to the 11gR2 docuementaion here for more detail.

Create a primary key on the non-partitioned table that matches the primary key on the partitioned table in terms of the columns used and then retry the exchange:

SQL> alter table trade_account_load add constraint trade_account_load_pk
  2  primary key (acc_id,date_created);

Table altered.

SQL> alter table trade_account
  2  exchange partition P131113 with table trade_account_load;

Table altered.

Rebuild the unusable indexes. If the indexes are local, then only the index local to the partition that was exchanged would have to be rebuilt. If the index is global then the entire global index requires rebuilding, which could take a considerable amount of time, so it might be worth investigating whether the rebuild can occur in parallel.

SQL> select uix1.index_name
  2        ,null as partition_name
  3        ,uix1.status
  4  from   user_indexes uix1
  5  where  uix1.table_name  = 'TRADE_ACCOUNT'
  6  and    uix1.partitioned = 'NO'
  7  union all
  8  select uip.index_name
  9        ,uip.partition_name
 10        ,uip.status
 11  from   user_ind_partitions uip
 12  inner join user_indexes uix2 on uip.index_name = uix2.index_name
 13                              and uix2.table_name = 'TRADE_ACCOUNT'
 14  order by 2;

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
TRADE_ACCOUNT_PK               P131109                        USABLE
TRADE_ACCOUNT_PK               P131110                        USABLE
TRADE_ACCOUNT_PK               P131111                        USABLE
TRADE_ACCOUNT_PK               P131112                        USABLE
TRADE_ACCOUNT_PK               P131113                        UNUSABLE
TRADE_ACCOUNT_PK               P131114                        USABLE
TRADE_ACCOUNT_PK               PMAX                           USABLE

7 rows selected. 

There are no global indexes, but a single local partition index that is unusable;

SQL> alter index TRADE_ACCOUNT_PK rebuild partition P131113;

Index altered.

SQL> select uix1.index_name
  2        ,null as partition_name
  3        ,uix1.status
  4  from   user_indexes uix1
  5  where  uix1.table_name  = 'TRADE_ACCOUNT'
  6  and    uix1.partitioned = 'NO'
  7  union all
  8  select uip.index_name
  9        ,uip.partition_name
 10        ,uip.status
 11  from   user_ind_partitions uip
 12  inner join user_indexes uix2 on uip.index_name = uix2.index_name
 13                              and uix2.table_name = 'TRADE_ACCOUNT'
 14  order by 2;

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
TRADE_ACCOUNT_PK               P131109                        USABLE
TRADE_ACCOUNT_PK               P131110                        USABLE
TRADE_ACCOUNT_PK               P131111                        USABLE
TRADE_ACCOUNT_PK               P131112                        USABLE
TRADE_ACCOUNT_PK               P131113                        USABLE
TRADE_ACCOUNT_PK               P131114                        USABLE
TRADE_ACCOUNT_PK               PMAX                           USABLE

7 rows selected.

Finally, run the stats gathering again to verify whether the rows were loaded:

SQL> exec dbms_stats.gather_table_stats(sys_context('userenv','current_schema'), 'TRADE_ACCOUNT');

PL/SQL procedure successfully completed.

SQL> select partition_name,num_rows
  2  from user_tab_partitions
  3  where table_name = 'TRADE_ACCOUNT';

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
P131109                                 0
P131110                                 0
P131111                                 0
P131112                                 1
P131113                                 2
P131114                                 0
PMAX                                    0

7 rows selected.

Unix variables set from SQL Plus

Passing a parameter out of a SQL script to a UNIX variable. Method 1 outputs a literal. Method 2 will output a variable entered by a user.

Method 1

Create a file, "oranum.sql", which looks as follows:

set heading off
connect user/pwd@oraserver
select 10 from dual;
exit

run it as follows:

ORANUM=`sqlplus -s /nolog @oranum.sql`
echo $ORANUM

Method 2

#!/bin/ksh
echo "Enter value"
read val2
sqlplus $ORA_USER/$ORA_PWD@$ORA_HOST  << EOF1
set serveroutput on
begin
  dbms_output.put_line('${val2}');
end;
/
exit
EOF1

How to use UNIX environment variables in SQL Plus

Passing a variable to a SQL script in UNIX.

Method 1

SOME_STR="Some Str"
sqlplus apps/password@oracle_sid <<EOF
set serveroutput on
begin
  dbms_output.put_line('${SOME_STR}');
end;
/
exit
EOF

Method 2

Create a test file, test_str.sql, to test with first.

$ more test_str.sql
set serveroutput on
declare

  l_var1 varchar2(100):='&&1';
  l_var2 varchar2(100):='&&2';

begin
  dbms_output.put_line(l_var1);
  dbms_output.put_line(l_var2);
end;
/
exit

Now for the test

$ more test_str.ksh
VAR1="Accounts Payable"
VAR2="Receivable"
sqlplus -s apps/password@oracle_sid @test_str.sql $VAR1 $VAR2


$ . test_str.ksh
old   3:   l_var1 varchar2(100):='&&1';
new   3:   l_var1 varchar2(100):='Account Payable';
old   4:   l_var2 varchar2(100):='&&2';
new   4:   l_var2 varchar2(100):='Receivable';

Accounts Payable
Receivable

Generating dynamic spool files in SQL Plus

The following example will append YYYYMMDD_HHMISS to the spool file name.

set echo on
column spool_file new_val spool_file;
select 'dynamic_spool_file_' 
       || to_char(sysdate, 'yyyymmdd_hh24miss' ) spool_file 
from dual;
spool &spool_file
select 'file name : &spool_file' as spool_file_name from dual;
set echo off
spool off

Identify which tables/columns are being queried the most.

select o.owner
      ,o.object_name         as Table_Name
      ,c.column_name         as Column_Name
      ,u.equality_preds
       + u.equijoin_preds
       + u.nonequijoin_preds
       + u.range_preds
       + u.like_preds
       + u.null_preds        as Usage_Total
      ,u.timestamp           as Last_Used
from
       sys.col_usage$        u
      ,dba_objects           o
      ,dba_tab_columns       c
where
       o.object_id           = u.obj#
and    c.table_name          = o.object_name
and    c.column_id           = u.intcol#
and    o.object_type         = 'TABLE'
and    o.owner               = upper('&owner')
order by 4 desc
/

Indentify unindexed Foreign Key columns

The following statement lists unindexed foreign key columns. The statement covers single as well as multi-column foreign key.

undefine schema_owner
set pagesize 5000
set linesize 350
column status format a10
column table_name format a30
column fk_name format a30
column fk_columns format a30
column index_name format a30
column index_columns format a30

select
        case
          when b.table_name is null then
            'unindexed'
          else
            'indexed'
        end               as status
       ,a.table_name      as table_name
       ,a.constraint_name as fk_name
       ,a.fk_columns      as fk_columns
       ,b.index_name      as index_name
       ,b.index_columns   as index_columns
from
    (
      select a.table_name
            ,a.constraint_name
            ,listagg(a.column_name, ',') within
             group (order by a.position) fk_columns
      from
             dba_cons_columns   a
            ,dba_constraints    b
      where
             a.constraint_name  = b.constraint_name
      and    b.constraint_type  = 'R'
      and    a.owner            = '&&schema_owner'
      and    a.owner            = b.owner
      group by a.table_name, a.constraint_name
    ) a
   ,(
      select table_name
            ,index_name
            ,listagg(c.column_name, ',') within
             group (order by c.column_position) index_columns
      from
             dba_ind_columns  c
      where  c.index_owner    = '&&schema_owner'
      group BY table_name, index_name
    ) b
where
      a.table_name        =     b.table_name(+)
and   b.index_columns(+)  like  a.fk_columns || '%'
order by 1 desc, 2;
 
Save the above statement in a file, identify_unindexed_foreign_keys.sql. 
Now test:

SQL> create table t1 (a number, b number);

Table created.

SQL> alter table t1 add constraint t1_pk primary key (a,b);

Table altered.

SQL> create table t2 (a number, b number, c number);

Table created.

SQL> alter table t2 add constraint t2_fk foreign key (a,b)
  2  references t1(a,b);

Table altered.

SQL> @identify_unindexed_foreign_keys.sql
Enter value for schema_owner: USERNAME

STATUS     TABLE_NAME FK_NAME FK_COLUMNS INDEX_NAME INDEX_COLUMNS
---------- ---------- ------- ---------- ---------- -------------
unindexed  T2         T2_FK   A,B 

SQL> create index idx_t2 on t2 (a,b);

Index created.

SQL> @identify_unindexed_foreign_keys.sql
Enter value for schema_owner: USERNAME

STATUS     TABLE_NAME FK_NAME FK_COLUMNS INDEX_NAME INDEX_COLUMNS
---------- ---------- ------- ---------- ---------- -------------
indexed    T2         T2_FK   A,B        IDX_T2     A,B
The above statement does not however list indexes on composite foreign keys where the column order in the index differs to that of the foreign key constraint.

For example:

SQL> drop index idx_t2;

Index dropped.

SQL> create index idx_t2 on t2 (b,a);

Index created.

SQL> @identify_unindexed_foreign_keys.sql
Enter value for schema_owner: USERNAME

STATUS     TABLE_NAME FK_NAME FK_COLUMNS INDEX_NAME INDEX_COLUMNS
---------- ---------- ------- ---------- ---------- -------------
unindexed  T2         T2_FK   A,B


The statement requires some modification so as to list foreign keys as "indexed", regardless of the column order of the composite index.

the modified statement:

select
      case
        when i.index_name is not null then
          'indexed'
        else
          'unindexed'
       end                  as status
      ,c.table_name         as table_name
      ,c.constraint_name    as fk_name
      ,c.fk_columns         as fk_columns
      ,i.index_name         as index_name
      ,i.index_columns      as index_columns
from
    (
      select  a.table_name
             ,a.constraint_name
             ,listagg(b.column_name, ' ' )
              within group (order by column_name) as fk_columns
      from
              dba_constraints a
             ,dba_cons_columns b
      where
              a.constraint_name = b.constraint_name
      and     a.constraint_type = 'R'
      and     a.owner           = b.owner
      and     a.owner           = '&&schema_owner'
      group by
              a.table_name
             ,a.constraint_name
    ) c
left outer join
  (
    select  table_name
           ,index_name
           ,cr
           ,listagg(column_name, ' ')
            within group (order by column_name) as index_columns
    from
        (
          select
                  table_name
                 ,index_name
                 ,column_position
                 ,column_name
                 ,connect_by_root(column_name) cr
          from
                  dba_ind_columns
          where
                  index_owner               = '&&schema_owner'
          connect
                 by prior column_position-1 = column_position
                 and prior index_name       = index_name
         )
    group by table_name
            ,index_name, cr
  ) i
      on c.fk_columns = i.index_columns
     and c.table_name = i.table_name;

the foreign key will now be shown as "indexed", once the script file has been modified :-)

SQL> @identify_unindexed_foreign_keys.sql
Enter value for schema_owner: USERNAME

STATUS     TABLE_NAME FK_NAME FK_COLUMNS INDEX_NAME INDEX_COLUMNS
---------- ---------- ------- ---------- ---------- -------------
indexed    T2         T2_FK   A,B        IDX_T2     A,B

Concatenate CLOB's without using PL/SQL functions

The following SQL concatenates clobs from a table into a
single clob without the use of PL/SQL functions.
create table t_clobs (c clob)
/
insert into t_clobs values (to_clob('A'));
insert into t_clobs values (to_clob('B'));
insert into t_clobs values (to_clob('C'));
commit;

select xmlserialize
        (
          content
            xmlagg(xmlelement(c, c)).extract('//text()') as clob
        ) as clobs_concatenated
from t_clobs;


SQL> select xmlserialize(content
  2           xmlagg(xmlelement(c, c)).extract('//text()')
  3           as clob
  4         ) as concatenated
  5  from t_clobs;

CONCATENATED
-----------------------------------------------------------
ABC

Recreate sequences with new starting numbers

The following script is useful in certain deployment scenarios.

For example, where the sequence names are created by CREATE SEQUENCE statements and the tables and columns, which the sequences are associated with have been imported with data.

A further scenario might be, data was loaded and the existing sequence, for whatever reason, was not used in the load process. The sequence would have to be dropped and recreated with a starting number that is in synch with the data loaded for the application to behave as per prior to the load.

In both of the above scenarios it might be useful to obtain the maximum number either from

1)the table column associated with the sequence
2)known gap between loaded data and last sequence number


set verify off
set feedback off
set head off

declare
  SEQ_NOT_EXIST   exception;
  TBL_NOT_EXIST   exception;
  pragma          exception_init(SEQ_NOT_EXIST, -2289);
  pragma          exception_init(TBL_NOT_EXIST, -942);
  l_Sequence_Name varchar2(30) := 'SQ_T1';
  l_Tbl_Name      varchar2(30) := 'T1';

begin

  begin

    execute immediate 'drop sequence ' || l_Sequence_Name;
    -- ignore if sequence does not exist, but 
    -- raise exception otherwise
    exception
      when SEQ_NOT_EXIST then
        null;
  end;

  begin
    execute immediate 'drop table ' || l_Tbl_Name;
    -- ignore if table does not exist, but 
    -- raise exception otherwise
    exception
      when TBL_NOT_EXIST then
        null;
  end;

end;
/

-- Create a table and sequence to test the script with
create table t1 (a number);
create sequence sq_t1;

set verify on
set feedback on
set head on

-- Insert some data using the sequence
insert into t1 values (sq_t1.nextval);
commit;

select * from t1;

set verify off
set feedback off
set head off

Drop and recreate sequence with starting number one more than the current value of the table/column that is associated with the sequence.

set serveroutput on
declare
  l_Sequence_Name varchar2(30) := 'SQ_T1';
  l_Tbl_Name      varchar2(30) := 'T1';
  l_Col_Name      varchar2(30) := 'A';
  l_Cnt           number;
  SEQ_NOT_EXIST   exception;
  pragma          exception_init(SEQ_NOT_EXIST, -2289);
begin

  execute immediate 'begin
                      select  max(' || l_Col_Name || ')'
                   || ' into :1 from '
                   || l_Tbl_Name || '; end;' using out l_Cnt;

  begin
    execute immediate 'drop sequence ' || l_Sequence_Name;
    exception
      when SEQ_NOT_EXIST then
        null; -- ignore if sequence does not exist, but raise exception in other cases
  end;

  begin

    execute immediate 'create sequence ' || l_Sequence_Name
                                         || ' start with '
                                         || to_char(l_Cnt + 1);

    dbms_output.put_line('Sequence ' || l_Sequence_Name
                                     || ' sucessfully recreated');
  end;
end;
/

set verify on
set feedback on
set head on

insert into t1 values (sq_t1.nextval);
commit;

select * from t1;

An alternative scenario would be to use the LAST_NUMBER column in the USER_SYNONYMS dictionary view and the if the gap(i.e. difference between max in data and LAST_NUMBER) is a known quantity, then simply specify as a parameter.

This might be useful for sequences which are not necessarily associated with a table column.
For example:

undefine GAP
set serveroutput on
declare
  l_Sequence_Name varchar2(30) := 'SQ_T1';
  l_Gap           number := to_number('&&GAP');
  l_Cnt           number;
  SEQ_NOT_EXIST   exception;
  pragma          exception_init(SEQ_NOT_EXIST, -2289);
begin

  begin
    select  last_number+l_Gap
    into    l_Cnt
    from    user_sequences
    where   sequence_name = l_Sequence_name;
  end;

  begin
    execute immediate 'drop sequence ' || l_Sequence_Name;

    -- ignore if sequence does not exist, but 
    -- raise exception in other cases    
    exception
      when SEQ_NOT_EXIST then
        null;
  end;

  execute immediate 'create sequence ' || l_Sequence_Name
                                       || ' start with '
                                       || to_char(l_Cnt);

  dbms_output.put_line('Sequence ' || l_Sequence_Name
                                   || ' sucessfully recreated');
end;
/

Table foreign key script generator

The script below generates a script, which when executed will create a table with data along with foreign keys and indexes on foreign key columns based on a table on a remote server and accessed via a database link.

  
set feedback off
set verify off
set linesize 1000
set pagesize 5000
set heading off

undefine Table_to_Script
undefine DB_Link_to_use

variable tbl_name varchar2(30);
variable db_link_name varchar2(30);
exec :tbl_name := '&Table_to_Script';
exec :db_link_name := '&DB_Link_to_use';

select 'Table selected for scripting : ' || upper(:tbl_name) || ';'
from dual;

select 'DB Link to use : ' || upper(:db_link_name) || ';'
from dual;

SELECT 'DROP TABLE ' || TABLE_NAME || ';'
FROM USER_TABLES
WHERE TABLE_NAME = :tbl_name;

set serveroutput on for wra size unlimited
begin
  dbms_output.put_line('prompt Create table ' || :tbl_name);
  dbms_output.put_line('create table '
                           || :tbl_name
                           || ' as (select * from '
                           || :tbl_name
                           || '@'
                           || :db_link_name
                           || ');');
end;
/

-- Create primary and/or unique keys first off
declare

  l_PK_Str    varchar2(4000);
  l_Tbl_Name  varchar2(30) := :tbl_name;

begin

  -- Assume that indexes will exist for all foreign keys
  for p in (select  uc.constraint_name as pk_constraint_name
                   ,uc.table_name
                   ,uc.constraint_type
                   ,'('||
                    listagg(ucc.column_name, ',')
                    within group (order by ucc.position)
                    || ')' as col_lst
            from
                    user_cons_columns   ucc
                   ,user_constraints    uc
            where
                    uc.table_name       = l_Tbl_Name
            and     uc.table_name       = ucc.table_name
            and     uc.constraint_name  = ucc.constraint_name
            and     uc.constraint_type  in ('P', 'U')
            group by uc.table_name
                    ,uc.constraint_name
                    ,uc.constraint_type
            ) loop

    l_PK_Str := 'alter table ' || l_Tbl_Name 
                               || ' add constraint ' 
                               || p.pk_constraint_name
                               || case p.constraint_type
                                     when 'P' then
                                       ' primary key '
                                     else
                                       ' unique '
                                   end
                               || p.col_lst || ';';

    dbms_output.put_line(null);
    dbms_output.put_line('prompt Add ' 
                            || case p.constraint_type 
                                 when 'P' then 
                                   ' primary ' 
                                 else 
                                   ' unique ' 
                               end 
                            || ' key to ' 
                            || p.table_name);

    dbms_output.put_line(l_PK_Str);

  end loop;

end;
/

declare
  l_FK_Str    varchar2(1000);
  l_Cnt       number;
  l_Tbl_Name  varchar2(30) := :tbl_name;
begin
  for r in (select  u1.constraint_name    as constraint_name
                   ,u1.r_constraint_name  as r_constraint_name
                   ,u1.table_name         as table_name
                   ,u2.table_name         as Ref_Tbl
            from
                    user_constraints      u1
                   ,user_constraints      u2
            where
                    u1.table_name         = l_Tbl_Name
            and     u1.constraint_type    = 'R'
            and     u1.r_constraint_name  = u2.constraint_name 
           )        loop


    l_FK_Str := 'alter table ' || r.table_name
                || ' add constraint '
                || r.constraint_name
                || ' foreign key (';

    -- add table foreign key columns
    for k in (select  column_name
              from    user_cons_columns
              where   constraint_name = r.constraint_name
              order by position) loop

      l_FK_Str := l_FK_Str || k.Column_Name || ',';
    end loop;

    -- strip off trailing commans and replace with ')'
    l_FK_Str :=    substr(l_FK_Str, 1, length(l_FK_Str) - 1)
                || ') references '
                || r.Ref_Tbl
                || '(';

    -- add reference table key columns
    for k in (select  column_name
              from    user_cons_columns
              where   constraint_name = r.r_constraint_name
              order by position) loop

      l_FK_Str := l_FK_Str || k.Column_Name || ',';
    end loop;

    l_FK_Str := substr(l_FK_Str, 1, length(l_FK_Str) - 1) || ');';

    dbms_output.put_line(null);
    dbms_output.put_line('prompt Add foreign key to table ' 
                          || l_Tbl_Name 
                          || ' references ' 
                          || r.Ref_Tbl 
                          || substr(l_FK_Str
                                   ,instr(l_FK_Str,  '(', 1)
                                   ,(instr(l_FK_Str,')',1) -  
                                     instr(l_FK_Str,'(',1)+1)
                                   )
                        );
    dbms_output.put_line(l_FK_Str);
  end loop;

  -- Assume that indexes will exist for all foreign keys
  for i in (select  'idx_'
                    || substr(uc.constraint_name,1,26) as index_name
                   ,uc.table_name
                   ,'('||
                    listagg(ucc.column_name, ',')
                    within group (order by ucc.position)
                    || ')' as col_lst
            from
                    user_cons_columns ucc
                   ,user_constraints  uc
            where
                    uc.table_name      = :tbl_name
            and     uc.table_name      = ucc.table_name
            and     uc.constraint_name = ucc.constraint_name
            and     uc.constraint_type = 'R'
            group by uc.table_name, uc.constraint_name) loop

    l_FK_Str := 'create index ' || i.Index_Name
                                || ' on '
                                || i.table_name 
                                || i.col_lst 
                                || ';';
 
    dbms_output.put_line(null);
    dbms_output.put_line('prompt prompt Add index on ' 
                            || i.table_name 
                            || i.col_lst);

    dbms_output.put_line(l_FK_Str);

  end loop;


end;
/

set feedback on
set verify on
set heading on

Password expiry

When the following error occurs:

ORA-28002: the password will expire within 7 days

Two ALTER styatements are required in order for the user to stop receiving such errors when a connection is attempted:

Assume the two ALTER statement are executed as SYS:

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
ALTER USER USERNAME IDENTIFIED BY PASSWORD;

Implicit Savepoint when deadlock detection occurs.

The following example describes how Oracle
sets an implicit Savepoint after each UPDATE.

For this example, when a deadlock error occurs, ORA-00060, not all
DML statements in the transaction will therefore be rolled back,
but only the single statement within the session in which the deadlock was
detected.

The following illustrates the point;

Create a test table and data first;

create table a (x number);

insert all
  into a (x) values (1)
  into a (x) values (2)
select 1
from dual;

commit;

The data would look as follows;

SQL> select * from a;

         X
----------
         1
         2
        
Now issue a series of statements in two sessions to generate a deadlock error;

Time DML Statement                       Notes
==== =============                       =====          
     Session 1         Session 2
     =========         =========

t0   update a
     set    x = 3
     where  x = 1;
--------------------------------------------------------------------
t1                     update a
                       set    x = 4
                       where  x = 2;
--------------------------------------------------------------------
t2   update a                        At this point Session 1
     set    x = 6                    hangs, because Session 2
     where  x = 2;                   has issued an UPDATE on
                                     the same row at point t1.
--------------------------------------------------------------------
t3                     update a      At this point Session 2
                       set    x = 5  hangs as well.                        where  x = 1; Session 1 is now waiting
                                     for a row that Session 2 
                                     has locked while Session 2 
                                     is waiting for a row that 
                                     Session 1 has locked.
--------------------------------------------------------------------
t4  update                           At this point Oracle will
    a set   x = 6                    will detect a deadly
    where   x = 2;                   embrace (deadlock) due to
                                     a perpetual wait for
    update a set                     shareable resources as
        *                            described at point T3.
    ERROR at line 1:
    ORA-00060:
    deadlock detected
    while waiting for
    resource
--------------------------------------------------------------------
t5  commit;                          The waiting in Session 2
                                     ceases and the UPDATE
                                     shows as being
                                     successfully completed.
--------------------------------------------------------------------
t6  SQL> select *                    At this point the data
      2  from a;                     indicates that 1 was
                                     successfully updated to 3,
             X                       but 2 was not.
             -
             3
             2                                 
            
The reason that one of the updates appeared to have worked, when viewing
the data at point T6, is that Oracle created an implicit Savepoint at point
T2, so when the error was encountered at T5, the entire transaction was not
rolled back, but only the single UPDATE in which the deadlock was detected.

Session 2 would have continued to hang (wait) at point T4, even though a
partial rollback occurred to an implicit savepoint due to the deadlock detection, since the row, updated at T3, was locked at T0.

The UPDATE in session 2 was successful once a COMMIT was issued, at T5,
in session 1 however.

Use this SQL to view the number of deadlocks that have occurred within the database;

SQL> select name, value
  2  from v$sysstat
  3  where name = 'enqueue deadlocks';

NAME                                         VALUE
--------------------------------------- ----------
enqueue deadlocks                                3

To avoid deadlocks, always lock rows in the same order,

For example, for the above set of transactions, had the order
of UPDATES occurred as follows;


t0   update a
     set    x = 3
     where  x = 1;
------------------------------------
t1                     update a
                       set    x = 5
                       where  x = 1;
------------------------------------
t2   update a                      
     set    x = 6                  
     where  x = 2;                 
                                   
------------------------------------
t3                     update a    
                       set    x = 4
                       where  x = 2;

Then a deadlock error would not have occurred.