DML TRIGGERS IN ORACLE DB

 -- Table for update trigger 

create table student_update_info (
id number primary key,
old_name varchar2(200),
new_name varchar2(200),
time_stamp varchar2(100),
message varchar2(200)
);

-- Table for insert and delete trigger 

create table student_info (
log_info varchar2(200), 
time_stamp varchar2(100)
)

-- AFTER INSERT Trigger

CREATE OR REPLACE TRIGGER student_insert_trigger
AFTER INSERT ON student
FOR EACH ROW
BEGIN
    -- Code to be executed after insert
    -- For example, logging the insertion along with timestamp
    INSERT INTO student_info (log_info , time_stamp) 
    VALUES ('New row inserted', TO_CHAR(SYSTIMESTAMP, 'DD-MON-YYYY HH:MI:SS AM'));
END;
/

-- After update trigger 

CREATE OR REPLACE TRIGGER student_update_trigger
AFTER UPDATE ON student
FOR EACH ROW
BEGIN
    IF :OLD.name <> :NEW.name THEN
        INSERT INTO student_update_info (id, old_name, new_name, update_date)
        VALUES (:OLD.id, :OLD.name, :NEW.name, TO_CHAR(SYSTIMESTAMP, 'DD-MON-YYYY HH:MI:SS AM'));
    END IF;
END;
/

-- AFTER DELETE Trigger

CREATE OR REPLACE TRIGGER after_delete_trigger
AFTER DELETE ON student
FOR EACH ROW
BEGIN
    -- Code to be executed after delete
    -- For example, logging the deletion
    INSERT INTO student_info (log_info, time_stamp) VALUES ('Row deleted', TO_CHAR(SYSTIMESTAMP, 'DD-MON-YYYY HH:MI:SS AM'));
END;
/

                                       

Load data from oracle db to csv or excel sheet

 declare
csv_fh  utl_file.file_type;
begin
    csv_fh := utl_file.fopen('/tmp', 'data_export.csv', 'W');
    for r in   ( select id,name from mydb) loop
    utl_file.put_line(csv_fh, r.id||'|'||r.name);
    end loop;
    utl_file.fclose(csv_fh);
end;  

 

CREATE DIRECTORY test_dir AS '/var/opt/oracle/lstest';

-- CREATE DIRECTORY test_dir AS '/tmp';

DECLARE
  fileHandler UTL_FILE.FILE_TYPE;
BEGIN
  fileHandler := UTL_FILE.FOPEN('/tmp', 'test_file.txt', 'W');
  UTL_FILE.PUTF(fileHandler, 'Writing TO a file\n');
  UTL_FILE.FCLOSE(fileHandler);
EXCEPTION
  WHEN utl_file.invalid_path THEN
     raise_application_error(-20000, 'ERROR: Invalid PATH FOR file.');
END;
/

utl_file_dir                         string      /tmp

SQL> DECLARE
    fileHandler UTL_FILE.FILE_TYPE;
BEGIN
  fileHandler := UTL_FILE.FOPEN('/tmp', 'test_file.txt', 'W');
  UTL_FILE.PUTF(fileHandler, 'Writing TO a file\n');
  UTL_FILE.FCLOSE(fileHandler);
EXCEPTION
  WHEN utl_file.invalid_path THEN
     raise_application_error(-20000, 'ERROR: Invalid PATH FOR file.');
END;
/  

PL/SQL procedure successfully completed.

SQL> ls -lrt /tmp

SP2-0734: unknown command beginning "ls -lrt /t..." - rest of line ignored.

SQL> ! cat /tmp/test_file.txt

Writing TO a file

SQL> 

declare
   csv_fh  utl_file.file_type;
begin
    csv_fh := utl_file.fopen('/tmp', 'data_export.csv', 'W');
    for r in   ( select id,name from mydb) loop
    utl_file.put_line(csv_fh, r.id||'|'||r.name);
    end loop;
    utl_file.fclose(csv_fh);
end;  

/

PL/SQL procedure successfully completed.

rm command importance

 2. To delete all files with the exception of filename1 and filename2:

$ rm -v !("filename1"|"filename2") 

**********************************************************

    *(pattern-list) – matches zero or more occurrences of the specified patterns

    ?(pattern-list) – matches zero or one occurrence of the specified patterns

    +(pattern-list) – matches one or more occurrences of the specified patterns

    @(pattern-list) – matches one of the specified patterns

    !(pattern-list) – matches anything except one of the given patterns

