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