h1. CREATE USER LOCAL
alter session set container=ORCLPDB1;
alter session set "_ORACLE_SCRIPT"=true;
alter session set container = cdb$root;
CREATE USER TCDN_MOI_ADAN_GOC IDENTIFIED BY Vdi2024 CONTAINER=ALL;
grant all privileges to TCDN_MOI_ADAN_GOC;
GRANT CONNECT, RESOURCE TO TCDN_MOI_ADAN_GOC;
grant all privileges to c##roletestbh;
grant c##roletestbh to TCDN_MOI_ADAN_GOC CONTAINER=ALL;
ALTER USER TCDN_MOI_ADAN_GOC quota unlimited on USERS;

ALTER USER TCDN_MOI_ADAN_GOC quota unlimited on USERS;

h1. Create user AAA

CREATE USER GUEST IDENTIFIED BY passssssss
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP_GRP;

GRANT CONNECT TO GUEST;

GRANT EXECUTE ANY PROCEDURE TO GUEST;
GRANT ALTER ANY SEQUENCE TO GUEST;
GRANT CREATE SESSION TO GUEST;

####ExportDB/ImportDB
GRANT EXP_FULL_DATABASE TO TCDN_MOI_ADAN_GOC;

-- hoac
alter session set container=ORCLPDB1;
alter session set "_ORACLE_SCRIPT"=true;
alter session set container = cdb$root;
CREATE USER tcdn_moi_adan IDENTIFIED BY Vdi2024 CONTAINER=ALL;
grant all privileges to tcdn_moi_adan;
GRANT CONNECT, RESOURCE TO tcdn_moi_adan;

h1. DUMP

-- exp online pro + function
expdp schemas=TCDN_MOI_ADAN directory=DATA_PUMP_DIR dumpfile=AAA_241122_v1.dmp logfile=AAA_241122_v1.log include=PROCEDURE,FUNCTION

impdp schemas=tcdn_moi dumpfile=TCDN_MOI_2024_05_14.DMP logfile=TCDN_import_11052024.log include=PROCEDURE,FUNCTION

--

• impdp schemas=tcdn_moi dumpfile=TCDN_MOI_2024_05_14.DMP logfile=TCDN_import_11052024.log

• impdp remap_schema=TCDN_MOI:tcdn_moi_adan dumpfile=TCDN_MOI_2024_05_28.DMP logfile=TCDN_MOI_2024_05_28.log

h1. KILL SESSION

DECLARE
open_count INTEGER;
BEGIN
EXECUTE IMMEDIATE 'alter session set "_ORACLE_SCRIPT"=true';
EXECUTE IMMEDIATE 'alter user TCDN_MOI_ADAN account lock';
FOR session IN (SELECT sid, serial#
FROM v$session
WHERE username = 'TCDN_MOI_ADAN')
LOOP
EXECUTE IMMEDIATE 'alter system disconnect session ''' || session.sid || ',' || session.serial# || ''' immediate';
END LOOP;
LOOP
SELECT COUNT(1)
INTO open_count
FROM v$session
WHERE username = 'TCDN_MOI_ADAN';
EXIT WHEN open_count = 0;
dbms_lock.sleep(1); -- Tăng thời gian chờ lên 1 giây
END LOOP;
EXECUTE IMMEDIATE 'drop user TCDN_MOI_ADAN cascade';
END;

h1. Lỗi EDITIONALE

DECLARE
v_sql VARCHAR2(1000);
BEGIN
FOR obj_rec IN (SELECT OBJECT_NAME, OWNER,EDITIONABLE
FROM ALL_OBJECTS
WHERE OBJECT_TYPE = 'PROCEDURE'
AND OWNER = 'TCDN_MOI_ADAN'
AND EDITIONABLE = 'Y') LOOP
-- Chuyển procedure thành noneditionable
v_sql := 'ALTER PROCEDURE ' || obj_rec.OWNER || '.' || obj_rec.OBJECT_NAME || ' NONEDITIONABLE';
EXECUTE IMMEDIATE v_sql;
END LOOP;
END;
/
DECLARE
v_sql VARCHAR2(1000);
BEGIN
FOR obj_rec IN (SELECT OBJECT_NAME, OWNER,EDITIONABLE
FROM ALL_OBJECTS
WHERE OBJECT_TYPE = 'FUNCTION'
AND OWNER = 'TCDN_MOI_ADAN'
AND EDITIONABLE = 'Y') LOOP
-- Chuyển procedure thành noneditionable
v_sql := 'ALTER FUNCTION ' || obj_rec.OWNER || '.' || obj_rec.OBJECT_NAME || ' NONEDITIONABLE';
EXECUTE IMMEDIATE v_sql;
END LOOP;
END;

h1. Khác

h3. Đổi schema
select sys_context( 'userenv', 'current_schema' ) from dual;

ALTER SESSION SET CURRENT_SCHEMA = AAA_TCDN_1;

h1. Kiem tra va kill session

DECLARE
open_count INTEGER;
BEGIN
EXECUTE IMMEDIATE 'alter session set "_ORACLE_SCRIPT"=true';
-- cau lenh nau de lock user -- unlock alter user TCDN_MOI_ADAN account unlock (bang user khac)
--EXECUTE IMMEDIATE 'alter user TCDN_MOI_ADAN account lock';
FOR session IN (SELECT sid, serial#
FROM v$session
WHERE username = 'TCDN_MOI_ADAN')
LOOP
EXECUTE IMMEDIATE 'alter system disconnect session ''' || session.sid || ',' || session.serial# || ''' immediate';
END LOOP;
LOOP
SELECT COUNT(1)
INTO open_count
FROM v$session
WHERE username = 'TCDN_MOI_ADAN';
EXIT WHEN open_count = 0;
dbms_lock.sleep(1); -- Tăng thời gian chờ lên 1 giây
END LOOP;
-- cau lenh nay de xoa schema
--EXECUTE IMMEDIATE 'drop user TCDN_MOI_ADAN cascade';
END;

