sqlplus komutu

Giriş

sqlplus için bazen ORACLE_HOME ortam değişkenini kullanılıyor. Şöyle yaparız

export ORACLE_BASE=/u02/app/oracle/product/12.2.0 
export ORACLE_HOME=$ORACLE_BASE/dbhome_1 
export ORACLE_SID=dbz 
export PATH=$PATH:$ORACLE_HOME/bin

Örnek

Şöyle yaparız

$ sqlplus / as sysdba

Örnek - bash içinden kullanma

Şöyle yaparız.

# set errorcodes
SCRIPT_ERROR=1
NO_ERROR=19968
ALREADY_INSTALLED=20000
ALREADY_EXISTS=20001
COMPILATION_ERRORS=20100

Şöyle yaparız. Burada EOF .. EOF ile Bash Here Document kullanılıyor. Aslında gereksiz.

SQLADM="$ORACLE_HOME/bin/sqlplus -S $DBUSER/$DBPASSWORD@\"${DBURL}\" as sysdba"

SCRIPTS=`/bin/ls -1`

SQL_HEADER="SQL.HEADER"
SQL_FOOTER="SQL.FOOTER"

for f in $SCRIPTS
do
  cat $SQL_HEADER $f $SQL_FOOTER > $SCRIPT_TO_RUN
  $SQLADM @$SCRIPT_TO_RUN $f<<EOF
EOF
  ERRORCODE=$(( $? + $NO_ERROR ))
  if [ $ERRORCODE -eq $ALREADY_INSTALLED ]; then
    echo "[INFO] Skipped script $f - already installed"
    echo "[INFO] ------------------------------"
    continue
  elif [ $ERRORCODE -eq $ALREADY_EXISTS ]; then
    echo "[INFO] Skipped script $f - already exists"
    echo "[INFO] ------------------------------"
    continue
  elif [ $ERRORCODE -eq $COMPILATION_ERRORS ]; then
    echo "[ERROR] PL/SQL compilation errors, failed to install $f"
    echo "[ERROR] ##############################"
    exit $ERRORCODE
  elif [ $ERRORCODE -ne $NO_ERROR ]; then
    echo "[ERROR] SQL errors, failed to install $f!"
    echo "[ERROR] ##############################"
    exit $ERRORCODE
  else
    echo "[INFO] Successfully installed $f"
    echo "[INFO] ------------------------------"
  fi
done

CREATE USER

Örnek
Şöyle yaparız

DROP USER myuser CASCADE;
CREATE USER adv IDENTIFIED BY "2Ab}7C";
GRANT ALL PRIVILEGES TO myuser;
GRANT EXECUTE ON utl_file TO myuser;
COMMIT;
QUIT

GRANT SELECT ON

Örnek

Şöyle yaparız. DBA_SCHEDULER_JOBS Oracle tablosu

GRANT SELECT ON DBA_SCHEDULER_JOBS TO MYUSER;
GRANT SELECT ON v_$database TO MYUSER;

Açıklaması şöyle. Yani burada SYS'ye ait tablolara da erişim veriliyor.

v$database is actually a public synonym for the v_$database view owned by sys (checked in Oracle 12).

Invoking Java in the Database

Giriş

Kendi kodumdan Stored Procedure çağrısı yaparak aslında bir Java kodunu çalıştırmak mümkün.

Örnek

Şöyle yaparız

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED MYSCHEMA.JavaTools
AS 
package util.oracle;
import java.io.*;
import java.net.InetAddress;  
import java.util.List;

public class JavaTools {

  public static double pingOSCommand(String Command){
    double time = -1;
    try {
      Process process = Runtime.getRuntime().exec(Command);
      process.waitFor();
      BufferedReader stdInput = new BufferedReader(
        new InputStreamReader(process.getInputStream()));
      BufferedReader stdError = new BufferedReader(
        new InputStreamReader(process.getErrorStream()));
      while ((String s = stdInput.readLine()) != null) {
        if (s.contains("time=")) {
	  if (s.contains("ms")){
            time = Double.parseDouble(s.substring(s.indexOf("time=")+5,
              s.indexOf("ms")-1));
	  }
        }
      }
      while ((s = stdError.readLine()) != null) {
        System.out.println(s);
      }
      return time;
    } catch (Exception e){
      System.out.println(e.getMessage());
      return -1;
    } 
  }
};
/ 
/******************************************************************************/
GRANT EXECUTE ON JAVA SOURCE MYSCHEMA.RailwayJavaTools TO MYUSER;
COMMIT;

Execute dbms_java.grant_permission( 'MYUSER', 'SYS:java.io.FilePermission',
  '<<ALL FILES>>', 'execute');
execute dbms_java.grant_permission( 'MYUSER', 'SYS:java.lang.RuntimePermission',
  'writeFileDescriptor', '*' );
