How to execute package or procedure using DB link in Oracle

To execute package or procure using DB link in Oracle database first create DB link .

To create DB link please view following link.

How to create DB link

Now you can call package or procedure using db link as follows.

Syntex:
exec schema.porcedure@db_link( {parameteres });
exec schema.package.procedure@db_link ([parameters});
Example:

exec ali_db.calculate_tax@raza_db (10000);
exec ali_db.tax_package.calculate_tax@raza_db (10000);

Query to find parent and child tables and columns in Oracle

select b.owner , b.table_name , b.column_name CHILDCOL,
         C.OWNER , C.TABLE_NAME , C.COLUMN_NAME PARENTCOL,
          b.position,
         a.constraint_name,
         a.delete_rule,
         b.table_name bt,
         b.owner bo
    from all_cons_columns b,
         all_cons_columns c,
         all_constraints a
   where b.constraint_name = a.constraint_name
     and a.owner           = b.owner
     and b.position        = c.position
     and c.constraint_name = a.r_constraint_name
     and c.owner           = a.r_owner
     AND A.CONSTRAINT_TYPE = 'R'
     AND C.OWNER      =UPPER('&OWNER_NAME')
     AND C.TABLE_NAME =UPPER('&PARENT_TABLE_NAME')
     --and b.table_name like case when upper('&CHILD_TABLE_NAME') is null then '%'
       --                         else upper('&CHILD_TABLE_NAME') end
order by 7,6,4,2

How to create DB link in Oracle

When we have multiple databases in our environment and we want to send and receive data to and from these databases we can use DB link for this purpose.

i.e we have database named as ora_db and new_ardb and we want to establish link between these database.

Following steps describe to establish DB link between these databases.

1. First copy sid from new_ardb database server tnsnames.ora file to ora_db database server tnsnames.ora file.

In my case following sample Sid will be copied to ora_db tnsnames.ora.

NEW_ARDB=

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST=NEW_ARDB.ALI.COM) (PORT=1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME=NEW_ARDB.ALI.COM)

    )

  )

now on ora_db database go command prompt and ping the new Sid you have added.

c:\>tnsping new_ardb

if ping response is ok then we are ok to proceed for the next step.

login to ora_db as dba user and execute following command to create DB link.

Syntax:  
CREATE DATABASE LINK [DB link name] CONNECT TO [Username] IDENTIFIED BY [Password] USING ['SERVICE NAME'];

Example:
CREATE DATABASE LINK NEW_ARDB CONNECT TO SYSTEM IDENTIFIED BY SYSTEM  USING 'NEWARDB';

now try to execute following command to check if the DB link is working .

select * from DUAL@NEW_ARDB;

if  above command execute successfully you link is working fine and you can query other tables from the new_ardb database but remember to add DB link while accessing tables from the new_ardb database.

i.e  To access emp table in scott schema on the new_ardb use following command.

select * from scott.emp@new_ardb;

Sql script to generate script to Create Audit table.

Set serveroutput on size unlimited

Declare

         tab varchar2(50);       
         owne varchaR2(50);
          cursor c2 is select owner,table_name,column_name,column_id,data_type,data_length,data_precision,data_scale from dba_tab_columns where table_name='&tab' and owner='&owne' order by column_id;
          
          v_id number;
      Begin
          select max(column_id) into v_id from dba_tab_columns where table_name='&tab';
          for c2_rec in c2 loop
              if c2_rec.column_id=1 and c2_rec.data_type in ('VARCHAR2','CHAR') then
                   dbms_output.put_line('CREATE TABLE AUD_'||c2_rec.owner||'.AUD_'||c2_rec.table_name||'(');
                    dbms_output.put_line('OLD_'||c2_rec.column_name||'               '||c2_rec.data_type||'('||c2_rec.data_length||'),');
                    dbms_output.put_line('NEW_'||c2_rec.column_name||'               '||c2_rec.data_type||'('||c2_rec.data_length||'),');
                    elsif c2_rec.column_id=1 and c2_rec.data_type='NUMBER'and c2_rec.data_precision IS NULL then
                          dbms_output.put_line('CREATE TABLE AUD_'||c2_rec.owner||'.AUD_'||c2_rec.table_name||'(');
                          dbms_output.put_line('OLD_'||c2_rec.column_name||'               '||c2_rec.data_type||',');
                          dbms_output.put_line('NEW_'||c2_rec.column_name||'               '||c2_rec.data_type||',');     
                          elsif c2_rec.column_id=1 and c2_rec.data_type='NUMBER'and c2_rec.data_scale IS NULL then
                                dbms_output.put_line('CREATE TABLE AUD_'||c2_rec.owner||'.AUD_'||c2_rec.table_name||'(');
                                dbms_output.put_line('OLD_'||c2_rec.column_name||'               '||c2_rec.data_type||'('||c2_rec.data_precision||'),');
                                dbms_output.put_line('NEW_'||c2_rec.column_name||'               '||c2_rec.data_type||'('||c2_rec.data_precision||'),');     
                                elsif c2_rec.column_id=1 and c2_rec.data_type='NUMBER'and c2_rec.data_precision is not null then
                                      dbms_output.put_line('CREATE TABLE AUD_'||c2_rec.owner||'.AUD_'||c2_rec.table_name||'(');
                                      dbms_output.put_line('OLD_'||c2_rec.column_name||'               '||c2_rec.data_type||'('||c2_rec.data_precision||','||c2_rec.data_scale||'),');
                                      dbms_output.put_line('NEW_'||c2_rec.column_name||'               '||c2_rec.data_type||'('||c2_rec.data_precision||','||c2_rec.data_scale||'),');     
                                      elsif c2_rec.column_id=1 and c2_rec.data_type='DATE' then
                                            dbms_output.put_line('CREATE TABLE AUD_'||c2_rec.owner||'.AUD_'||c2_rec.table_name||'(');
                                            dbms_output.put_line('OLD_'||c2_rec.column_name||'               '||c2_rec.data_type||',');
                                            dbms_output.put_line('NEW_'||c2_rec.column_name||'               '||c2_rec.data_type||',');                               
                   elsif c2_rec.column_id>1 and c2_rec.column_id<v_id and c2_rec.data_type in ('VARCHAR2','CHAR')then
                          dbms_output.put_line('OLD_'||c2_rec.column_name||'               '||c2_rec.data_type||'('||c2_rec.data_length||'),');
                          dbms_output.put_line('NEW_'||c2_rec.column_name||'               '||c2_rec.data_type||'('||c2_rec.data_length||'),');
                          elsif c2_rec.column_id>1 and c2_rec.column_id<v_id and c2_rec.data_type='NUMBER'and c2_rec.data_precision IS NULL then
                                dbms_output.put_line('OLD_'||c2_rec.column_name||'                '||c2_rec.data_type||',');
                                dbms_output.put_line('NEW_'||c2_rec.column_name||'                '||c2_rec.data_type||',');     
                                elsif c2_rec.column_id>1 and c2_rec.column_id<v_id and c2_rec.data_type='NUMBER'and c2_rec.data_scale IS NULL then
                                      dbms_output.put_line('OLD_'||c2_rec.column_name||'               '||c2_rec.data_type||'('||c2_rec.data_precision||'),');
                                      dbms_output.put_line('NEW_'||c2_rec.column_name||'               '||c2_rec.data_type||'('||c2_rec.data_precision||'),');     
                                      elsif c2_rec.column_id>1 and c2_rec.column_id<v_id and c2_rec.data_type='NUMBER'and c2_rec.data_precision is not null then
                                            dbms_output.put_line('OLD_'||c2_rec.column_name||'                '||c2_rec.data_type||'('||c2_rec.data_precision||','||c2_rec.data_scale||'),');
                                            dbms_output.put_line('NEW_'||c2_rec.column_name||'                '||c2_rec.data_type||'('||c2_rec.data_precision||','||c2_rec.data_scale||'),');     
                                            elsif c2_rec.column_id>1 and c2_rec.column_id<v_id and c2_rec.data_type='DATE' then
                                                  dbms_output.put_line('OLD_'||c2_rec.column_name||'                 '||c2_rec.data_type||',');
                                                  dbms_output.put_line('NEW_'||c2_rec.column_name||'                 '||c2_rec.data_type||',');     
              elsif c2_rec.column_id=v_id and c2_rec.data_type in ('VARCHAR2','CHAR')then
                    dbms_output.put_line('OLD_'||c2_rec.column_name||'                 '||c2_rec.data_type||'('||c2_rec.data_length||'),');
                    dbms_output.put_line('NEW_'||c2_rec.column_name||'                 '||c2_rec.data_type||'('||c2_rec.data_length||'),');
                    elsif c2_rec.column_id=v_id and c2_rec.data_type='NUMBER'AND c2_rec.data_precision IS NULL then
                          dbms_output.put_line('OLD_'||c2_rec.column_name||'                '||c2_rec.data_type||',');
                          dbms_output.put_line('NEW_'||c2_rec.column_name||'                '||c2_rec.data_type||',');     
                          elsif c2_rec.column_id=v_id and c2_rec.data_type='NUMBER'AND c2_rec.data_scale IS NULL then
                                dbms_output.put_line('OLD_'||c2_rec.column_name||'               '||c2_rec.data_type||'('||c2_rec.data_precision||'),');
                                dbms_output.put_line('NEW_'||c2_rec.column_name||'               '||c2_rec.data_type||'('||c2_rec.data_precision||'),');     
                                elsif c2_rec.column_id=v_id and c2_rec.data_type='NUMBER'AND  c2_rec.data_precision is not null then
                                      dbms_output.put_line('OLD_'||c2_rec.column_name||'                 '||c2_rec.data_type||'('||c2_rec.data_precision||','||c2_rec.data_scale||'),');
                                      dbms_output.put_line('NEW_'||c2_rec.column_name||'                 '||c2_rec.data_type||'('||c2_rec.data_precision||','||c2_rec.data_scale||'),');     
                                      elsif c2_rec.column_id=1 and c2_rec.data_type='DATE' then
                                            dbms_output.put_line('OLD_'||c2_rec.column_name||'                 '||c2_rec.data_type||',');
                                            dbms_output.put_line('NEW_'||c2_rec.column_name||'                  '||c2_rec.data_type||',');                     
              end if;
            end loop;            
             dbms_output.put_line('CHANGE_DATE                                    DATE,');
             dbms_output.put_line('CHANGE_BY                                      VARCHAR2(30),');
             dbms_output.put_line('OPERATION                                      CHAR(1),');
             dbms_output.put_line('ADDRESS                                        VARCHAR2(200));');
        end;

