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

 

A. Without Date

1. Check

select * from teguhth.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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 

.::: Setting up and using the mutt email client in Linux for alert NMS or Other :::.

 

Berikut contoh penggunaan mutt untuk mengirim email dari command line di CentOS 9:

A. Setup

1. update repository

sudo dnf update -y

2. setup mutt

sudo dnf install mutt -y

3. check

mutt -v
 

.::: 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
);
 

.::: How to enable CDC (Change Data Capture) in SQL Server Using Trigger with client_ip :::.

 

Berikut adalah implementasi Change Data Capture (CDC) menggunakan trigger di SQL Server, termasuk contoh tabel, data awal, serta operasi INSERT, UPDATE, dan DELETE.

correlation  with http://teguhth.blogspot.com/2022/11/how-to-enable-cdc-change-data-capture.html
https://teguhth.blogspot.com/2024/12/how-to-enable-cdc-change-data-capture.html
 
1. Membuat Tabel Utama
Misalkan tabel utama bernama Products:

CREATE TABLE Products (
    ProductID INT IDENTITY PRIMARY KEY,
    ProductName NVARCHAR(100),
    Price DECIMAL(10, 2),
    Stock INT
);

.::: 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;

.::: How to enable CDC (Change Data Capture) in PostgreSQL, EDB with user,ip client :::.

 

correlation https://teguhth.blogspot.com/2024/04/how-to-enable-cdc-change-data-capture.html

1. Enable WAL (Write-Ahead Logging): PostgreSQL uses Write-Ahead Logging for durability. Ensure that your PostgreSQL server is configured to use WAL.

2. Create Audit Table: Create an audit table where you will log the changes.

CREATE TABLE audit_table (
    id SERIAL PRIMARY KEY,
    table_name TEXT NOT NULL,
    action_type TEXT NOT NULL,
    old_data JSONB,
    new_data JSONB,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    performed_by VARCHAR(100),
    ip_client TEXT NOT NULL
);

.::: How to enable CDC (Change Data Capture) in MariaDB, MySQL, MarinaDB in Table with user() information :::.

 

correlation https://teguhth.blogspot.com/2024/03/how-to-enable-cdc-change-data-capture.html


Enable Binary Logging
1. check table for sample

SELECT *,@@hostname,@@version FROM teguhth.pembelian
 
2. Create table for cdc

# create table

CREATE TABLE audit_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(100),
    action_type ENUM('INSERT', 'UPDATE', 'DELETE'),
    old_data JSON,
    new_data JSON,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    performed_by VARCHAR(100)
);

ALTER TABLE audit_table
ADD COLUMN performed_by VARCHAR(100);

SELECT *,@@hostname,@@version FROM audit_table;

.::: Insert data table to another table with different Database in SQL Server :::.

 

  A. Without Date

1. Check

select * from teguhth..pembelian
 

2. create table & insert firstime

-- pertama kali insert table
select * into dwh..pembelian_clone from teguhth..pembelian
 

.::: Shrink Disk VMWare for Linux & Windows Virtual Machine :::.

 A. Shrink Disk Linux Virtual Machine

1. check before shrink
 

2. Shrink via Terminal

dd if=/dev/zero of=zerofile bs=1M
 

.::: Setup Polybase in SQL Server for External Table MSSQL :::.

 

Fungsi PolyBase di SQL Server untuk Sumber Eksternal
PolyBase di SQL Server memungkinkan Anda untuk mengakses data dari berbagai sumber eksternal, termasuk SQL Server lain dan MSSQL. Dengan PolyBase, Anda dapat menjalankan query T-SQL di SQL Server yang menggabungkan data dari sumber eksternal tersebut tanpa memindahkan data.

1. setup ODBC MSSQL

2. Buat Credential untuk Login

CREATE DATABASE SCOPED CREDENTIAL RemoteCred
WITH IDENTITY = 'admin', SECRET = 'admin';

.::: Setup Polybase in SQL Server for External Table PostgreSQL :::.

 

Fungsi PolyBase di SQL Server untuk Sumber Eksternal
PolyBase di SQL Server memungkinkan Anda untuk mengakses data dari berbagai sumber eksternal, termasuk SQL Server lain dan PostgreSQL. Dengan PolyBase, Anda dapat menjalankan query T-SQL di SQL Server yang menggabungkan data dari sumber eksternal tersebut tanpa memindahkan data.

1. setup ODBC PostgreSQL
 

2. Buat Credential untuk Login

CREATE DATABASE SCOPED CREDENTIAL PGCred
WITH IDENTITY = 'admin', SECRET = 'admin'

.::: Setup Polybase in SQL Server for External Table MariaDB :::.

 

Fungsi PolyBase di SQL Server untuk Sumber Eksternal
PolyBase di SQL Server memungkinkan Anda untuk mengakses data dari berbagai sumber eksternal, termasuk SQL Server lain dan MariaDB. Dengan PolyBase, Anda dapat menjalankan query T-SQL di SQL Server yang menggabungkan data dari sumber eksternal tersebut tanpa memindahkan data.

1. setup ODBC MariaDB
 

2. Buat Credential untuk Login

CREATE DATABASE SCOPED CREDENTIAL MariadbCred
WITH IDENTITY = 'admin', SECRET = 'admin';

.::: 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';