*************************************************************

    * – matches zero or more characters

    ? – matches any single character

    [seq] – matches any character in seq

    [!seq] – matches any character not in seq

****************************************************************

3. The example below shows how to remove all files other than all .zip files interactively:

$ rm -i !(*.zip)

=====================================================================

4. Next, you can delete all files in a directory apart from all .zip and .odt files as follows, while displaying what is being done:

$ rm -v !(*.zip|*.odt)

=========================================================================

. Using a pipeline and xargs, you can modify the case above as follows:

$ find . -type f -not -name '*gz' -print0 | xargs -0  -I {} rm -v {}

**********************************************************

diag_alert_500.log

you can do = rm diag_alert_*.log

********************************************************

--------------------------------------### Mostly successful command ###-------------------------------

find /path/to/folder -mtime +90 -exec rm {} +

find /path/to/folder -mtime +90 -delete

find /path/to/folder -mtime +90 -print

Restore Point in Oracle 19c

 RESTORE POINT 

Note:- Before creating GRP make sure below 

1) db should be in archive log mode and flashback must be on

>> beacuse of fra get full if restore point not drop

A restore point is a name assigned to a system change number (SCN) in Oracle.

An SCN is a number that uniquely identifies each new change to an Oracle database; 

this number is incremented whenever users commit a new transaction to the database.

**************************************************************************

 To Enable Flashback Database 

shutdown immediate;

startup nomount;

alter database mount;

archive log list;

alter database archivelog;

change FRA size and then dest :-

alter system set db_flashback_retention_target=2880; ----> 2880 minutes 

alter system set db_recovery_file_dest_size=10G;

alter system set db_recovery_file_dest='/path/to/FRA';

alter system set undo_retention=86400; -----------> 86400 seconds

alter database flashback on;

alter database open;

if not open then do "alter database open resetlogs" or "alter database open noresetlogs" 

SQL >

select name, value from gv$parameter where name in ('db_flashback_retention_target','db_recovery_file_dest_size','db_recovery_file_dest','undo_retention');

To Check Flashback on/off :-

select flashback_on from gv$database;

***********************************************************

SQL> CREATE RESTORE POINT before_upgrade;

Restore point created.

====================================================================================

Guarantee Restore Point:

--------------------------

SQL> CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

Restore point created.

==========================================================================================

List out restore points:

------------------------------

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,

        GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE

        FROM V$RESTORE_POINT;

========================================================================================

Only Guarantee Restore Points:

--------------------------------------

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,

        GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE

        FROM V$RESTORE_POINT

      WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

=========================================================================================

Droping Restore Points:

-------------------------------

SQL> DROP RESTORE POINT before_app_upgrade;

Restore point dropped.

==========================================================================================

5.2.5 Monitoring Space Usage For Guaranteed Restore Points

When guaranteed restore points are defined on your database,

 you should monitor the amount of space used in your 

flash recovery area for files required to meet the guarantee. 

Use the query for viewing guaranteed restore points in

 "Listing Restore Points" and refer to the STORAGE_SIZE 

column to determine the space required for files related to 

each guaranteed restore point. To see the total usage of your

 flash recovery area, use the query provided in 

"Using

 V$RECOVERY_FILE_DEST and V$FLASH_RECOVERY_AREA_USAGE".

======================================================================================

 SQL>  select name,FLASHBACK_ON,open_mode from v$database;

NAME      FLASHBACK_ON     OPEN_MODE

--------- ------------     --------------------------

TECHNO_DB  NO              READ WRITE

============================================================================

 SQL> alter database flashback on;

Database altered.

SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON

------------------

YES

Check for Fast Recovery Area size and location

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------

db_recovery_file_dest                string      +FLASH

db_recovery_file_dest_size           big integer 100G

SQL>

Check for any restore point using v$restore_point view

set lines 200

col TIME for a40

col NAME for a40

select inst_id,NAME,TIME,GUARANTEE_FLASHBACK_DATABASE,storage_size/1024/1024/1024 from v$restore_point;

no rows selected

Create actual restore point using CREATE RESTORE POINT command

SQL> create restore point RESTORE_POINT_20FEB2019 guarantee flashback database;

Restore point created.

Verify if restore point is created