Sql Script to get audit trigger Script for any table In Oracle

-- This Script is shared by Ashfaq Ahmed.

This script help you to generate script to create audit trigger for any table.
Audit are used to record history for the modification made to data.

Before creating the trigger use following script to create table to store audit data.

Script to create audit table

Set serveroutput on size unlimited

Declare
  tab varchar2(50);
   owne varchar2(20);
           cursor c2 is select owner,table_name,column_name,column_id,data_type,data_length,data_precision,data_scale from dba_tab_columns where table_name='&tab' and owner='&owne' order by column_id;
           v_id number;
       Begin
           select max(column_id) into v_id from dba_tab_columns where table_name='&tab';
           for c2_rec in c2 loop
               if c2_rec.column_id=1 then
                  dbms_output.put_line('CREATE TRIGGER AUD_'||c2_rec.owner||'.AUD_'||c2_rec.table_name||'');
                  dbms_output.put_line('AFTER INSERT OR DELETE OR UPDATE ON '|| c2_rec.owner||','||c2_rec.table_name);
                  dbms_output.put_line('FOR EACH ROW');
                  dbms_output.put_line('DECLARE');
                  dbms_output.put_line('INFO VARCHAR2(200);');
                  dbms_output.put_line('BEGIN');
                  dbms_output.put_line('SELECT sys_context(''USERENV'',''TERMINAL'')'||'||'||''' - '''||'||'||'sys_context(''USERENV'', ''OS_USER'')'||'||'||''' - '''||'||'||'sys_context(''USERENV'',''IP_ADDRESS'')' ||' INTO'|| ' INFO'|| ' FROM'|| ' dual'||';');
                  DBMS_OUTPUT.PUT_LINE('-----------INSERTING------------');
                  dbms_output.put_line('    IF INSERTING THEN');
                  dbms_output.put_line ('INSERT INTO AUD_'||c2_rec.owner||'.AUD_'||c2_rec.table_name||'');
                  dbms_output.put_line('(OLD_'||c2_rec.column_name||',');
                  dbms_output.put_line('NEW_'||c2_rec.column_name||',');
               elsif c2_rec.column_id>1 and c2_rec.column_id<v_id then
                  dbms_output.put_line('OLD_'||c2_rec.column_name||',');
                  dbms_output.put_line('NEW_'||c2_rec.column_name||',');
               elsif c2_rec.column_id=v_id then
                  dbms_output.put_line('OLD_'||c2_rec.column_name||')');
                  dbms_output.put_line('NEW_'||c2_rec.column_name||')');
                  dbms_output.put_line('CHANGE_DATE,');
                  dbms_output.put_line('CHANGE_BY,');
                  dbms_output.put_line('OPERATION,');
                  dbms_output.put_line('ADDRESS)');
                  dbms_output.put_line('Values');
               end if;
            end loop;
            for c3_rec in c2 loop
               if c3_rec.column_id=1 then
                  dbms_output.put_line('(:OLD.'||c3_rec.column_name||',');
                  dbms_output.put_line(':NEW.'||c3_rec.column_name||',');
               elsif c3_rec.column_id>1 and c3_rec.column_id<v_id then
                  dbms_output.put_line(':OLD.'||c3_rec.column_name||',');
                  dbms_output.put_line(':NEW.'||c3_rec.column_name||',');
               elsif c3_rec.column_id=v_id then
                  dbms_output.put_line(':OLD.'||c3_rec.column_name||',');
                  dbms_output.put_line(':NEW.'||c3_rec.column_name||',');
                  dbms_output.put_line(SYSDATE||',');
                  dbms_output.put_line(USER||',');
                  dbms_output.put_line('I');
                  dbms_output.put_line('INFO);');
               end if;
            end loop;
                     ------------------------------------
            DBMS_OUTPUT.PUT_LINE('-----------UPDATING------------');
            for c2_rec in c2 loop
               if c2_rec.column_id=1 then
                     dbms_output.put_line('    ELSIF UPDATING THEN');
                      dbms_output.put_line ('INSERT INTO AUD_'||c2_rec.owner||'.AUD_'||c2_rec.table_name||'');
                  dbms_output.put_line('(OLD_'||c2_rec.column_name||',');
                  dbms_output.put_line('NEW_'||c2_rec.column_name||',');
               elsif c2_rec.column_id>1 and c2_rec.column_id<v_id then
                  dbms_output.put_line('OLD_'||c2_rec.column_name||',');
                  dbms_output.put_line('NEW_'||c2_rec.column_name||',');
               elsif c2_rec.column_id=v_id then
                  dbms_output.put_line('OLD_'||c2_rec.column_name||')');
                  dbms_output.put_line('NEW_'||c2_rec.column_name||')');
                  dbms_output.put_line('CHANGE_DATE,');
                  dbms_output.put_line('CHANGE_BY,');
                  dbms_output.put_line('OPERATION,');
                  dbms_output.put_line('ADDRESS)');
                  dbms_output.put_line('Values');
               end if;
            end loop;
            for c3_rec in c2 loop
               if c3_rec.column_id=1 then
                  dbms_output.put_line('(:OLD.'||c3_rec.column_name||',');
                  dbms_output.put_line(':NEW.'||c3_rec.column_name||',');
               elsif c3_rec.column_id>1 and c3_rec.column_id<v_id then
                  dbms_output.put_line(':OLD.'||c3_rec.column_name||',');
                  dbms_output.put_line(':NEW.'||c3_rec.column_name||',');
               elsif c3_rec.column_id=v_id then
                  dbms_output.put_line(':OLD.'||c3_rec.column_name||',');
                  dbms_output.put_line(':NEW.'||c3_rec.column_name||',');
                  dbms_output.put_line(SYSDATE||',');
                  dbms_output.put_line(USER||',');
                  dbms_output.put_line('U');
                  dbms_output.put_line('INFO);');
               end if;
            end loop;
          DBMS_OUTPUT.PUT_LINE('-----------DELETING------------');
          for c2_rec in c2 loop
              if c2_rec.column_id=1 then
            DBMS_OUTPUT.PUT_LINE('ELSIF DELETING THEN');
                  dbms_output.put_line ('INSERT INTO AUD_'||c2_rec.owner||'.AUD_'||c2_rec.table_name||'');
                  dbms_output.put_line('(OLD_'||c2_rec.column_name||',');
                  dbms_output.put_line('NEW_'||c2_rec.column_name||',');
               elsif c2_rec.column_id>1 and c2_rec.column_id<v_id then
                  dbms_output.put_line('OLD_'||c2_rec.column_name||',');
                  dbms_output.put_line('NEW_'||c2_rec.column_name||',');
               elsif c2_rec.column_id=v_id then
                  dbms_output.put_line('OLD_'||c2_rec.column_name||')');
                  dbms_output.put_line('NEW_'||c2_rec.column_name||')');
                  dbms_output.put_line('CHANGE_DATE,');
                  dbms_output.put_line('CHANGE_BY,');
                  dbms_output.put_line('OPERATION,');
                  dbms_output.put_line('ADDRESS)');
                  dbms_output.put_line('Values');
               end if;
            end loop;
            for c3_rec in c2 loop
               if c3_rec.column_id=1 then
                  dbms_output.put_line('(:OLD.'||c3_rec.column_name||',');
                  dbms_output.put_line(':NEW.'||c3_rec.column_name||',');
               elsif c3_rec.column_id>1 and c3_rec.column_id<v_id then
                  dbms_output.put_line(':OLD.'||c3_rec.column_name||',');
                  dbms_output.put_line(':NEW.'||c3_rec.column_name||',');
               elsif c3_rec.column_id=v_id then
                  dbms_output.put_line(':OLD.'||c3_rec.column_name||',');
                  dbms_output.put_line(':NEW.'||c3_rec.column_name||',');
                  dbms_output.put_line(SYSDATE||',');
                  dbms_output.put_line(USER||',');
                  dbms_output.put_line('D');
                  dbms_output.put_line('INFO);');
               end if;
            end loop;
       dbms_output.put_line('END IF;');
       dbms_output.put_line('END;');     
   end;