execute dbms_java.grant_permission( 'MYUSER', 'SYS:java.lang.RuntimePermission',
  'readFileDescriptor', '*' );
COMMIT;

Bunu çağırmak için şöyle yaparız. Sonuç olarak double döndürür

CREATE OR REPLACE PACKAGE MYSCHEMA.MyTools 
AS
  function f_pingOSCommand(command varchar2) return number;
END MyTools;
/

CREATE OR REPLACE PACKAGE body MYSCHEMA.MyTools
AS
  function f_pingOSCommand(command varchar2) 
  return number 
  as
  language java
  NAME 'util.oracle.JavaTools.pingOSCommand(java.lang.String) return double';
		
end MYTools;
/

Bunu çağırmak için şöyle yaparız. Sonuç olarak double döndürür

SELECT MYSCHEMA.MyTools.f_pingOSCommand(?) FROM DUAL

Oracle Streams Senkron Kullanım

Giriş

Şeklen şöyle

Senkron kullanım için adımlar burada

1. Create User & Setup Privileges : Stream Administrator rolü yaratılır

2. Create Database Links at source and target

3. Create Queue at source and target

4. Create Capture at source

5. Create Apply Process at target

6. Create Propagation at source

7. Instantiation at target

8 Start Apply at target

9. Start Capture at source

Kullanılan PL/SQL paketleri şöyle

DBMS_APPLY_ADM

DBMS_CAPTURE_ADM

DBMS_FLASHBACK

DBMS_OUTPUT

DBMS_STREAMS_ADM

DBMS_APPLY_ADM Paketi

START_APPLY(), STOP_APPLY(), DELETE_ALL_ERRORS() gibi metodlar kullanılabilir

DBMS_APPLY_ADM.START_APPLY(apply_name => 'SYNC_APPLY_1');
...
DBMS_APPLY_ADM.STOP_APPLY(apply_name => 'SYNC_APPLY_1');
DBMS_APPLY_ADM.DELETE_ALL_ERRORS(apply_name => 'SYNC_APPLY_1');

Duplicate Rows

Açıklaması şöyle

1. You cannot use Oracle Streams to replicate a table which has duplicate rows. It will not work.
2. If you don’t have unique constraint on your table in some form, you will have duplicate rows.

Create Database Links

Şöyle yaparız

--on source
CONNECT strmadmin/strmadminpw;
CREATE DATABASE LINK REP01 CONNECT TO strmadmin IDENTIFIED BY strmadminpw USING 'REP01';
select count(*) from dba_tables@REP01;

-- on target
CONNECT strmadmin/strmadminpw;
CREATE DATABASE LINK DB05 CONNECT TO strmadmin IDENTIFIED BY strmadminpw USING 'DB05';
select count(*) from dba_tables@DB05

Create Queue

Örnek

Şöyle yaparız

DBMS_STREAMS_ADM.SET_UP_QUEUE(
 queue_table =>'STRADMIN.SQT_IMMEDIATE1_S', 
 queue_name => 'STRADMIN.SQ_IMMEDIATE_S');

Apply

streams_type olarak APPLY verilebilir

Örnek

Şöyle yaparız.

DBMS_STREAMS_ADM.ADD_TABLE_RULES(
  table_name => ...,
  streams_type => 'apply',
  streams_name => ...,
  queue_name => ...
);

Capture

streams_type olarak CAPTURE, SYNC_CAPTURE verilebilir. Açıklaması şöyle

Synchronous Mode

Synchronous Capture uses triggers on the source database to capture change data. It has no latency because the change data is captured continuously and in real-time on the source database. The change tables are populated when DML operations on the source table are committed. Hence it is titled “Synchronous” Change Data Capture.

New change data arrives automatically as DML operations on the source tables are committed. Publishers can define new change sets in the predefined SYNC_SOURCE change source or use the predefined change set, SYNC_SET. The SYNC_SET change set cannot be altered or dropped.

While the “invasive” nature of the approach of the synchronous mode of Change Data Capture adds overhead to the source database at capture time, this mode can reduce costs (as compared to attempting to extract change data using table differencing or change-value section) by simplifying the extraction of change data.

Örnek - Senkron

Şöyle yaparız. inclusion_rule FALSE verilerek capture işlemi yapılmıyor

DBMS_STREAMS_ADM.ADD_TABLE_RULES(
  table_name=>'DEV110.APP_QUEUE',
  streams_type=>'capture',
  streams_name=>'CAPTURE_110DEV',
  queue_name=>'REP_CAPTURE_QUEUE',
  include_dml=>TRUE,
  include_ddl=>TRUE,
  include_tagged_lcr=> FALSE,
  source_database=>'DB05',
  inclusion_rule=>FALSE,
  and_condition=>NULL);