.::: Script to kill Locking in Database Oracle using oracle & root User :::.

 

1. run from https://teguhth.blogspot.com/2024/11/how-to-sample-simulation-blocking.html

export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export ORACLE_SID=tgh
export PATH=$ORACLE_HOME/bin:$PATH

 

or 

 

export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1 
export ORACLE_SID=tgh 
export PATH=$ORACLE_HOME/bin:$PATH 

2. original script locking
SELECT
    s1.sid AS waiting_session,
    s1.serial# AS waiting_serial,
    s1.username AS waiting_user,
    s1.status AS waiting_status,
    s2.sid AS blocking_session,
    s2.serial# AS blocking_serial,
    s2.username AS blocking_user,
    s2.status AS blocking_status,
 s1.event,
    'ALTER SYSTEM KILL SESSION ''' || s1.sid || ',' || s1.serial# || ''' IMMEDIATE;' AS query_kill
FROM
    v$session s1
JOIN
    v$session s2
ON
    s1.blocking_session = s2.sid;

.::: Simulation create view with additional ID Unique row_number(),hash /md5 & Primary Key in Oracle :::.

 

1. Buat Tabel dengan tanggal_lahir
CREATE TABLE karyawan (
    emp_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    nama VARCHAR2(100),
    jabatan VARCHAR2(100),
    lokasi VARCHAR2(100),
    tanggal_lahir DATE
);

2. Insert Data Sample (dengan tanggal lahir)

.::: Troubleshoot oracle PLS-00306: wrong number or types of arguments in call to 'SPBUY' PL/SQL: Statement ignored :::.

Dari gambar error tersebut terlihat jelas penyebabnya:

procedure spbuy(integer, integer, integer, integer, integer, integer, integer, integer, integer, integer, integer, integer, integer, integer) does not exist

Artinya: FUNCTION atau Stored Procedure spbuy (atau sptaxsample) membutuhkan 15 parameter, tapi saat dipanggil hanya diberikan 14 parameter.

. Tujuan Anda

Anda ingin mensimulasikan kasus tersebut pada database sample oracle dengan database contoh bernama hris.

Saya buatkan contoh struktur database, fungsi, dan pemanggilan yang benar & salah, sehingga error yang sama bisa direplikasi.

. SIMULASI DI oracle: Database hris

1. Buat Database Sample

 

.::: Testing Script Lucee using Driver PostgreSQL, EDB, Oracle, MSSQL SQL Server Database :::.

 

1. run mssql

http://10.10.10.90:8888/belimssql.cfm
 

2. run edb

http://10.10.10.90:8888/beliedb.cfm
 

3. run oracle

http://10.10.10.90:8888/belioracle.cfm
 

.::: Grouping Year, Month, Day, Time, Date for Archive, Cleansing in Oracle Database :::.

1. create table 

CREATE TABLE datefilter (
    datex DATE NOT NULL
);

CREATE TABLE datefilter (
    datex TIMESTAMP NOT NULL
);

2. create date sample 3 years

INSERT INTO datefilter (datex)
WITH cte AS (
    SELECT TRUNC(ADD_MONTHS(SYSDATE, 1 - LEVEL), 'MM') AS dt
    FROM dual
    CONNECT BY LEVEL <= 36
)
SELECT dt + INTERVAL '1' SECOND
FROM cte;

INSERT INTO datefilter (datex)
WITH cte AS (
    SELECT TRUNC(ADD_MONTHS(SYSDATE, 1 - LEVEL), 'MM') AS dt
    FROM dual
    CONNECT BY LEVEL <= 36
)
SELECT dt + INTERVAL '15' DAY + INTERVAL '1' SECOND
FROM cte;

.::: Create CDB & Sample Database in Oracle 19C :::.

 

1. Login sebagai SYS atau SYSTEM

Masuk ke CDB lalu kasih privilege ke HRADMIN:

-- Masuk ke CDB sebagai SYS
sqlplus sys@//localhost:1521/TGHDB as sysdba

-- Pindah ke PDB TGHDBHR
ALTER SESSION SET CONTAINER=TGHDBHR;

-- Beri hak penuh

CREATE USER hradmin IDENTIFIED BY hrpass;
GRANT CONNECT, RESOURCE TO hradmin;
ALTER USER hradmin QUOTA UNLIMITED ON USERS;

GRANT CONNECT, RESOURCE TO hradmin;
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE TO hradmin;

-- Kalau mau full DBA di PDB
GRANT DBA TO hradmin;

sqlplus hradmin/hrpass@//localhost:1521/TGHDBHR
 

.::: Create New Container or add Container CDB in Oracle 19C :::.

 

1. Cek dulu container yang ada

sqlplus sys/oracle as sysdba;

SHOW CON_NAME;
SHOW PDBS;
 

2. Buat PDB baru dari PDB$SEED (template bawaan)