Funtion to verify Date and number data in a table.

Following functions can be used to extract invalid data from table and are usefull in ETL.

First function accepts two parameters one is value and other is date format.
It will return 1 if the data in the column is according to date format provided else it will return 0.
This type of function can be used in following situation.

Data is loaded from excel or text file into a temporary table with all columns of character type and hiredate is loaded with mix of date entries i.e.
10/01/12,10/1/12,13-mar-2012 , 1/2/2012.
In this situation one need to identify and make same pattern of date to load data in actual table.

In following way this function can be used.


select is_date('31-mar-2013 20:10:30','dd-mon-yyyy hh24:mi:ss')
from dual;

select * from temptable
where is_date(hiredate,'dd-mon-yyyy hh24:mi:ss')<>1;

create or replace function is_date (p_value varchar2,p_format varchar2) return number is
v_date date;
begin
v_date:=to_date(p_value,p_format) ;
return 1;
exception when others then
return 0;
end;


This is same function to identify the valid number in column.

create or replace function is_number (p_value varchar2) return number is
v_date number;
begin
v_date:=p_value ;
return 1;
exception when others then
return 0;
end;


select is_number('221a34') from dual;

select * from temptable
where is_number(salary)<>1;

Sql Script to generate script to reorganize tables to remove fragmentation

This script is shared by Ashfaq Ahmed.


-----------------

Following script will generate script to reorganize tables to remove fragmentation of Scott schema and users tablespace.

declare
cursor c1 is select table_name from dba_tables where tablespace_name='USERS' and owner='SCOTT';
a  number;
d  varchar2(30);
b  number;
begin
 dbms_output.enable(60000);
   open c1;
     b:=0;
     select count(*) into a from dba_tables where tablespace_name='USERS';
     while b<a loop
           fetch c1 into d;
           b:=b+1;
           dbms_output.put_line('alter table '||d||' move tablespace users;');
           declare
             cursor c2 is select index_name from dba_indexes where table_name=d;
             a1    number;
        d1 varchar2(30);
             b1 number;
           begin
             open c2;
               b1:=0;
               select count(*) into a1 from dba_indexes where table_name=d;
               while b1<a1 loop
                  fetch c2 into d1;
                  b1:=b1+1;
                  dbms_output.put_line('alter index '||d1||' rebuild online;');
               end loop;
            end;
       end loop;
end;

How to unload data from multiple tables into Multiple files

Here the task is to unload all data from all tables of scott schema in to different files.

Create folder scott at any drive. As in my case I have created scott folder in c: drive.

now create spoolmultiple.sql file and add following script to this file.

-----------------Start-------------------

SET LINESIZE 8000 TRIMSPOOL ON PAGESIZE 0 FEEDBACK OFF
--col well_operator format a100
set long 2000

SET TERMOUT OFF
--SET LINES 108
--SET PAGES 0

select * from emp;

spool c:\scott\emp.txt
/
spool off

select * from dept
.
spool c:\scott\dept.txt
/
spool off

select * from salgrade
.
spool c:\scott\salgrade.txt
/
spool off

---------------end-------------------


Now login to database with scott user.

c:\> sqlplus scott/tiger@ardb

SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 20 20:22:02 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @spoolmultiple_queries.sql
SQL> exit;
c:\>


Now you should have three files in scott folder.

How To Restore Oracle Database Using Cold Backup

Sometimes we are working on test database and database is curropted or we want to refresh the database.
In that case we can use cold backup to restore the database to previous state.

Click here to see How to take cold backup

To restore the database follow the below instructions.

Login to Oracle with Sys as sysdba

[oracle@arzaidi Desktop]$ sqlplus sys/oracle as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 19 20:12:04 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

-- Shutdown database.


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@arzaidi Desktop]$


First delete existing files to clean the database.

remove all datafiles from the ../oradata/database folder in my case it is /oracle/flash_recovery_area/ardb.
Remove all control files. Usually second control file is found in flash_recovery_are/database folder in my case it is /oracle/flash_recovery_area/ardb.


Now copy all files from the backup and put them in the apporipate folders as /oracle/oradata/database in my case it is /oracle/flash_recovery_area/ardb.

Copy second control file in flash_recovery_area/database in my case it is /oracle/flash_recovery_area/ardb.

Go to Shell Prompt and login to Oracle with sys user as sysdba and start database.

[oracle@arzaidi Desktop]$ sqlplus sys/oracle as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 19 20:04:33 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup;
ORACLE instance started.

Total System Global Area 1590267904 bytes
Fixed Size    1336792 bytes
Variable Size 1040189992 bytes
Database Buffers  536870912 bytes
Redo Buffers   11870208 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@arzaidi Desktop]$


your database has been restored to previous point.

How To Recover Data files in oracle database without shutting down the database with Hot/Cold backup.

Connect to oracle database.


[oracle@arzaidi Desktop]$ sqlplus sys/oracle as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 17 09:10:26 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


Create new user.


SQL> create user ali identified by ali;

User created.

Give rights.

SQL> grant resource,connect,dba to ali;

Grant succeeded.

make apps_data tablespace as default tablespace.


SQL> alter user ali default tablespace apps_data;

User altered.

connect to new created user.

SQL> connect ali/ali@ardb
Connected.


SQL> create table sales1 as select * from sh.sales;

Table created.

Go to oradata folder and then databse folder and delete APPS_DATA01.DBF file.

Now try to execute following command this will give error beacuse datafile APPS_DATA01.DBF is deleted which contains sh.sales data.


SQL> create table sales2 as select * from sh.sales;
create table sales2 as select * from sh.sales
                                        *
ERROR at line 1:
ORA-01565: error in identifying file '/oracle/oradata/ardb/apps_data01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

--But the following table is created even if apps_data01.dbf which contains data of ali user does not exists beacue it is using undo files to store new data
for the ali user.


SQL> create table dba_tables as select * from dba_tables;

Table created.

SQL> select count(0) from dba_tables;

  COUNT(0)
----------
      2784

SQL>  exit;

[oracle@arzaidi Desktop]$ sqlplus sys/oracle as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 17 09:32:11 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system checkpoint;

System altered.

SQL> exit;

[oracle@arzaidi Desktop]$ sqlplus ali/ali@ardb

SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 17 09:35:48 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


Do not shutdown database just login as sysdba and offline the table space.

SQL> conn sys/oracle as sysdba
Connected.

Take offline apps_data tablespace.


SQL> alter tablespace apps_data offline immediate;

Tablespace altered.


Now copy apps_data01.dbf from backup to the oradata/ardb folder.

SQL> recover automatic tablespace apps_data;
Media recovery complete.
SQL> alter tablespace apps_data online;

Tablespace altered.

SQL> conn ali/ali@ardb
Connected.
SQL> select count(0) from dba_tables;

How to Recover Data files Using database backup in Oracle

At first step we will curropt the database and then try to recover it from the cold /hot backup.

Suppose your database is running go to oradatafolder and then to your database folder in my case it is /ardb.

Delete some database files as follows.
users01.dbf
system01.dbf
sysaux01.dbf


Now try to execute following command and you will receive error.

SQL> create table scott.sales as select * from sh.sales;
create table scott.sales as select * from sh.sales
                                             *
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/oracle/oradata/ardb/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

shutdown database;


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@arzaidi Desktop]$ sqlplus sys/oracle as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 17 08:56:39 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

Try to start the database and you will again receive error with mean you need recovery.


SQL> startup;  
ORACLE instance started.

Total System Global Area 1590267904 bytes
Fixed Size    1336792 bytes
Variable Size 1040189992 bytes
Database Buffers  536870912 bytes
Redo Buffers   11870208 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oracle/oradata/ardb/system01.dbf'


Shutdown immediate with following command and copy missing files to the database folder from the backup.


SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

Startup database to mount point.


SQL> startup mount;
ORACLE instance started.

Total System Global Area 1590267904 bytes
Fixed Size    1336792 bytes
Variable Size 1040189992 bytes
Database Buffers  536870912 bytes
Redo Buffers   11870208 bytes
Database mounted.

execute following command to recover the database.