set lines 200

col TIME for a40

col NAME for a40

select NAME,TIME,GUARANTEE_FLASHBACK_DATABASE,storage_size/1024/1024/1024 from v$restore_point;

    NAME                 TIME           GUA STORAGE_SIZE/1024/1024/1024

-------------         ------------ -------------------------- --- ----------------

RESTORE_POINT_20FEB2019 20-FEB-19 06.20.40.000000000 AM    YES        2

DROP Restore Point

Drop restore point can be performed using DROP RESTORE POINT command and we need turn off flashback_on option

SQL> DROP RESTORE POINT RESTORE_POINT_20FEB2019;

Restore point dropped.

SQL> alter database flashback on;

Database altered.

DAILY SHELL SCRIPTS

 

if count of files > 1lac then use below command to delete them from diag/trace

cd ~trace 

for i in $(ls -lrt | head -n 400000 | awk '{for(i=6;i<=NF;i++) printf("%s ",$i); printf("\n");}' | awk '{print $4}'); do rm -f  $i; done;

touch freethespace.sh

chmod 777 freethespace.sh

vim freethespace.sh

------------------------------------

#!/bin/bash

echo "Enter year:"

read year

echo "Enter month:"

read month

echo "Enter day:"

read day

rm -rf ${year}_${month}_${day}_*

#rm -rf $(date --date='1 days ago' '+%Y_%m_%d')*g

================================================

To check file exists in current directory :

------------------------------------------------

#!/bin/bash

echo "Enter your file to be search:"

read file

if test -f $file 

then

echo "file does exists !"

else

echo "no such file found !"

fi

===============================================

ls -lrt | awk '{for(i=6;i<=NF;i++) printf("%s ",$i); printf("\n");}' | grep "Oct 18" | awk '{for(j=4;j<=NF;j++) printf("%s ",$j); printf("\n");}'

redo_551326936_2_657440.arc

redo_551326936_2_657441.arc

redo_551326936_1_758270.arc

redo_551326936_1_758271.arc

redo_551326936_1_758272.arc

redo_551326936_2_657442.arc

redo_551326936_1_758273.arc

redo_551326936_1_758274.arc

redo_551326936_1_758275.arc

redo_551326936_2_657443.arc

redo_551326936_1_758276.arc

redo_551326936_1_758277.arc

redo_551326936_2_657444.arc

redo_551326936_1_758278.arc

redo_551326936_1_758279.arc

ls -lrt | awk '{for(i=6;i<=NF;i++) printf("%s ",$i); printf("\n");}' | grep "Dec 22" | awk '{for(j=4;j<=NF;j++) printf("%s ",$j); printf("\n");}' | while read i; 

do rm -rf  $i ; done 

or 

ls -lrt *.arc | awk '{for(i=6;i<=NF;i++) printf("%s ",$i); printf("\n");}' | grep "Dec 23" | awk '{print $4}' | while read i; do rm -rf $i; done 

ls -lrt | head -n 500 | awk '{for(i=6;i<=NF;i++) printf("%s ",$i); printf("\n");}' | awk '{print $4}' | while read n; do rm -rf $n; done 

IPADDRESS DETAILS

There are three ranges of IPv4 addresses reserved for private use:

10.0.0.0/8: This range includes all IP addresses from 10.0.0.0 to 10.255.255.255, 

allowing for over 16 million unique addresses. It is commonly used in larger networks, 

such as corporate intranets.

172.16.0.0/12: This range includes all IP addresses from 172.16.0.0 to 172.31.255.255, 

providing around 1 million unique addresses. It is often used by medium-sized organizations 

or for network segmentation within larger networks.

192.168.0.0/16: This range includes all IP addresses from 192.168.0.0 to 192.168.255.255, 

allowing for over 65,000 unique addresses. It is widely used in home networks and small to 

medium-sized businesses due to its simplicity and ease of use.

IP addresses are identifiers for devices in computer networks.

IPv4 addresses are 32-bit numerical addresses written in four decimal numbers separated by periods.

IPv6 addresses are 128-bit hexadecimal addresses written in eight groups of four hexadecimal digits separated by colons.

Public IP addresses are globally unique and directly accessible from the internet.

Private IP addresses are used within private networks like LANs and intranets and are not directly accessible from the internet.

