A simple demonstration about how to comparing 2 Oracle Database Instance(or Schema) objects(such as tables)

I want to show, how to compare objects of 2 oracle instances or schemas. Below, i show indexes and referential constraints. You can write for all other objects. There are some tools or scripts to compare 2 schemas. But they compare by object names. For instance if an index is renamed, you can see that as if the index does not exist. I compare the objects by their structures:

--QUERY_001
--This query compares SOURCE_DBLINK and current database in terms of INDEXes.
--Comparison is done only by INDEX_NAMEs. Renamed-indexes can not be found!!!
--Returns INDEXes which exist in only SOURCE_DBLINK
SELECT index_name, table_name
  FROM all_indexes@SOURCE_DBLINK md
WHERE table_owner = 'DBUSER'
   AND NOT EXISTS (SELECT 1
          FROM all_indexes db
         WHERE md.table_owner = db.table_owner
           AND md.index_name  = db.index_name);
--QUERY_002
--This query compares SOURCE_DBLINK and current database in terms of INDEXes.
--Comparison is done by index structure. Renamed-indexes can be found.This is am enhancement of QUERY_001
--Returns INDEXes which exist in only SOURCE_DBLINK
SELECT *
  FROM all_ind_columns@SOURCE_DBLINK md
WHERE table_owner = 'DBUSER'
   AND index_owner = 'DBUSER'
   AND NOT EXISTS (SELECT 1
          FROM all_ind_columns db
         WHERE md.table_owner     = db.table_owner
           AND md.index_owner     = db.index_owner
           AND md.table_name      = db.table_name
           AND md.column_name     = db.column_name
           AND md.column_position = db.column_position);
--QUERY_003
--This query compares SOURCE_DBLINK and current database in terms of Referantial Constraints.
--Comparison is done only by CONTRAINT_NAMEs. Renamed-ref-constraints can not be found!!!
--Returns REF-CONTRAINTs which exist in only SOURCE_DBLINK        
SELECT constraint_name, table_name,
  FROM all_constraints@SOURCE_DBLINK md
WHERE owner = 'DBUSER'
   AND constraint_type = 'R'
   AND NOT EXISTS
(SELECT 1
          FROM all_constraints db
         WHERE md.owner = db.owner
           AND md.constraint_type = db.constraint_type
           AND md.constraint_name = db.constraint_name);
--QUERY_004
--This query compares SOURCE_DBLINK and current database in terms of Referantial Constraints.
--Comparison is done by constraint structure. Renamed-ref-constraints can be found.This is am enhancement of QUERY_003
--Returns REF-CONTRAINTs which exist in only SOURCE_DBLINK         
SELECT *
  FROM all_cons_columns@SOURCE_DBLINK md
WHERE owner = 'DBUSER'
   AND NOT EXISTS
(SELECT 1
          FROM all_cons_columns db
         WHERE md.owner = db.owner
           AND md.table_name = db.table_name
           AND md.column_name = db.column_name
           AND NVL(md.position, 0) = NVL(db.position, 0));

Oracle Directory Objects and Solution of ORA-29280 and ORA-32001

Today i ws debugging a PL/SQL program. The program was using a log file that is created with utl_file without Oracle's Directory object by PL/SQL on server side. During debugging i got some errors and fixed them. Below i described how to solve them:

SQL> DECLARE
  2    f utl_file.file_type;
  3  BEGIN
  4    f := utl_file.FOPEN('/data01/WORK/TMP', 'drop.txt', 'w');
  5    utl_file.fclose(f);
  6  END;
  7  /
DECLARE
  f utl_file.file_type;
BEGIN
  f := utl_file.FOPEN('/data01/WORK/TMP', 'drop.txt', 'w');
  utl_file.fclose(f);
END;
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 33
ORA-06512: at "SYS.UTL_FILE", line 436
ORA-06512: at line 4
SQL> show parameter utl_file_dir
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string      /data02/TMP
SQL>
SQL> ALTER system SET utl_file_dir = '/data01/WORK7/TMP' scope = spfile;
ALTER system SET utl_file_dir = '/data01/WORK7/TMP' scope = spfile
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup

SQL> create spfile from pfile;                                             
File created.
SQL> ALTER system SET utl_file_dir = '/data01/WORK7/TMP' scope = spfile;
ALTER system SET utl_file_dir = '/data01/WORK7/TMP' scope = spfile
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  293601280 bytes
Fixed Size                  1302824 bytes
Variable Size              94117592 bytes
Database Buffers          197132288 bytes
Redo Buffers                1048576 bytes
Database mounted.
Database opened.
SQL> ALTER system SET utl_file_dir = '/data01/WORK7/TMP' scope = spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  293601280 bytes
Fixed Size                  1302824 bytes
Variable Size              94117592 bytes
Database Buffers          197132288 bytes
Redo Buffers                1048576 bytes
Database mounted.
Database opened.
SQL> show parameter spfile
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /data01/app/oracle/product/10.
                                                 1/dbs/spfiletest.ora
SQL> show parameter utl_file_dir
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string      /data01/WORK7/TMP
SQL>

Oracle has Directory objects that are stored as if they were tables in database. They do not have owners. So they can not possible to query such as select * from user_directories. They can be granted to users on read and write privilleges:

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr

SQL> create directory test_directory as 'C:\TMP';
create directory test_directory as 'C:\TMP'
ORA-01031: insufficient privileges
SQL> connect sys/sys@XE as sysdba
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as SYS
SQL> grant create any directory to hr;
Grant succeeded
SQL> connect hr/hr@XE
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr
SQL> create directory test_directory as 'C:\TMP';
Directory created
SQL> select * from user_directories;
select * from user_directories
ORA-00942: table or view does not exist
SQL> select * from all_directories;
SQL> grant read on directory test_directory to u1;
Grant succeeded

SQL> SELECT t.grantor, t.grantee, t.privilege
  2    FROM all_tab_privs t
  3   WHERE t.table_name = 'TEST_DIRECTORY';
GRANTOR                        GRANTEE                        PRIVILEGE
------------------------------ ------------------------------ ----------------------------------------
HR                             U1                             READ
SYS                            HR                             READ
SYS                            HR                             WRITE
SQL>