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;