Private IP address ranges include 10.0.0.0/8, 172.16.0.0/12, and 192.168.0.0/16, defined in RFC 1918.

Static IP addresses are manually configured and remain constant over time.

Dynamic IP addresses are automatically assigned by DHCP servers and may change over time.

Reserved IP addresses are set aside for specific purposes such as multicast, loopback, or broadcast communications.

Golden gate 19c all in one approach

 

GGSCI> info extract extract_name, showch ;

copy the SCN number from above output. 

select name, thread#, sequence#, status, first_time, next_time, first_change#, next_change# from  v$archived_log where 25772732953522 between first_change# and next_change#;

select name, thread#, sequence#, status, first_time, next_time, first_change#, next_change# from  v$archived_log where <scn> between first_change# and next_change#;

Path/to/archivelog/1_8322558_956503891.arc

Linux

systemctl start service_name

systemctl stop service_name

systemctl enable service_name

systemctl disable service_name

systemctl status service_name

sudo chkconfig service_name on

sudo chkconfig service_name off

sudo chkconfig service_name status

bash --noprofile --norc starts a completely clean, bare-bones Bash shell without running any of your normal startup scripts.

---

What it does

When you start Bash normally, it loads several configuration files, such as:

• /etc/profile → system-wide environment variables

• ~/.bash_profile, ~/.bash_login, ~/.profile → user login scripts

• ~/.bashrc → aliases, functions, prompt settings, etc.

These can set your PATH, prompt, aliases, etc.
Sometimes you don’t want that — especially when testing or running untrusted scripts.

The flags:

• --noprofile → skip /etc/profile and any personal profile files.

• --norc → skip reading ~/.bashrc.

Errors while running "yum update or dnf update" :- Errors during downloading metadata for repository 'pgdg-common':

 PostgreSQL common RPMs for RHEL / Rocky 8 - x86_64                                                                          353  B/s | 659  B     00:01

PostgreSQL common RPMs for RHEL / Rocky 8 - x86_64                                                                          0.0  B/s |   0  B     00:00

Errors during downloading metadata for repository 'pgdg-common':

  - Curl error (37): Couldn't read a file:// file for file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG [Couldn't open file /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG]

Error: Failed to retrieve GPG key for repo 'pgdg-common': Curl error (37): Couldn't read a file:// file for file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG [Couldn't open file /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG]

[oracle@pm ~]$ cd /etc/yum.repos.d/

sudo rm pg* 

-- Note :- just remove the package which having error as above and then you're good to go --

# mkdir /var/lib/rpm/backup
# cp -a /var/lib/rpm/__db* /var/lib/rpm/backup/
# rm -f /var/lib/rpm/__db.[0-9][0-9]*
# rpm --quiet -qa
# rpm --rebuilddb
# yum clean all

Triggers in oracle

 -- Creating a table for demonstration purposes

CREATE TABLE your_table (

    id NUMBER PRIMARY KEY,

    data VARCHAR2(50),

    last_updated TIMESTAMP

);

-- BEFORE INSERT Trigger

CREATE OR REPLACE TRIGGER before_insert_trigger

BEFORE INSERT ON your_table

FOR EACH ROW

BEGIN

    -- Code to be executed before insert

    -- For example, setting a default value

    :NEW.last_updated := SYSTIMESTAMP;

END;

/

-- AFTER INSERT Trigger

CREATE OR REPLACE TRIGGER after_insert_trigger

AFTER INSERT ON your_table

FOR EACH ROW

BEGIN

    -- Code to be executed after insert

    -- For example, logging the insertion

    INSERT INTO log_table (log_message) VALUES ('New row inserted');

END;

/

-- BEFORE UPDATE Trigger

CREATE OR REPLACE TRIGGER before_update_trigger

BEFORE UPDATE ON your_table

FOR EACH ROW

BEGIN

    -- Code to be executed before update

    -- For example, preventing updates under certain conditions

    IF :NEW.data = 'restricted' THEN

        RAISE_APPLICATION_ERROR(-20001, 'Updates to "restricted" data are not allowed.');

    END IF;

END;

/

-- AFTER UPDATE Trigger

CREATE OR REPLACE TRIGGER after_update_trigger

AFTER UPDATE ON your_table

FOR EACH ROW

BEGIN

    -- Code to be executed after update

    -- For example, updating related records in another table

    UPDATE related_table

    SET related_data = :NEW.data

    WHERE related_id = :NEW.id;

