Create database link in another schema

Here you can find couple of ways to create database link in another schema: using dbms_ijob and dbms_sys_sql

DECLARE
  l_link_owner VARCHAR2(30) := 'TEST_USER';
  l_query VARCHAR2(32767) := q'[
begin
    execute immediate 'CREATE DATABASE LINK test_link4
                          connect to    "test_user"
                          identified by "test_user"
                          using         ''localhost:1521/orcl''';
END;  
  ]';
BEGIN
  sys.dbms_isched.create_job(job_name            => 'CREATE_DBLINK',
                             job_style           => 'REGULAR',
                             program_type        => 'PLSQL_BLOCK',
                             program_action      => l_query,
                             number_of_arguments => 0,
                             schedule_type       => 'NOW', -- ONCE, NOW, EVENT
                             schedule_expr       => NULL,
                             queue_spec          => NULL,
                             start_date          => NULL,
                             end_date            => NULL,
                             job_class           => 'DEFAULT_JOB_CLASS', -- SELECT * FROM dba_scheduler_job_classes
                             comments            => NULL,
                             enabled             => TRUE,
                             auto_drop           => TRUE,
                             invoker             => l_link_owner,
                             sys_privs           => 1, -- sys.dbms_scheduler.check_sys_privs()
                             aq_job              => FALSE);
  COMMIT;
  dbms_isched.run_job('CREATE_DBLINK',
                      asynchronous   => TRUE,
                      invoker        => l_link_owner,
                      sys_privs      => 1/*sys.dbms_scheduler.check_sys_privs()*/);
END;
/

Foreign keys and nulls

Be careful with null enabled foreign key, especially multicolumn.
Test case below:

CREATE TABLE par(id1 NUMBER NOT NULL, id2 NUMBER NOT NULL, CONSTRAINT par_pk PRIMARY KEY(id1, id2));
Table created
INSERT INTO par VALUES(1,1);
1 row inserted
CREATE TABLE chi(id1 NUMBER, id2 NUMBER, CONSTRAINT par_fk FOREIGN KEY (id1, id2) REFERENCES par(id1, id2));
Table created
INSERT INTO chi VALUES(NULL, NULL);-- double nulls are allowed
1 row inserted
INSERT INTO chi VALUES(1, NULL);
1 row inserted
INSERT INTO chi VALUES(2, NULL);--!!!we have no 2 in par table!!!
1 row inserted
INSERT INTO chi VALUES(2, 1);
INSERT INTO chi VALUES(2, 1)
ORA-02291: integrity constraint (DOCKER.PAR_FK) violated - parent key not found

If one of the fields is null, the second field doesn’t need to present in parent table.

dblink, которого нет

SELECT * FROM GLOBAL_NAME;
GLOBAL_NAME
--------------------------
ORCL
SELECT COUNT(*) cnt FROM dba_db_links l WHERE l.db_link = 'ORCL';
       CNT
----------
         0
SELECT * FROM dual@ORCL;
DUMMY
-----
X

Liquibase test: sorting in includeAll

Let we have the following folder structure

folder_01
  subfolder_03
    file_1.sql
    file_2.sql
  subfolder_04
    file_1.sql
    file_2.sql
folder_02
  subfolder_01
    file_1.sql
    file_2.sql
  subfolder_02
    file_1.sql
    file_2.sql
file_01.sql
file_02.sql
zfile.sql

and I have changelog with includeAll:

<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog/1.9"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog/1.9
         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.9.xsd">
     <includeAll path="/multifolder/"/>
</databaseChangeLog>

the order of inclusion will be following

-- *********************************************************************
-- Update Database Script
-- *********************************************************************
-- Change Log: multifolder.xml
-- Ran at: 19.09.16 15:40
-- Against: null@offline:oracle
-- Liquibase version: 3.5.1
-- *********************************************************************

SET DEFINE OFF;

-- Changeset multifolder/file_01.sql::1::includeall
select 'file01' f from dual;

-- Changeset multifolder/file_02.sql::1::includeall
select 'file02' f from dual;

-- Changeset multifolder/folder_01/subfolder_03/1.sql::1::includeall
select 'folder_01subfolder_03_file1' f from dual;

-- Changeset multifolder/folder_01/subfolder_03/2.sql::1::includeall
select 'folder_01subfolder_03_file2' f from dual;

-- Changeset multifolder/folder_01/subfolder_04/1.sql::1::includeall
select 'folder_01subfolder_04_file1' f from dual;

-- Changeset multifolder/folder_01/subfolder_04/2.sql::1::includeall
select 'folder_01subfolder_04_file2' f from dual;

-- Changeset multifolder/folder_02/subfolder_01/1.sql::1::includeall
select 'folder_02subfolder_01_file1' f from dual;

-- Changeset multifolder/folder_02/subfolder_01/2.sql::1::includeall
select 'folder_02subfolder_01_file2' f from dual;

-- Changeset multifolder/folder_02/subfolder_02/1.sql::1::includeall
select 'folder_02subfolder_02_file1' f from dual;

-- Changeset multifolder/folder_02/subfolder_02/2.sql::1::includeall
select 'folder_02subfolder_02_file2' f from dual;

-- Changeset multifolder/zfile.sql::1::includeall
select 'zfile.sql' f from dual;

So files and folders are sorted together level by level.

One moment you should to keep in mind. If I write

     <includeAll path="multifolder/"/>

instead of

     <includeAll path="/multifolder/"/>

I’ve got Unexpected error running Liquibase: Unknown Reason

ORA-12154: space is really matter

Got an stupid error ORA-12154: TNS:could not resolve the connect identifier specifed.
Double check tnsnames.ora, sqlnet.ora, TNS_ADMIN – all were correct
This one in tnsnames doesn’t work:

TEST =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL= TCP)(HOST= myhost)(Port= 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = db1)
)
)

and this one works:

TEST =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL= TCP)(HOST= myhost)(Port= 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = db1)
 )
)

Difference only in one space in penultimate line.

It’s interesting, that tnsping shows another error

TNS-12533: TNS:illegal ADDRESS parameters

but sqlplus

ORA-12154: TNS:could not resolve the connect identifier specifed

Sql*plus login.sql file

SQLPATH/login.sql

The User Profile is executed after the Site Profile and is intended to allow users to specifically customize their session. The User Profile script is generally named login.sql. SQL*Plus searches for the User Profile in your current directory, and then the directories you specify with the SQLPATH environment variable. SQL*Plus searches this colon-separated list of directories in the order they are listed.
Content of my file

set echo off
set termout off
set feedback off
set serveroutput on size 1000000
set trimspool on
set long 5000
set linesize 120
set pagesize 9999
column plan_plus_exp format a80
column global_name new_value gname
define _editor="C:\Program Files\IDM Computer Solutions\UltraEdit-32\Uedit32.exe"
define sqlplprompt=idle
column sqlplprompt noprint new_value sqlplprompt
-- user@connect_identifier
--select LOWER(user) || '@' || '&_CONNECT_IDENTIFIER' sqlplprompt from dual;
-- user@sqlprompt
--select LOWER(user) || '@' || global_name sqlplprompt from global_name;
-- connect identifier only
define sqlplprompt=&_CONNECT_IDENTIFIER
set sqlprompt '&sqlplprompt.>'
ALTER SESSION SET statistics_level = ALL;
set termout on
set feedback on

--set timing ondefine

Also you can use glogin.sql file, but usually we don’t have access to it

The Site Profile script is generally named glogin.sql. SQL*Plus or the iSQL*Plus Server executes this script whenever a user starts a SQL*Plus or iSQL*Plus session and successfully establishes the Oracle Database connection. The Site Profile enables the DBA to set up SQL*Plus environment defaults for all users of a particular SQL*Plus or iSQL*Plus Application Server installation. Users cannot directly access the Site Profile.The Site Profile script is $ORACLE_HOME/sqlplus/admin/glogin.sql in UNIX, and %ORACLE_HOME%\sqlplus\admin\glogin.sql in Windows.

Oracle scheduler and commit

Oracle scheduler DOES EXPLICIT COMMIT.
Proof and test case

dba_hist_sqlstat statistics

Template for PL/SQL developer based on link

Counts execution statistics from dba_hist_sqlstat for given sql_id

select to_char(min(s.end_interval_time),'DD.MM.YYYY HH24:MI') sample_end
, q.sql_id
, q.plan_hash_value
, sum(q.EXECUTIONS_DELTA) executions
, round(sum(DISK_READS_delta)/greatest(sum(executions_delta),1),1) pio_per_exec
, round(sum(BUFFER_GETS_delta)/greatest(sum(executions_delta),1),1) lio_per_exec
, round((sum(ELAPSED_TIME_delta)/greatest(sum(executions_delta),1)/1000000),3) sec_exec
from dba_hist_sqlstat q, dba_hist_snapshot s
where q.SQL_ID=trim('&sqlid')
and s.snap_id = q.snap_id
and s.dbid = q.dbid
and s.instance_number = q.instance_number
&<name="start_time_dd_mm_YYYY_hh24_mi"
  prefix="and s.end_interval_time >= to_date(trim('"
  suffix="'),'DD.MM.YYYY hh24:mi')">
&<name="end_time_dd_mm_YYYY_hh24_mi"
  prefix="and s.begin_interval_time <= to_date(trim('"
  suffix="'),'DD.MM.YYYY hh24:mi')">
group by s.snap_id
, q.sql_id
, q.plan_hash_value
order by s.snap_id, q.sql_id, q.plan_hash_value;