-- lay ra session dang on
SELECT *
FROM v$session
WHERE username = 'TCDN_MOI_ADAN' and status = 'ACTIVE';

h1. Kill query bi treo

SELECT
s.sid,
s.serial#,
s.username,
s.machine,
s.program,
o.object_name
FROM
v$session s,
v$locked_object l,
dba_objects o
WHERE
s.sid = l.session_id
AND l.object_id = o.object_id;
/
SELECT
s.sid,
s.serial#,
s.username,
s.program,
t.start_time
FROM
v$session s,
v$transaction t
WHERE
s.saddr = t.ses_addr;

ALTER SYSTEM KILL SESSION '742,36467' IMMEDIATE;

------ Gen lệnh kill
SET SERVEROUTPUT ON;
BEGIN
FOR rec IN (
SELECT 'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# || ''' IMMEDIATE;' AS kill_command
FROM v$lock l
JOIN v$session s ON l.sid = s.sid
JOIN dba_objects o ON l.id1 = o.object_id
WHERE o.object_name = 'TEMP_1'
AND o.owner = 'AAA_TCDN'
) LOOP
DBMS_OUTPUT.PUT_LINE(rec.kill_command);
END LOOP;
END;

h1. Xóa tất cả object trong schema

SET SERVEROUTPUT ON;
BEGIN
FOR rec IN (
SELECT object_name, object_type, owner
FROM all_objects
WHERE owner = 'AAA_TCDN'
AND object_type IN ('TABLE', 'VIEW', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'INDEX', 'SEQUENCE', 'TRIGGER')
) LOOP
BEGIN
DBMS_OUTPUT.PUT_LINE('Đang xóa ' || rec.object_type || ' ' || rec.owner || '.' || rec.object_name);
EXECUTE IMMEDIATE 'DROP ' || rec.object_type || ' ' || rec.owner || '.' || rec.object_name;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Không thể xóa ' || rec.object_type || ' ' || rec.owner || '.' || rec.object_name || ' - ' || SQLERRM);
END;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Hoàn tất xóa các đối tượng.');
END;

h1. kill session bao cao

SELECT 'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# || ''';'
FROM v$session s
JOIN v$tempseg_usage t ON s.saddr = t.session_addr
WHERE t.username='TCDN_MOI_ADAN_V1' and t.tablespace IN (
SELECT tablespace_name FROM dba_tablespaces WHERE contents = 'TEMPORARY'
);

h1. lich backup

create user export_user identified by Vdi2024;
grant create session, exp_full_database, datapump_exp_full_database to export_user;
ALTER USER EXPORT_USER QUOTA UNLIMITED ON USERS;

CREATE OR REPLACE DIRECTORY BACKUP_V1_DIR AS '/opt/oracle/admin/ORCLCDB/backup/TCDN_MOI_ADAN_V1';
GRANT READ, WRITE ON DIRECTORY BACKUP_V1_DIR TO EXPORT_USER;
SELECT * FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME = 'BACKUP_DIR_V1';

-----------chown -R oracle:oinstall /opt/oracle/admin/ORCLCDB/backup/¶

vi backup_expdp.sh

#!/bin/bash

echo "ORACLE_HOME=$ORACLE_HOME"
echo "ORACLE_SID=$ORACLE_SID"
echo "TNS_ADMIN=$TNS_ADMIN"

bien môi trường¶

export ORACLE_HOME=/opt/oracle/product/19c/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

Kiểm tra nếu expdp có thể chạy¶

which expdp

Tạo tên file dump và log động¶

DATE=$(date +%y%m%d_%H%M%S)

Thực hiện backup¶

expdp export_user/Vdi2024@ORCLCDB schemas=TCDN_MOI_ADAN_V1 directory=BACKUP_V1_DIR dumpfile=BACKUP_${DATE}.dmp logfile=BACKUP_${DATE}.log include=PROCEDURE,FUNCTION

Kiểm tra nếu expdp chạy thành công¶

if [ $? -eq 0 ]; then
echo "Backup completed successfully."
else
echo "Backup failed."
fi

--
chmod +x backup_expdp.sh
-- hoac
chmod +x /opt/script/backup_expdp.sh

--
crontab -e

0 0 * * * /opt/script/backup_expdp.sh
0 12 * * * /opt/script/backup_expdp.sh

--- kiem tra job
grep CRON /var/log/cron
crontab -e
crontab -l
cat $ORACLE_HOME/network/admin/tnsnames.ora

-- In tham so

create or replace noneditionable function FBH_BT_KBT(
b_ktra varchar2,b_tien_bh number,b_t_that number,b_bth number,b_nd varchar2,b_kho number:=0) return varchar2
as
b_loi varchar2(500):=''; b_n number:=0; b_dk varchar2(1):='L';
begin
b_loi := 'Tham số: b_ktra=' || b_ktra ||
', b_tien_bh=' || b_tien_bh ||
', b_t_that=' || b_t_that ||
', b_bth=' || b_bth ||
', b_nd=' || b_nd ||
', b_kho=' || b_kho;
-- Dan - Muc khau tru/vu tren muc trach nhiem

return b_loi;
exception WHEN OTHERS THEN
b_loi := 'Lỗi trong FBH_BT_KBT: ' || SQLERRM || ' | Tham số: ' || b_loi;
RAISE_APPLICATION_ERROR(-20001, b_loi);
end;