SQL> recover automatic database;
 Media recovery complete.

 Now open the database.

 SQL> alter database open;

Database altered.

SQL>                

How to enable database archive mode in oracle

Login sys as sysdba.

sqlplus sys/oracle as sysdba


To check if archiving status

SQL> archive log list;
Database log mode       No Archive Mode
Automatic archival       Disabled
Archive destination       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     23
Current log sequence       25


shutdown database.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

start up database at mount point


SQL> startup mount;
ORACLE instance started.

Total System Global Area 1590267904 bytes
Fixed Size    1336792 bytes
Variable Size 1040189992 bytes
Database Buffers  536870912 bytes
Redo Buffers   11870208 bytes
Database mounted.

To check if archiving status

SQL> archive log list;
Database log mode       No Archive Mode
Automatic archival       Disabled
Archive destination       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     23
Current log sequence       25

To enable archiving .


SQL> alter database archivelog;

Database altered.

To verify archiving


SQL> archive log list;
Database log mode       Archive Mode
Automatic archival       Enabled
Archive destination       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     23
Next log sequence to archive   25
Current log sequence       25
SQL>

Now open the database.

SQL> alter database open;

Database altered.

SQL>

How to take Hot backup of Oracle database.

To take hot backup of oracle database first set the oracle in archive mode.

Note: To enable archive mode please view How to enable database archive mode in oracle

Create Hot backup directory and make oracle user as owner of this directory.

[root@arzaidi /]# mkdir /oracle_backup/hotbackup
[root@arzaidi /]# chown -R oracle.oinstall oracle_backup/
[root@arzaidi /]#


Now login as sysdba to database.

At sql prompt execute following script to get path and copy script for later use of control ,data and redolog file path in windows you can use copy paste.

select 'cp ' ||name|| ' /oracle_backup/hotbackup' from v$datafile


you will get following script as output for later use.

cp /oracle/oradata/ardb/system01.dbf /oracle_backup/hotbackup
cp /oracle/oradata/ardb/undotbs01.dbf /oracle_backup/hotbackup
cp /oracle/oradata/ardb/sysaux01.dbf /oracle_backup/hotbackup
cp /oracle/oradata/ardb/users01.dbf /oracle_backup/hotbackup
cp /oracle/oradata/ardb/example01.dbf /oracle_backup/hotbackup


execute following script to view all tablespaces.


SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE

6 rows selected.


Note: When system tablespace is being backuped oracle uses undo tablespace and when the undo tablespace is begin backuped the it uses system tablespace so
make backup of both tablespaces trun by trun.

First set system table space for backup.

SQL> alter tablespace system begin backup;


Now open another shell and execute copy command as oracle user for system tablespace or from graphical view copy and paste file in hotbackup folder from oradata/dbdatafile folder.


[oracle@arzaidi /]$ cp /oracle/oradata/ardb/system01.dbf /oracle_backup/hotbackup


SQL> alter tablespace system end backup;

Tablespace altered.


SQL> alter tablespace UNDOTBS1 begin backup;

Tablespace altered.

[oracle@arzaidi /]$ cp /oracle/oradata/ardb/undotbs01.dbf /oracle_backup/hotbackup

SQL> alter tablespace UNDOTBS1 end backup;

Tablespace altered.


execute following script to get script to set tablespace in backup mode.

SQL> select 'Alter tablespace '|| tablespace_name ||' begin backup ;' from dba_tablespaces;

-------------------------------------------------------------
--Alter tablespace SYSTEM begin backup ;  -- do not put syatem and undo tablespaces at the same time
--Alter tablespace UNDOTBS1 begin backup ; -- do not put syatem and undo tablespaces at the same time
--------------------------------------------------------------
--Alter tablespace TEMP begin backup ; -- hot backup do not apply to this tablespace
Alter tablespace SYSAUX begin backup ;
Alter tablespace USERS begin backup ;
Alter tablespace EXAMPLE begin backup ;
6 rows selected.



execute following script to get script to unset tablespace from backup mode.

SQL> select 'Alter tablespace '|| tablespace_name ||' end backup ;' from dba_tablespaces;

-------------------------------------------------------------
--Alter tablespace SYSTEM end backup ;  -- do not put syatem and undo tablespaces at the same time
--Alter tablespace UNDOTBS1 end backup ; -- do not put syatem and undo tablespaces at the same time
--------------------------------------------------------------
Alter tablespace SYSAUX end backup ;
Alter tablespace USERS end backup ;
Alter tablespace EXAMPLE end backup ;
6 rows selected.

alter database backup controlfile to '/oracle_backup/hotbackup/controlfilebackup.bkp';

Now the database hot backup is complete.

How to take cold backup of oracle database

Login to linux shell prompt with root user and create directory /oracle_backup/coldbackup for the backup.

mkdir oracle_backup

mkdir /oracle_backup/coldbackup

To change owner to oracle admin user and group to oinstall.

chown -R oracle.oinstall /oracle_backup

To set oracle SID whose backup will be created if there is only one database may not need to execute it.

export ORACLE_SID=ardb

Login to database with sys user


sqlplus sys/oracle as sysdba

At sql prompt execute following script to get path of control ,data and redolog file path.

select 'cp ' ||name|| ' /oracle_backup/coldbackup' from v$datafile
UNION ALL
select 'cp ' ||name|| ' /oracle_backup/coldbackup' from v$controlfile
UNION ALL
select 'cp ' ||name|| ' /oracle_backup/coldbackup' from v$tempfile
UNION ALL
select 'cp ' ||member|| ' /oracle_backup/coldbackup' from v$logfile
order by 1;


shutdown database to take cold backup..

shutdown immediate;

exit;


------------------------
you will get output like below copy this output and paste on shell prompt with oracle user this will copy all oracle files to coldbackup folder.

cp /oracle/oradata/ardb/system01.dbf /oracle_backup/coldbackup
cp /oracle/oradata/ardb/sysaux01.dbf /oracle_backup/coldbackup
cp /oracle/oradata/ardb/undotbs01.dbf /oracle_backup/coldbackup
cp /oracle/oradata/ardb/users01.dbf /oracle_backup/coldbackup
cp /oracle/oradata/ardb/example01.dbf /oracle_backup/coldbackup
cp /oracle/oradata/ardb/control01.ctl /oracle_backup/coldbackup
cp /oracle/flash_recovery_area/ardb/control02.ctl /oracle_backup/coldbackup
cp /oracle/oradata/ardb/temp01.dbf /oracle_backup/coldbackup
cp /oracle/oradata/ardb/redo03.log /oracle_backup/coldbackup
cp /oracle/oradata/ardb/redo02.log /oracle_backup/coldbackup
cp /oracle/oradata/ardb/redo01.log /oracle_backup/coldbackup


Now login again to oracle with sys user.

sqlplus sys/oracle as sysdba

To start the database;

startup;

exit;

your database cold backup is complete.

How to get data in file from Oracle using spool

SET TERMOUT OFF
SET LINES 108
SET PAGES 0


SELECT
ename            ,
empno            ,
SUBSTR(job,1,20),
hiredate      ,
salary
FROM
emp
WHERE ename='ALI'
AND   SALARY>5000
ORDER BY 2
.

SPOOL D:\EMPLOYEE_DATA.TXT
/
SPOOL OFF

Automating Database Startup and Shutdown on Linux

To automatically start the database and listener

with root user

1) Edit the /etc/oratab file as

[root@server1 ~]# gedit /etc/oratab

e.g your database name is orcl then
in this file there will be a line as

orcl:/oracle/product/10.2.0/db_1:N--------only change this 'N' to 'Y'

2) Create dbora file under /etc/init.d/  using following script .

---------------dbora start--------------------



#!/bin/sh

# The script in dbora starts and shuts down the Oracle database automatically.
# Description: This script calls the dbstart script to start oracle and dbshut to stop oracle.

ORA_HOME=/app/oracle/product/11.2/db_home
ORA_OWNER=oracle

if [ ! -f $ORA_HOME/bin/dbstart ];
then
        echo "Sorry!!! The $ORA_HOME/bin/dbstart is missing"
        echo "Cannot start Oracle"
        exit
fi

case "$1" in
'start') # Starts the Oracle database and listeners
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start"
su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME"
su - $ORA_OWNER -c "$ORA_HOME/bin/emctl start dbconsole"
# su - $ORA_OWNER -c "$ORA_HOME/bin/isqlplusctl start"
                ;;
'stop') # Stops the Oracle databases and listeners
 # su - $ORA_OWNER -c "$ORA_HOME/bin/isqlplusctl stop"
su - $ORA_OWNER -c "$ORA_HOME/bin/emctl stop dbconsole"
su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut"
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop"
;;
esac


---------------dbora end--------------------------------

3)[root@server1 ~]# chmod 755 /etc/init.d/dbora