Misalnya mau buat container baru bernama TGHDBHR:

CREATE PLUGGABLE DATABASE TGHDBHR
  ADMIN USER hradmin IDENTIFIED BY hrpass
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/TGH/pdbseed/',
                     '/u01/app/oracle/oradata/TGH/TGHDBHR/');

.::: First time login Oracle using CDB (Container Database / Multitenant) :::.

 

1. login as sysdba 
sqlplus sys/oracle as sysdba;

oracle@teguhth ~]$ sqlplus sys/oracle as sysdba;

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 24 14:12:31 2025
Version 19.3.0.0.0

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

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> 
 

.::: Insert data table to another table with different Database in Oracle Database :::.

 

A. Without Date

1. Check

select * from pembelian;
 

2. table after create
CREATE TABLE pembelian_clone (
    KODE_PEMBELIAN     CHAR(10) NOT NULL,
    KODE_BARANG        CHAR(6) NOT NULL,
    KODE_CUSTOMER      CHAR(6) NOT NULL,
    TANGGAL_PEMBELIAN  DATE DEFAULT NULL,
    JUMLAH_PEMBELIAN   DECIMAL(4,0) DEFAULT NULL
);
 

.::: how to use DBMS_CRYPTO.ENCRYPT dan DBMS_CRYPTO.DECRYPT in Oracle as example table pembelian :::.

 

Oke! Saya akan kasih kamu contoh penggunaan DBMS_CRYPTO.ENCRYPT dan DBMS_CRYPTO.DECRYPT pada tabel pembelian. Di sini, kita akan menyimpan kolom credit_card_number dalam bentuk terenkripsi, dan mendekripsinya saat dibaca.

Studi Kasus: Tabel pembelian_card

1. grant access

GRANT EXECUTE ON DBMS_CRYPTO TO admin;
commit;

2. Buat tabel:

CREATE TABLE pembelian_card (
    id               NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    nama_pembeli     VARCHAR2(100),
    credit_card_enc  RAW(2000) -- data terenkripsi
);

.::: Backup restore Export import expdp impdp from schema to another schema in oracle using crontab :::.

 

correlation https://teguhth.blogspot.com/2024/12/backup-restore-export-import-expdb.html


1. backup manual

[oracle@teguhth ~]$ expdp aisyah/hanin@localhost:1521/tgh DIRECTORY=DATA_PUMP_DIR DUMPFILE=aisyah_schema.dmp LOGFILE=aisyah_schema.log SCHEMAS=aisyah