END;

/

-- BEFORE DELETE Trigger

CREATE OR REPLACE TRIGGER before_delete_trigger

BEFORE DELETE ON your_table

FOR EACH ROW

BEGIN

    -- Code to be executed before delete

    -- For example, preventing deletion of specific records

    IF :OLD.data = 'protected' THEN

        RAISE_APPLICATION_ERROR(-20002, 'Deletion of "protected" records is not allowed.');

    END IF;

END;

/

-- AFTER DELETE Trigger

CREATE OR REPLACE TRIGGER after_delete_trigger

AFTER DELETE ON your_table

FOR EACH ROW

BEGIN

    -- Code to be executed after delete

    -- For example, logging the deletion

    INSERT INTO log_table (log_message) VALUES ('Row deleted');

END;

/

GRANT & REVOKE Commands for the new_user on Oracle 19c Database ?

---------- Grant Commands ------------

GRANT CREATE CLUSTER, CREATE ANY CLUSTER, ALTER ANY CLUSTER, DROP ANY CLUSTER TO &new_user;
GRANT ALTER DATABASE, ALTER SYSTEM, AUDIT SYSTEM TO &new_user;
GRANT CREATE ANY INDEX, ALTER ANY INDEX, DROP ANY INDEX TO &new_user;
GRANT CREATE PROFILE, ALTER PROFILE, DROP PROFILE TO &new_user;
GRANT CREATE ROLE, ALTER ANY ROLE, DROP ANY ROLE, GRANT ANY ROLE TO &new_user;
GRANT CREATE ROLLBACK SEGMENT, ALTER ROLLBACK SEGMENT, DROP ROLLBACK SEGMENT TO &new_user;
GRANT CREATE USER, ALTER USER, DROP USER, CREATE SESSION TO &new_user;
GRANT CREATE VIEW, CREATE ANY VIEW, DROP ANY VIEW TO &new_user;
GRANT CREATE SYNONYM, CREATE ANY SYNONYM, CREATE PUBLIC SYNONYM, DROP ANY SYNONYM, DROP PUBLIC SYNONYM TO &new_user;
GRANT CREATE SESSION, ALTER SESSION, RESTRICTED SESSION, ALTER RESOURCE COST TO &new_user;
GRANT CREATE TABLE, CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE, SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE, LOCK ANY TABLE TO &new_user;
GRANT CREATE TABLESPACE, ALTER TABLESPACE, DROP TABLESPACE, MANAGE TABLESPACE TO &new_user;

----- Revoke Commands -----

REVOKE CREATE CLUSTER, CREATE ANY CLUSTER, ALTER ANY CLUSTER, DROP ANY CLUSTER FROM &new_user;

REVOKE ALTER DATABASE, ALTER SYSTEM, AUDIT SYSTEM FROM &new_user;

REVOKE CREATE ANY INDEX, ALTER ANY INDEX, DROP ANY INDEX FROM &new_user;

REVOKE CREATE PROFILE, ALTER PROFILE, DROP PROFILE FROM &new_user;

REVOKE CREATE ROLE, ALTER ANY ROLE, DROP ANY ROLE, GRANT ANY ROLE FROM &new_user;

REVOKE CREATE ROLLBACK SEGMENT, ALTER ROLLBACK SEGMENT, DROP ROLLBACK SEGMENT FROM &new_user;

REVOKE CREATE USER, ALTER USER, DROP USER, CREATE SESSION FROM &new_user;

REVOKE CREATE VIEW, CREATE ANY VIEW, DROP ANY VIEW FROM &new_user;

REVOKE CREATE SYNONYM, CREATE ANY SYNONYM, CREATE PUBLIC SYNONYM, DROP ANY SYNONYM, DROP PUBLIC SYNONYM FROM &new_user;

REVOKE CREATE SESSION, ALTER SESSION, RESTRICTED SESSION, ALTER RESOURCE COST FROM &new_user;

REVOKE CREATE TABLE, CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE, SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE, LOCK ANY TABLE FROM &new_user;

REVOKE CREATE TABLESPACE, ALTER TABLESPACE, DROP TABLESPACE, MANAGE TABLESPACE FROM &new_user;

ALTER USER &new_user QUOTA 100M ON &tablespace;

GRANT UNLIMITED TABLESPACE TO &new_user;