4) run these commands one by one

   [root@server1 ~]# ln -s /etc/init.d/dbora /etc/rc0.d/K10dbora
   [root@server1 ~]# ln -s /etc/init.d/dbora /etc/rc1.d/K10dbora
   [root@server1 ~]# ln -s /etc/init.d/dbora /etc/rc2.d/K10dbora
   [root@server1 ~]# ln -s /etc/init.d/dbora /etc/rc3.d/S99dbora
   [root@server1 ~]# ln -s /etc/init.d/dbora /etc/rc4.d/K10dbora
   [root@server1 ~]# ln -s /etc/init.d/dbora /etc/rc5.d/S99dbora
   [root@server1 ~]# ln -s /etc/init.d/dbora /etc/rc6.d/K10dbora

5) [root@server1 ~]# reboot

Query to Migrate data from one db to other db

Query to generate script to drop table/objects from database.

SELECT 'DROP TABLE '||OWNER||'.'||OBJECT_NAME||' PURGE;'
FROM DBA_OBJECTS WHERE OBJECT_NAME LIKE '%_EXCEL70';


Query to generate script to get table columns count.

select  COUNT(DISTINCT column_name) --DISTINCT column_name--DISTINCT OWNER, TABLE_NAME, COLUMN_NAME
from all_tab_columns
where (COLUMN_NAME  like '%UNIT%') --OR COLUMN_NAME like '%_UOM%' )
AND OWNER IN ('SCOTT')
AND TABLE_NAME NOT LIKE 'BIN$%'
AND
COLUMN_NAME IN (
select  DISTINCT column_name  --DISTINCT OWNER, TABLE_NAME, COLUMN_NAME
from all_tab_columns
where (COLUMN_NAME like '%UNIT%') OR COLUMN_NAME like '%_UOM%' )
AND OWNER IN ('SCOTT')
AND TABLE_NAME NOT LIKE 'BIN$%'
);

select  DISTINCT OWNER, TABLE_NAME, COLUMN_NAME
from all_tab_columns
where (COLUMN_NAME like '%UNIT%' OR COLUMN_NAME like '%UOM%' )
AND OWNER IN ('SCOTT')
AND TABLE_NAME NOT LIKE 'BIN$%';

--------------

Query to generate scripts to get table wise data count in database.