Export: Release 19.0.0.0.0 - Production on Tue Apr 22 14:10:38 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "AISYAH"."SYS_EXPORT_SCHEMA_01":  aisyah/********@localhost:1521/tgh DIRECTORY=DATA_PUMP_DIR DUMPFILE=aisyah_schema.dmp LOGFILE=aisyah_schema.log SCHEMAS=aisyah
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
. . exported "AISYAH"."BARANG"                           6.984 KB       6 rows
. . exported "AISYAH"."CUSTOMER"                         7.554 KB       6 rows
. . exported "AISYAH"."EMPLOYEES"                        6.359 KB       2 rows
. . exported "AISYAH"."EMPLOYEES_CHANGE_LOG"             8.312 KB       5 rows
. . exported "AISYAH"."PASOK"                            7.695 KB      12 rows
. . exported "AISYAH"."PEMBELIAN"                        7.835 KB      15 rows
. . exported "AISYAH"."SUPLIER"                          7.578 KB       6 rows
Master table "AISYAH"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for AISYAH.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/backup/aisyah_schema.dmp
Job "AISYAH"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Apr 22 14:11:39 2025 elapsed 0 00:00:58

[oracle@teguhth ~]$
 

.::: Enable Change Data Capture (CDC) Oracle Database using TRIGGER with session_user & client_ip :::.

 

Berikut adalah langkah-langkah untuk mengaktifkan Change Data Capture (CDC) di Oracle menggunakan Trigger, termasuk contoh tabel, isi tabel, dan implementasi trigger untuk melacak perubahan data:

1. Membuat Tabel Sumber
Tabel ini adalah tabel utama tempat data disimpan, dan perubahan pada tabel ini akan dilacak.

CREATE TABLE EMPLOYEES (
    EMP_ID NUMBER PRIMARY KEY,
    EMP_NAME VARCHAR2(50),
    DEPARTMENT_ID NUMBER,
    SALARY NUMBER
);
 

.::: Query Check user session, current, Client IP in MariaDB MySQL, PostgreSQL EDB, MSSQL SQL Server, Oracle :::.

 

1. MariaDB MySQL

select USER();
 

2. PostgreSQL

SELECT session_user, CURRENT_USER || '@' || host(inet_client_addr()) AS user_with_ip;  
SELECT CURRENT_USER || '@' || host(inet_client_addr()) AS user_with_ip;
SELECT COALESCE(host(inet_client_addr()), 'localhost') AS ip_only;
SELECT session_user,CURRENT_USER || '@' || inet_client_addr() AS user_with_ip;

.::: Query to check table, column, compare schema database, structure table in Oracle Database :::.

 

Untuk membandingkan struktur tabel antara dua database teguhth dan aisyah di SQL Server, kita bisa menggunakan query yang membandingkan schema (skema tabel), kolom, tipe data, dan properti lainnya.

A. Query to check table & system table

1. ALL_TABLES & ALL_TAB_COLUMNS

SELECT OWNER, TABLE_NAME
FROM ALL_TABLES
WHERE OWNER = 'AISYAH';

SELECT OWNER, TABLE_NAME
FROM ALL_TABLES
WHERE OWNER = 'TEGUHTH';
 

.::: Script EXPDP Backup Daily Database Oracle 19C with Record to Table with Duration with insert client table :::.

 

correlation https://teguhth.blogspot.com/2025/02/script-expdp-backup-daily-database.html

A. Create Backup with record table
1. Create DATA_PUMP_DIR backup directory

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/home/oracle/backup';
GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO aisyah;

2. create schema (dbatools)

.::: Script EXPDP Backup Daily Database Oracle 19C with Record to Table with Duration :::.

 

A. Create Backup with record table
1. Create DATA_PUMP_DIR backup directory

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/home/oracle/backup';
GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO aisyah;

2. create schema (dbatools)

CREATE USER dbatools IDENTIFIED BY dbatools;
GRANT CONNECT, RESOURCE,oem_monitor TO dbatools;
ALTER USER dbatools QUOTA UNLIMITED ON USERS;

-- Memberikan hak akses dasar kepada user dbatools
GRANT CONNECT, RESOURCE,oem_monitor TO dbatools;

-- Memberikan hak akses untuk melakukan dbatoolsistrasi
GRANT CREATE SESSION TO dbatools;
GRANT UNLIMITED TABLESPACE TO dbatools;
GRANT DBA TO dbatools;

.::: Script EXPDP Backup Daily Database Oracle 19C with Record to Table :::.

 

A. Create Backup with record table
1. Create DATA_PUMP_DIR backup directory

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/home/oracle/backup';
GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO aisyah;

2. create schema (dbatools)

CREATE USER dbatools IDENTIFIED BY dbatools;
GRANT CONNECT, RESOURCE,oem_monitor TO dbatools;
ALTER USER dbatools QUOTA UNLIMITED ON USERS;

-- Memberikan hak akses dasar kepada user dbatools
GRANT CONNECT, RESOURCE,oem_monitor TO dbatools;

-- Memberikan hak akses untuk melakukan dbatoolsistrasi
GRANT CREATE SESSION TO dbatools;
GRANT UNLIMITED TABLESPACE TO dbatools;
GRANT DBA TO dbatools;
 

.::: Shell Script to create,insert data CRUD Oracle Database :::.

 

1. check version
sh check_version.sh
 
2. insert data
sh create_insert.sh

3. script check version

[oracle@teguhth blog]$ cat check_version.sh
#!/bin/bash

# Konfigurasi koneksi database
vlogin="aisyah/hanin@//10.10.10.19:1521/tgh"

# Menjalankan query SQL dan menyimpan output ke file
sqlplus -s "$vlogin" <<EOF > output_instance_info.txt
SET HEADING ON
SET FEEDBACK OFF
SET PAGESIZE 100
SET LINESIZE 200

.::: Query Convert select to insert table in SQL Server MSSQL, MariaDB MySQL, PostgreSQL EDB, Oracle :::.

 

A. MariaDB
    
1. Query basic

select * from pembelian;

2. query to convert select to insert
SELECT CONCAT(
    'INSERT INTO pembelian (KODE_PEMBELIAN, KODE_BARANG, KODE_CUSTOMER, TANGGAL_PEMBELIAN, JUMLAH_PEMBELIAN) VALUES (',
    QUOTE(KODE_PEMBELIAN), ', ',
    QUOTE(KODE_BARANG), ', ',
    QUOTE(KODE_CUSTOMER), ', ',
    QUOTE(TANGGAL_PEMBELIAN), ', ',
    JUMLAH_PEMBELIAN, ');'
) AS insert_query
FROM pembelian;

.::: Script Daily Backup Database Oracle using expdp :::.

 

1. create dba directory

https://teguhth.blogspot.com/2024/12/how-to-backup-and-restore-table.html

su - oracle
mkdir -p /home/oracle/backup
mkdir -p  /u01/backup/daily

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/home/oracle/backup';
GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO aisyah;

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/home/oracle/backup';
GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO ADMIN;

CREATE OR REPLACE DIRECTORY BACKUP_DAILY AS '/u01/backup/daily';
GRANT READ, WRITE ON DIRECTORY BACKUP_DAILY TO aisyah;