DECLARE
v_num number;
BEGIN
--provider_seq_num
FOR i IN (
select DISTINCT OWNER, TABLE_NAME, COLUMN_NAME
from all_tab_columns
where (COLUMN_NAME like '%UNIT%' OR COLUMN_NAME like '%UOM%' )
AND OWNER IN ('SCOTT')
AND TABLE_NAME NOT LIKE 'BIN$%'
) loop
--EXECUTE IMMEDIATE 'SELECT COUNT(0) FROM '||i.owner||'.'||i.table_name INTO v_num;--||' WHERE '||i.column_name||' = 5999033' INTO v_num;
--if v_num <> 0 then
--dbms_output.put_line (i.owner ||'.'||i.table_name||' = '||v_num);
--dbms_output.put_line ('--'|| i.table_name||' = '||v_num||' '||i.column_name);
dbms_output.put_line('SELECT '''||I.TABLE_NAME||''', P.SHORT_NAME,COUNT(0)'||CHR(10)||
' FROM '|| I.OWNER||'.'||i.table_name||' T ,POWERMED.FACILITY_PROF P ,POWERMED.LOCATION L
 WHERE P.SHORT_NAME IN (''GRANDVIEW'')
 AND P.SEQ_NUM= T.'||i.column_name||'
 AND P.SEQ_NUM=L.FACILITY_SEQ_NUM
 AND L.SHORT_NAME= ''GRANDVIEW''
 GROUP BY P.SHORT_NAME
UNION ALL') ;
--end if;
end loop;
end;

Query to generate scripts to get table wise data count in database.

DECLARE
v_num number;
BEGIN
--provider_seq_num
FOR i IN (select DISTINCT OWNER, TABLE_NAME, COLUMN_NAME
from all_tab_columns
where (COLUMN_NAME like '%FACILITY_SEQ_NUM%') --COLUMN_NAME like '%PROV_SEQ_NUM%' or
AND OWNER IN ('SCOTT', 'SCOTT1', 'POWERMED')
AND TABLE_NAME NOT LIKE 'BIN$%'
AND TABLE_NAME IN (select TABLE_NAME
from all_tab_columns
where (COLUMN_NAME like '%LOCATION%' )
AND OWNER IN ('SCOTT', 'SCOTT1', 'POWERMED')
AND TABLE_NAME NOT LIKE 'BIN$%')
) loop
EXECUTE IMMEDIATE 'SELECT COUNT(0) FROM '||i.owner||'.'||i.table_name||' WHERE '||i.column_name||' = 5999033' INTO v_num;
if v_num <> 0 then
--dbms_output.put_line (i.owner ||'.'||i.table_name||' = '||v_num);
--dbms_output.put_line ('--'|| i.table_name||' = '||v_num||' '||i.column_name);
 dbms_output.put_line('CREATE TABLE '||I.TABLE_NAME||'_EXCEL70 AS
 SELECT  T.*  FROM '||I.OWNER||'.'|| i.table_name||' T ,POWERMED.FACILITY_PROF P ,powermed.location l
  WHERE P.SHORT_NAME IN (''GRANDVIEW'')
 AND P.SEQ_NUM= T.'||i.column_name ||'
  AND P.SEQ_NUM=L.FACILITY_SEQ_NUM
 AND L.SHORT_NAME= ''GRANDVIEW''
 /
 ') ;
end if;
end loop;
end;


Query to generate script to disable constraints on specific tables.

   select  'ALTER TABLE '||a.OWNER||'.'||a.table_name||'  DISABLE   CONSTRAINT '|| a.constraint_name ||' ;'
    from all_constraints A,ALL_cons_columns B
WHERE    A.constraint_name = B.constraint_name
AND A.constraint_type in ('R','U')
   and A.table_name  in  ('CHARGE_PROF',
'INS_OFFICE_VISIT',
'LEDGER_ENTRY',
'PATIENT_APPOINTMENT',
'PATIENT_PROF',
'PROVIDER_SCHEDULE',
'SLOT',
'LOCATION')
   --and A.owner = 'SCOTT'
   and B.column_name='FACILITY_SEQ_NUM'
/


Query to generate update statement script to update data in different tables in database.


DECLARE
v_num number;
BEGIN
--provider_seq_num
FOR i IN (select DISTINCT OWNER, TABLE_NAME, COLUMN_NAME
from all_tab_columns
where (COLUMN_NAME like '%FACILITY_SEQ_NUM%') --COLUMN_NAME like '%PROV_SEQ_NUM%' or
AND OWNER IN ('SCOTT', 'SCOTT1', 'POWERMED')
AND TABLE_NAME NOT LIKE 'BIN$%'
AND TABLE_NAME IN (select TABLE_NAME
from all_tab_columns
where (COLUMN_NAME like '%LOCATION%' )
AND OWNER IN ('SCOTT', 'SCOTT', 'POWERMED')
AND TABLE_NAME NOT LIKE 'BIN$%')
) loop
EXECUTE IMMEDIATE 'SELECT COUNT(0) FROM '||i.owner||'.'||i.table_name||' WHERE '||i.column_name||' = 5999033' INTO v_num;
if v_num <> 0 then
--dbms_output.put_line (i.owner ||'.'||i.table_name||' = '||v_num);
--dbms_output.put_line ('--'|| i.table_name||' = '||v_num||' '||i.column_name);
 dbms_output.put_line('UPDATE  '||i.owner||'.'||I.TABLE_NAME||'  N
SET '||I.COLUMN_NAME||'= (SELECT SEQ_NUM FROM POWERMED.FACILITY_PROF WHERE SHORT_NAME=''INCAREHEAL'' )
 WHERE  '||I.COLUMN_NAME||' in (select F.seq_num from POWERMED.FACILITY_PROF F ,POWERMED.LOCATION L WHERE  F.SHORT_NAME IN (''GRANDVIEW'')  AND F.SEQ_NUM=L.FACILITY_SEQ_NUM AND L.SEQ_NUM=N.LOCATION_SEQ_NUM AND L.FACILITY_SEQ_NUM=N.FACILITY_SEQ_NUM AND L.SHORT_NAME= ''GRANDVIEW'')
 /') ;
end if;
end loop;
end;


Query to generate script to enable constraints on specific tables in database.

select 'ALTER TABLE '||a.OWNER||'.'||a.table_name||'  ENABLE    CONSTRAINT '|| a.constraint_name ||' ;'
    from all_constraints A,ALL_cons_columns B
WHERE    A.constraint_name = B.constraint_name
AND A.constraint_type in ('R','U')
   and A.table_name  in  ('CHARGE_PROF',
'INS_OFFICE_VISIT',
'LEDGER_ENTRY',
'PATIENT_APPOINTMENT',
'PATIENT_PROF',
'PROVIDER_SCHEDULE',
'SLOT',
'LOCATION')
   --and A.owner = 'SCOTT'
   and B.column_name='FACILITY_SEQ_NUM'
/

Queries to generate statements for data migration

Query to generate script to drop table/objects in database.

SELECT 'DROP TABLE '||OWNER||'.'||OBJECT_NAME||' PURGE;'
FROM DBA_OBJECTS WHERE OBJECT_NAME LIKE '%_EXCEL70';


Query to generate script to get count of value in different tables.

DECLARE
v_num number;
BEGIN
--provider_seq_num
FOR i IN (select DISTINCT OWNER, TABLE_NAME, COLUMN_NAME
from all_tab_columns
where (COLUMN_NAME like '%FACILITY_SEQ_NUM%') --COLUMN_NAME like '%PROV_SEQ_NUM%' or
AND OWNER IN ('SEQUEL', 'SEQUEL1', 'POWERMED')
AND TABLE_NAME NOT LIKE 'BIN$%'
AND TABLE_NAME IN (select TABLE_NAME
from all_tab_columns
where (COLUMN_NAME like '%LOCATION%' )
AND OWNER IN ('SEQUEL', 'SEQUEL1', 'POWERMED')
AND TABLE_NAME NOT LIKE 'BIN$%')
) loop
EXECUTE IMMEDIATE 'SELECT COUNT(0) FROM '||i.owner||'.'||i.table_name||' WHERE '||i.column_name||' = 5999033' INTO v_num;
if v_num <> 0 then
--dbms_output.put_line (i.owner ||'.'||i.table_name||' = '||v_num);
--dbms_output.put_line ('--'|| i.table_name||' = '||v_num||' '||i.column_name);
dbms_output.put_line('SELECT '''||I.TABLE_NAME||''', P.SHORT_NAME,COUNT(0)'||CHR(10)||
' FROM '|| I.OWNER||'.'||i.table_name||' T ,POWERMED.FACILITY_PROF P ,POWERMED.LOCATION L
 WHERE P.SHORT_NAME IN (''GRANDVIEW'')
 AND P.SEQ_NUM= T.'||i.column_name||'
 AND P.SEQ_NUM=L.FACILITY_SEQ_NUM
 AND L.SHORT_NAME= ''GRANDVIEW''
 GROUP BY P.SHORT_NAME
UNION ALL') ;
end if;
end loop;
end;

Query to generate table backup statement script with required criteria.


DECLARE
v_num number;
BEGIN
--provider_seq_num
FOR i IN (select DISTINCT OWNER, TABLE_NAME, COLUMN_NAME
from all_tab_columns
where (COLUMN_NAME like '%FACILITY_SEQ_NUM%') --COLUMN_NAME like '%PROV_SEQ_NUM%' or
AND OWNER IN ('SEQUEL', 'SEQUEL1', 'POWERMED')
AND TABLE_NAME NOT LIKE 'BIN$%'
AND TABLE_NAME IN (select TABLE_NAME
from all_tab_columns
where (COLUMN_NAME like '%LOCATION%' )
AND OWNER IN ('SEQUEL', 'SEQUEL1', 'POWERMED')
AND TABLE_NAME NOT LIKE 'BIN$%')
) loop
EXECUTE IMMEDIATE 'SELECT COUNT(0) FROM '||i.owner||'.'||i.table_name||' WHERE '||i.column_name||' = 5999033' INTO v_num;
if v_num <> 0 then
--dbms_output.put_line (i.owner ||'.'||i.table_name||' = '||v_num);
--dbms_output.put_line ('--'|| i.table_name||' = '||v_num||' '||i.column_name);
 dbms_output.put_line('CREATE TABLE '||I.TABLE_NAME||'_EXCEL70 AS
 SELECT  T.*  FROM '||I.OWNER||'.'|| i.table_name||' T ,POWERMED.FACILITY_PROF P ,powermed.location l
  WHERE P.SHORT_NAME IN (''GRANDVIEW'')
 AND P.SEQ_NUM= T.'||i.column_name ||'
  AND P.SEQ_NUM=L.FACILITY_SEQ_NUM
 AND L.SHORT_NAME= ''GRANDVIEW''
 /
 ') ;
end if;
end loop;
end;


Query to generate script to disable constraints on tables in database.

   select  'ALTER TABLE '||a.OWNER||'.'||a.table_name||'  DISABLE   CONSTRAINT '|| a.constraint_name ||' ;'
    from all_constraints A,ALL_cons_columns B
WHERE    A.constraint_name = B.constraint_name
AND A.constraint_type in ('R','U')
   and A.table_name  in  ('CHARGE_PROF',
'INS_OFFICE_VISIT',
'LEDGER_ENTRY',
'PATIENT_APPOINTMENT',
'PATIENT_PROF',
'PROVIDER_SCHEDULE',
'SLOT',
'LOCATION')
   --and A.owner = 'SEQUEL'
   and B.column_name='FACILITY_SEQ_NUM'
/

Query to generate script to enable constraints on tables in database.

select 'ALTER TABLE '||a.OWNER||'.'||a.table_name||'  ENABLE    CONSTRAINT '|| a.constraint_name ||' ;'
    from all_constraints A,ALL_cons_columns B
WHERE    A.constraint_name = B.constraint_name
AND A.constraint_type in ('R','U')
   and A.table_name  in  ('CHARGE_PROF',
'INS_OFFICE_VISIT',
'LEDGER_ENTRY',
'PATIENT_APPOINTMENT',
'PATIENT_PROF',
'PROVIDER_SCHEDULE',
'SLOT',
'LOCATION')
   --and A.owner = 'SEQUEL'
   and B.column_name='FACILITY_SEQ_NUM'
/

Query to generate update statement script to update data in tables.


DECLARE
v_num number;
BEGIN
--provider_seq_num
FOR i IN (select DISTINCT OWNER, TABLE_NAME, COLUMN_NAME
from all_tab_columns
where (COLUMN_NAME like '%FACILITY_SEQ_NUM%') --COLUMN_NAME like '%PROV_SEQ_NUM%' or
AND OWNER IN ('SEQUEL', 'SEQUEL1', 'POWERMED')
AND TABLE_NAME NOT LIKE 'BIN$%'
AND TABLE_NAME IN (select TABLE_NAME
from all_tab_columns
where (COLUMN_NAME like '%LOCATION%' )
AND OWNER IN ('SEQUEL', 'SEQUEL1', 'POWERMED')
AND TABLE_NAME NOT LIKE 'BIN$%')
) loop
EXECUTE IMMEDIATE 'SELECT COUNT(0) FROM '||i.owner||'.'||i.table_name||' WHERE '||i.column_name||' = 5999033' INTO v_num;
if v_num <> 0 then
--dbms_output.put_line (i.owner ||'.'||i.table_name||' = '||v_num);
--dbms_output.put_line ('--'|| i.table_name||' = '||v_num||' '||i.column_name);
 dbms_output.put_line('UPDATE  '||i.owner||'.'||I.TABLE_NAME||'  N
SET '||I.COLUMN_NAME||'= (SELECT SEQ_NUM FROM POWERMED.FACILITY_PROF WHERE SHORT_NAME=''INCAREHEAL'' )
 WHERE  '||I.COLUMN_NAME||' in (select F.seq_num from POWERMED.FACILITY_PROF F ,POWERMED.LOCATION L WHERE  F.SHORT_NAME IN (''GRANDVIEW'')  AND F.SEQ_NUM=L.FACILITY_SEQ_NUM AND L.SEQ_NUM=N.LOCATION_SEQ_NUM AND L.FACILITY_SEQ_NUM=N.FACILITY_SEQ_NUM AND L.SHORT_NAME= ''GRANDVIEW'')
 /') ;
end if;
end loop;
end;
/

Queries/Query to get database statistics

select  METRIC_NAME,
        VALUE
from    SYS.V_$SYSMETRIC
where   METRIC_NAME IN ('Database CPU Time Ratio',
                        'Database Wait Time Ratio') AND
        INTSIZE_CSEC =
        (select max(INTSIZE_CSEC) from SYS.V_$SYSMETRIC);
     
select  end_time,
        value
from    sys.v_$sysmetric_history
where   METRIC_NAME = 'Database CPU Time Ratio'
order by 1 desc;      

select  CASE METRIC_NAME
            WHEN 'SQL Service Response Time' then 'SQL Service Response Time (secs)'
            WHEN 'Response Time Per Txn' then 'Response Time Per Txn (secs)'
            ELSE METRIC_NAME
            END METRIC_NAME,
                CASE METRIC_NAME
            WHEN 'SQL Service Response Time' then ROUND((MINVAL / 100),2)
            WHEN 'Response Time Per Txn' then ROUND((MINVAL / 100),2)
            ELSE MINVAL
            END MININUM,
                CASE METRIC_NAME
            WHEN 'SQL Service Response Time' then ROUND((MAXVAL / 100),2)
            WHEN 'Response Time Per Txn' then ROUND((MAXVAL / 100),2)
            ELSE MAXVAL
            END MAXIMUM,
                CASE METRIC_NAME
            WHEN 'SQL Service Response Time' then ROUND((AVERAGE / 100),2)
            WHEN 'Response Time Per Txn' then ROUND((AVERAGE / 100),2)
            ELSE AVERAGE
            END AVERAGE
from    SYS.V_$SYSMETRIC_SUMMARY
where   METRIC_NAME in ('CPU Usage Per Sec',
                      'CPU Usage Per Txn',
                      'Database CPU Time Ratio',
                      'Database Wait Time Ratio',
                      'Executions Per Sec',
                      'Executions Per Txn',
                      'Response Time Per Txn',
                      'SQL Service Response Time',
                      'User Transaction Per Sec');
                   
--------------

select  case db_stat_name
            when 'parse time elapsed' then
                'soft parse time'
            else db_stat_name
            end db_stat_name,
        case db_stat_name
            when 'sql execute elapsed time' then
                time_secs - plsql_time
            when 'parse time elapsed' then
                time_secs - hard_parse_time
            else time_secs
            end time_secs,
        case db_stat_name
            when 'sql execute elapsed time' then
                round(100 * (time_secs - plsql_time) / db_time,2)
            when 'parse time elapsed' then
                round(100 * (time_secs - hard_parse_time) / db_time,2)
            else round(100 * time_secs / db_time,2)
            end pct_time
from
(select stat_name db_stat_name,
        round((value / 1000000),3) time_secs
    from sys.v_$sys_time_model
    where stat_name not in('DB time','background elapsed time',
                            'background cpu time','DB CPU')),
(select round((value / 1000000),3) db_time
    from sys.v_$sys_time_model
    where stat_name = 'DB time'),
(select round((value / 1000000),3) plsql_time
    from sys.v_$sys_time_model
    where stat_name = 'PL/SQL execution elapsed time'),
(select round((value / 1000000),3) hard_parse_time
    from sys.v_$sys_time_model
    where stat_name = 'hard parse elapsed time')
order by 2 desc;


-------------

select  WAIT_CLASS,
        TOTAL_WAITS,
        round(100 * (TOTAL_WAITS / SUM_WAITS),2) PCT_WAITS,
        ROUND((TIME_WAITED / 100),2) TIME_WAITED_SECS,
        round(100 * (TIME_WAITED / SUM_TIME),2) PCT_TIME
from
(select WAIT_CLASS,
        TOTAL_WAITS,
        TIME_WAITED
from    V$SYSTEM_WAIT_CLASS
where   WAIT_CLASS != 'Idle'),
(select  sum(TOTAL_WAITS) SUM_WAITS,
        sum(TIME_WAITED) SUM_TIME
from    V$SYSTEM_WAIT_CLASS
where   WAIT_CLASS != 'Idle')
order by 5 desc;

---------
select  sess_id,
        username,
        program,
        wait_event,
        sess_time,
        round(100 * (sess_time / total_time),2) pct_time_waited
from
(select a.session_id sess_id,
        decode(session_type,'background',session_type,c.username) username,
        a.program program,
        b.name wait_event,
        sum(a.time_waited) sess_time
from    sys.v_$active_session_history a,
        sys.v_$event_name b,
        sys.dba_users c
where   a.event# = b.event# and
        a.USER_ID = C.USER_ID and
        --sample_time > '21-NOV-04 12:00:00 AM' and
        --sample_time < '21-NOV-04 05:00:00 AM' and
        b.wait_class = 'User I/O'
group by a.session_id,
        decode(session_type,'background',session_type,c.username),
        a.program,
        b.name),
(select sum(a.time_waited) total_time
from    sys.v_$active_session_history a,
        sys.v_$event_name b
where   a.event# = b.event# and
        sample_time > '21-NOV-04 12:00:00 AM' and
        sample_time < '21-NOV-04 05:00:00 AM' and
        b.wait_class = 'User I/O')
order by 6 desc;

Use external table to load data in Oracle from text / .csv or excel files

External tables are used to load data into database from text,csv or xml files.


First we need to create a directory object on Server where we will place our files that we want to load.

--Connect to your database as sys user.
sqlplus  / as sysdba

--Query to find existing Directory objects  
select * from all_objects where object_type ='DIRECTORY';

--Query to find directory object path to operating system directory .
SELECT OWNER, DIRECTORY_NAME, DIRECTORY_PATH
  FROM ALL_DIRECTORIES;

--IF DIRECTORY DOES NOT EXITS USE FOLLOWING COMMAND TO CREATE DIRECTORY object  .
--Directory object should point to local path on the database machine.

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS 'c:\external';

--Grant Read and write privilige to user who will load data .
GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO ali;

-- connect the user that will load data.
conn ali/ali

--- Use following command to create external table.

CREATE TABLE EXT_TABLE (
DATESTIME    VARCHAR2(500),
LATITUDE   varchar2(500),
FIELD1   varchar2(500),
FIELD2   varchar2(500),
FIELD3   varchar2(500),
FIELD4   varchar2(500),
FIELD5   varchar2(500),
FIELD6   varchar2(500),
FIELD7   varchar2(500),
FIELD8   varchar2(500),
FIELD9   varchar2(500),
FIELD10   varchar2(500),
FIELD11   varchar2(500),
FIELD12   varchar2(500),
FIELD13   varchar2(500),
FIELD14   varchar2(500),
FIELD15   varchar2(500),
FIELD16   varchar2(500),
FIELD17   varchar2(500),
FIELD18   varchar2(500),
FIELD19   varchar2(500),
FIELD20   varchar2(500),
FIELD21   varchar2(500),
FIELD22   varchar2(500),
FIELD23   varchar2(500),
FIELD24   varchar2(500),
FIELD25   varchar2(500),
FIELD26   varchar2(500),
FIELD27   varchar2(500),
FIELD28   varchar2(500),
FIELD29   varchar2(500),
FIELD30   varchar2(500)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY DATA_PUMP_DIR
  ACCESS PARAMETERS (
  RECORDS DELIMITED BY NEWLINE
  READSIZE 5004288 
  BADFILE DATA_PUMP_DIR:'bad_%a_%p.bad'
  LOGFILE DATA_PUMP_DIR:'log_%a_%p.log'
  FIELDS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '"'
  MISSING FIELD VALUES ARE NULL
  REJECT ROWS WITH ALL NULL FIELDS
    (DATESTIME,    LATITUDE,    FIELD1,    FIELD2,    FIELD3,    FIELD4,    FIELD5,    FIELD6,    FIELD7,    FIELD8,    FIELD9,    FIELD10,    FIELD11,    FIELD12,    FIELD13,    FIELD14,    FIELD15,    FIELD16,    FIELD17,    FIELD18,    FIELD19,    FIELD20,    FIELD21,    FIELD22,    FIELD23,    FIELD24,    FIELD25,    FIELD26,    FIELD27,    FIELD28,    FIELD29,    FIELD30
    ))
    LOCATION (DATA_PUMP_DIR:'demo1.dat'))
PARALLEL
REJECT LIMIT UNLIMITED;


--- Use following command to view structure of external table.
desc ali.ext_table

-- Create file demo1.dat with following data and place is on database server in the directory path where directory object is pointing.

1111,MORGAN,DIRECTOR,10
2222,HARDIE,MANAGER,30
3333,HAVEMEYER,CTO,10
4444,LOFSTROM,DEVELOPER,10
5555,TOWNSEND,MANAGER,30


-- use following command to load data from demo1.dat file.

ALTER TABLE ALI.EXT_TABLE LOCATION('demo1.dat');

--- Use following command to view data loaded in the external table.

SELECT * FROM ali.EXT_TABLE;

-- Create file demo1.dat with following data and place is on database server in the directory path where directory object is pointing.

1111,MORGAN,DIRECTOR,10
2222,HARDIE,MANAGER,30
3333,HAVEMEYER,CTO,10
4444,LOFSTROM,DEVELOPER,10
5555,TOWNSEND,MANAGER,30
1111,MORGAN,DIRECTOR,10
2222,HARDIE,MANAGER,30
3333,HAVEMEYER,CTO,10
4444,LOFSTROM,DEVELOPER,10
5555,TOWNSEND,MANAGER,30
1111,MORGAN,DIRECTOR,10
2222,HARDIE,MANAGER,30
3333,HAVEMEYER,CTO,10
4444,LOFSTROM,DEVELOPER,10
5555,TOWNSEND,MANAGER,30


-- use following command to load data from demo2.dat file.

ALTER TABLE ALI.EXT_TABLE LOCATION('demo2.dat');

--- Use following command to view data loaded in the external table.

SELECT * FROM ALI.EXT_TABLE;

FILENAME.TXT IS FILEPATH, ANY TXT FILE, XML FILE , CSV FILE

How to read Write BLOB.

SELECT * FROM DBA_DATA_FILES;

drop TABLESPACE LOB_TBS1 INCLUDING CONTENTS ;

CREATE TABLESPACE LOB_TBS1
DATAFILE '/d01/oracle/oradata/lob_tbs1.dbf' SIZE 800M REUSE
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 64M
SEGMENT SPACE MANAGEMENT AUTO;

--------------------

CREATE TABLE customer_profiles (
id NUMBER,
full_name VARCHAR2(45),
resume CLOB DEFAULT EMPTY_CLOB(),
picture BLOB DEFAULT EMPTY_BLOB())
LOB(PICTURE) STORE AS BASICFILE
(TABLESPACE lob_tbs1);


-----------------

INSERT INTO customer_profiles
(ID, FULL_NAME, RESUME, PICTURE)
VALUES (164, 'Charlotte Kazan', EMPTY_CLOB(), NULL);
----------

UPDATE customer_profiles
SET resume = 'Date of Birth: 8 February 1951',
PICTURE = EMPTY_BLOB()
WHERE ID = 164;
-------------

UPDATE customer_profiles
SET RESUME = 'Date of Birth: 1 June 1956'
WHERE id = 150;

------------

CREATE OR REPLACE PROCEDURE loadLOBFromBFILE_proc(p_dest_loc IN OUT BLOB, p_file_name IN VARCHAR2,p_file_dir IN VARCHAR2) IS
v_src_loc BFILE := BFILENAME(p_file_dir, p_file_name);
v_amount INTEGER := 4000;
BEGIN
DBMS_LOB.OPEN(v_src_loc, DBMS_LOB.LOB_READONLY);
V_AMOUNT := DBMS_LOB.GETLENGTH(V_SRC_LOC);
DBMS_LOB.LOADFROMFILE (p_dest_loc, v_src_loc, v_amount);
DBMS_LOB.CLOSE(V_SRC_LOC);
END loadLOBFromBFILE_proc;


------
exec loadLOBFromBFILE_proc ('a','1',

----------------


CREATE OR REPLACE PROCEDURE write_lob(P_REC_ID NUMBER,p_file_name IN VARCHAR2, p_file_dir IN VARCHAR2)
IS
v_fn VARCHAR2(15);
V_LN VARCHAR2(40);
V_B BLOB;
v_src_loc BFILE := BFILENAME(p_file_dir, p_file_name);
BEGIN
DBMS_OUTPUT.ENABLE;
IF DBMS_LOB.FILEEXISTS(V_SRC_LOC) = 1 THEN
DBMS_OUTPUT.PUT_LINE('Begin Udating rows...');
UPDATE CUSTOMER_PROFILES
SET PICTURE=EMPTY_BLOB()
WHERE ID=P_REC_ID
RETURNING PICTURE INTO V_B;
LOADLOBFROMBFILE_PROC (V_B,P_FILE_NAME, P_FILE_DIR);
--COMMIT;
ELSE
DBMS_OUTPUT.PUT_LINE('File does not exists.');
END IF;
END write_lob;



-----------------


exec write_lob( 164,'g2g.jpg','LOB_READ');

select * from customer_profiles;

---delete  FROM customer_profiles


UPDATE CUSTOMER_PROFILES
SET PICTURE=EMPTY_BLOB()
WHERE ID=164;

--------





create or replace procedure read_lob
IS
V_LOB_LOC BLOB;

CURSOR profiles_cur IS
SELECT id, full_name, resume, picture
FROM customer_profiles;
v_profiles_rec customer_profiles%ROWTYPE;
BEGIN
OPEN profiles_cur;
LOOP
FETCH profiles_cur INTO v_profiles_rec;
v_lob_loc := v_profiles_rec.picture;
DBMS_OUTPUT.PUT_LINE('The length is: '||
DBMS_LOB.GETLENGTH(v_lob_loc));
DBMS_OUTPUT.PUT_LINE('The ID is: '|| v_profiles_rec.id);
DBMS_OUTPUT.PUT_LINE('The blob is read: '||
UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(v_lob_loc,200,1)));
EXIT WHEN profiles_cur%NOTFOUND;
END LOOP;
CLOSE profiles_cur;
END;

Working with trace files.

1. Add trigger to DB for the user you want to debug.

create or replace
TRIGGER scott.sqltrace
AFTER LOGON ON DATABASE
BEGIN
    --
    -- Only process specific user(s)
    --
    IF (user IN ('ALI')) THEN

       EXECUTE IMMEDIATE 'ALTER SESSION SET tracefile_identifier=''Ali''';

       EXECUTE IMMEDIATE 'ALTER SESSION SET max_dump_file_size = UNLIMITED';
        EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 12''';
    END IF;
END;
---

2. Grant Alter session to schema on wich trigger is added here scott

GRANT ALTER SESSION TO SCOTT;

3. Login to application and perform action to debug.

 i.e View records,Perform Deletion or updation.

4. Pick trace files from the trace folder and copy to any other folder . 
    (Get trace location from the following query)

Note:  Remember to disable trigger scott.sqltrace when you have finished tracing.

 Alter trigger scott.sqltrace disable;

Query to get trace file path

SELECT s.sid,
       s.serial#,
       pa.value || '/' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||
       '_ora_' || p.spid || '.trc' AS trace_file
FROM   v$session s,
       v$process p,
       v$parameter pa
WHERE  pa.name = 'user_dump_dest'
AND    S.PADDR = P.ADDR
AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

5. Go to  cmd /dos prompt go to trace file folder  and use tkprof or tvdxstat  to generate readable format file.

                    Usually file with big size contains your queries.

     tkprof scott_ora_10728.trc SCOTT_ora_10728.tk
or


    tvdxtat -i scott_ora_10728.trc -o scott_ora_10728.html

Note: tvdxtat is batter utility to analyze trace file because it provide detail information such as bind variable values.

tvdxtat can be downloaded from the following link for free.

http://www.antognini.ch/top/downloadable-files

now open SCOTT_ora_10728.tk and find readable output.

How to view rights and get source of objects

Query to view who has privilige on the table.

SELECT *
FROM sys.all_tab_privs
WHERE table_name ='EMP';


Query to view role priviliges.

select * from ROLE_SYS_PRIVS
where role='ADMIN_SROLE';


select * from dba_ROLE_PRIVS
where granted_role='ADMIN_SROLE';

SELECT * FROM role_role_privs
WHERE ROLE='ADMIN_SROLE';

---GET  PACKAGE TEXT

SELECT TEXT
FROM ALL_SOURCE
WHERE name ='PACK_USER_MGMT'
AND TYPE='PACKAGE BODY';


select owner, name, type
from dba_dependencies
where referenced_name = 'PACK_USER_MGMT' and referenced_type = 'SYNONYM';

SELECT synonym_name, table_owner, table_name FROM all_synonyms
where table_name='PACK_USER_MGMT';


---------------------------------------------------------
--2---       ---- TO VIEW  REFERENCE TABLES AND COLUMS
----------------------------------------------------------

SELECT   uc.constraint_name constraint_source,ucc1.TABLE_NAME||'.'||ucc1.column_name constraint_source_TABLE_COL
,       'REFERENCES'||CHR(10)
||      '('||UCC2.TABLE_NAME||'.'||UCC2.COLUMN_NAME||')' REFERENCES_COLUMN
FROM     dba_constraints uc
,        DBA_CONS_COLUMNS UCC1
,        dba_cons_columns ucc2
WHERE    uc.constraint_name = ucc1.constraint_name
AND      UC.R_CONSTRAINT_NAME = UCC2.CONSTRAINT_NAME
--AND      uc.constraint_type = 'R'
AND      uc.constraint_name = UPPER('FK_RESERVOIR_SK_3');

------------------------------------------
--2---       ---- TO VIEW  REFERENCE TABLES AND COLUMS
-----------------------------------------

  select b.owner child_own, b.table_name child_tab, b.column_name CHILDCOL,
         B.POSITION,
         c.owner parent_own,c.table_name parent_tab, c.column_name PARENTCOL,
         a.constraint_name,
         a.delete_rule,
         b.table_name bt,
         b.owner bo
    from all_cons_columns b,
         all_cons_columns c,
         all_constraints a
   where b.constraint_name = a.constraint_name
     and a.owner           = b.owner
     and b.position        = c.position
     and c.constraint_name = a.r_constraint_name
     and c.owner           = a.r_owner
     AND A.CONSTRAINT_TYPE = 'R'
     --and c.owner      like case when upper('&OWNER_NAME') is null then '%'                            else upper('&OWNER_NAME') end
   --and c.table_name like case when upper('CURVE_MNEMONIC_GROUP_MEMBER') is null then '%'                                else upper('CURVE_MNEMONIC_GROUP_MEMBER') end--parent table
     AND B.TABLE_NAME LIKE CASE WHEN UPPER('CURVE_MNEMONIC_GROUP_MEMBER') IS NULL THEN '%'                                ELSE UPPER('CURVE_MNEMONIC_GROUP_MEMBER') END--child table
ORDER BY 7,6,4,2;