export oracle environment shell script

What you can achieve ?

1. sqlplus / as sysdba

2. rman target /

3. expdp ...

4. impdp ...

5. srvctl 

Identify the Purpose of Patching

 

SQL styling and formatting !!!!!

-- Increase the size for long DDL outputs

SET LONG 10000;           -- Allows displaying long DDL statements

SET LONGCHUNKSIZE 1000;   -- Controls how LONG data is fetched and displayed

-- Formatting options

SET LINESIZE 200;         -- Adjusts output width for better readability

SET PAGESIZE 100;         -- Controls the number of lines per page (0 removes headers)

SET TRIMOUT ON;           -- Removes trailing spaces from output

SET TRIMSPOOL ON;         -- Removes trailing spaces when spooling output

SET WRAP OFF;             -- Prevents line wrapping in output

-- Improve output structure

SET COLSEP '|';           -- Adds a column separator for better visibility

SET UNDERLINE '-';        -- Adds an underline separator under column headers

-- Display settings

SET SERVEROUTPUT ON;      -- Enables DBMS_OUTPUT messages

SET VERIFY OFF;           -- Suppresses display of substitution variable values

SET FEEDBACK OFF;         -- Removes row count messages from the output

SET HEADING ON;           -- Ensures column headers are displayed

SET ECHO OFF;             -- Hides command execution in the output

-- If using spooling (saving output to a file)

SPOOL my_output.txt;

-- Your SQL queries here...

SPOOL OFF;

--For DDL Queries:

SET LONG 10000;

SET PAGESIZE 0;

SET LINESIZE 200;

SET TRIMOUT ON;

SET WRAP OFF;

--For Tabular query result

SET PAGESIZE 100;

SET LINESIZE 200;

SET COLSEP '|';

SET UNDERLINE = '-';

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

/* For exporting result to a file */

SET LONG 10000;

SET PAGESIZE 0;

SET LINESIZE 200;

SET TRIMOUT ON;

SET WRAP OFF;

SPOOL my_output.txt;

-- Your queries here...

SPOOL OFF;

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

2025-03-12 00:11:11 ERROR OGG-01169 Encountered an update where all key columns for target table SCHEMA.TABLE_NAME are not present

 To find primary key of a table:

SQL>

select cols.column_name

from dba_constraints cons

join dba_cons_columns cols

on cons.sonstraint_name = cols.constraint_name

where cons.constraint_type in ('U','P')

and cons.owner = '&SCHEMA'

and cons.table_name = '&TABLE_NAME';

to get ddl for indexes for a table on source :

SQL>

set long 5000;

set pages 0;

select dbms_metadata.get_ddl('INDEX',u.index_name,'SCHEMA') from dba_indexes u where

table_name='&table_name' ;

STEPS TO PERFORME ON TARGET DB AND SOURCE DB:

1) target gg db:

GGSCI > sh cat dirrpt/REPLICAT_NAME.rpt

2025-03-12 00:11:11 ERROR OGG-01169 Encountered an update where all key columns for

target table SCHEMA.TABLE_NAME are not present

2) GGSCI > edit param REPLICAT_NAME

...

2.1 on target db checked for primary key and unique key columns

    then take the ddl for the indexes on the table SCHEMA.TABLE_NAME on source db

2.2 create source indexes on  target

2.3 then add below lines in REPLICAT_NAME.prm

    map SCHEMA.TABLE_NAME, target SCHEMA.TABLE_NAME, keycols (column_pk,column_uk) ;

2.4 start target abended REPLICAT REPLICAT_NAME

    GGSCI > start REPLICAT_NAME

INDEX REBUILD TIME SQL

select sql_text,sql_id from gv$sqlstats where sql_text like '%SCHEMA.INDEX_NAME%';

abfxhr2v2bqgf 

select sid,serial# from gv$session_longops where sql_id='abfxhr2v2bqgf ';

SID             SERIAL#

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

2200           44444

select round(time_remaining/60) time_remaining_mins from gv$session_longops where sid='2200' and serial# = '44444';

TIME_REMAINING_MINS

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

33

DBMS_METADATA package to extract objects DDL

-----Always set below for getting full query result

set long 5000;

set pages 0;

-- 1. Get DDL for all Tables in the HR schema

SELECT DBMS_METADATA.GET_DDL('TABLE', TABLE_NAME, 'HR')

FROM ALL_TABLES

WHERE OWNER = 'HR';

-- 2. Get DDL for all Indexes in the HR schema

SELECT DBMS_METADATA.GET_DDL('INDEX', INDEX_NAME, 'HR')

FROM ALL_INDEXES

WHERE OWNER = 'HR';

-- 3. Get DDL for all Constraints in the HR schema

(Primary Key, Unique, Foreign Key, Check)

SELECT DBMS_METADATA.GET_DDL('CONSTRAINT', CONSTRAINT_NAME, 'HR')

FROM ALL_CONSTRAINTS

WHERE OWNER = 'HR' AND CONSTRAINT_TYPE IN ('P', 'U', 'R', 'C');

-- 4. Get DDL for all Sequences in the HR schema

SELECT DBMS_METADATA.GET_DDL('SEQUENCE', SEQUENCE_NAME, 'HR')

FROM ALL_SEQUENCES

WHERE SEQUENCE_OWNER = 'HR';

-- 5. Get DDL for all Views in the HR schema

SELECT DBMS_METADATA.GET_DDL('VIEW', VIEW_NAME, 'HR')

FROM ALL_VIEWS

WHERE OWNER = 'HR';

-- 6. Get DDL for all Synonyms in the HR schema

SELECT DBMS_METADATA.GET_DDL('SYNONYM', SYNONYM_NAME, 'HR')

FROM ALL_SYNONYMS

WHERE OWNER = 'HR';

-- 7. Get all Roles assigned to HR user

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'HR') FROM DUAL;

-- 8. Get all System Privileges granted to HR user

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', 'HR') FROM DUAL;

-- 9. Get all Object Privileges granted to HR user

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'HR') FROM DUAL;

-- 10. Remove storage, tablespace, and segment attributes from DDL output

BEGIN

  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE);

  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'TABLESPACE', FALSE);

  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', FALSE);

  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);

END;

/

-- DDL for Tables

SELECT DBMS_METADATA.GET_DDL('TABLE', u.table_name, 'SCHEMA')  

FROM dba_tables u  

WHERE owner = 'SCHEMA'

AND u.table_name = 'OBJECT';  

-- DDL for Indexes

SELECT DBMS_METADATA.GET_DDL('INDEX', u.index_name, 'SCHEMA')  

FROM dba_indexes u

WHERE owner = 'SCHEMA'

AND u.index_name = 'OBJECT';  

-- DDL for Views

SELECT DBMS_METADATA.GET_DDL('VIEW', u.view_name, 'SCHEMA')  

FROM dba_views u

WHERE owner = 'SCHEMA'

AND u.view_name = 'OBJECT';  

-- DDL for Sequences

SELECT DBMS_METADATA.GET_DDL('SEQUENCE', u.sequence_name, 'SCHEMA')  

FROM dba_sequences u

WHERE owner = 'SCHEMA'

AND u.sequence_name = 'OBJECT';  

-- DDL for Procedures

SELECT DBMS_METADATA.GET_DDL('PROCEDURE', u.object_name, 'SCHEMA')  

FROM dba_procedures u

WHERE owner = 'SCHEMA'  

AND object_type = 'PROCEDURE'

AND u.object_name = 'OBJECT';  

-- DDL for Functions

SELECT DBMS_METADATA.GET_DDL('FUNCTION', u.object_name, 'SCHEMA')  

FROM dba_procedures u

WHERE owner = 'SCHEMA'  

AND object_type = 'FUNCTION'

AND u.object_name = 'OBJECT';  

-- DDL for Packages

SELECT DBMS_METADATA.GET_DDL('PACKAGE', u.object_name, 'SCHEMA')  

FROM dba_procedures u

WHERE owner = 'SCHEMA'  

AND object_type = 'PACKAGE'

AND u.object_name = 'OBJECT';  

-- DDL for Triggers

SELECT DBMS_METADATA.GET_DDL('TRIGGER', u.trigger_name, 'SCHEMA')  

FROM dba_triggers u

WHERE owner = 'SCHEMA'

AND u.trigger_name = 'OBJECT';  

-- DDL for Synonyms

SELECT DBMS_METADATA.GET_DDL('SYNONYM', u.synonym_name, 'SCHEMA')  

FROM dba_synonyms u

WHERE owner = 'SCHEMA'

AND u.synonym_name = 'OBJECT';  

-- DDL for Materialized Views

SELECT DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW', u.mview_name, 'SCHEMA')  

FROM dba_mviews u

WHERE owner = 'SCHEMA'

AND u.mview_name = 'OBJECT';  

-- DDL for Constraints

SELECT DBMS_METADATA.GET_DDL('CONSTRAINT', u.constraint_name, 'SCHEMA')  

FROM dba_constraints u

WHERE owner = 'SCHEMA'

AND u.constraint_name = 'OBJECT';  

--Get DDL commands for all objects in database for a Schema --

SELECT DBMS_METADATA.GET_DDL('TABLE', table_name, 'SCHEMA') FROM

dba_tables WHERE owner = 'SCHEMA'

UNION ALL

SELECT DBMS_METADATA.GET_DDL('INDEX', index_name, 'SCHEMA') FROM

dba_indexes WHERE owner = 'SCHEMA'

UNION ALL

SELECT DBMS_METADATA.GET_DDL('VIEW', view_name, 'SCHEMA') FROM

dba_views WHERE owner = 'SCHEMA'

UNION ALL

SELECT DBMS_METADATA.GET_DDL('SEQUENCE', sequence_name, 'SCHEMA') FROM

dba_sequences WHERE owner = 'SCHEMA'

UNION ALL

SELECT DBMS_METADATA.GET_DDL('PROCEDURE', object_name, 'SCHEMA') FROM

dba_procedures WHERE owner = 'SCHEMA'

UNION ALL

SELECT DBMS_METADATA.GET_DDL('FUNCTION', object_name, 'SCHEMA') FROM

dba_procedures WHERE owner = 'SCHEMA' AND object_type = 'FUNCTION'

UNION ALL

SELECT DBMS_METADATA.GET_DDL('PACKAGE', object_name, 'SCHEMA') FROM

dba_procedures WHERE owner = 'SCHEMA' AND object_type = 'PACKAGE'

UNION ALL

SELECT DBMS_METADATA.GET_DDL('TRIGGER', trigger_name, 'SCHEMA') FROM

dba_triggers WHERE owner = 'SCHEMA'

UNION ALL

SELECT DBMS_METADATA.GET_DDL('SYNONYM', synonym_name, 'SCHEMA') FROM

dba_synonyms WHERE owner = 'SCHEMA'

UNION ALL

SELECT DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW', mview_name, 'SCHEMA')

FROM dba_mviews WHERE owner = 'SCHEMA';

DBMS_STATS.GATHER_STATS for tables schemas indexes columns

 

Action	Full Command	Description
Update statistics for a specific table	EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');	Updates statistics for the EMPLOYEES table in the HR schema.
Update statistics for a table and its indexes	EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES', cascade => TRUE);	Updates statistics for the EMPLOYEES table and all its indexes.
Update statistics for a specific index	EXEC DBMS_STATS.GATHER_INDEX_STATS('HR', 'EMP_SALARY_IDX');	Updates statistics for the EMP_SALARY_IDX index in the HR schema.
Update statistics for all tables in a schema	EXEC DBMS_STATS.GATHER_SCHEMA_STATS('HR');	Updates statistics for all tables and indexes in the HR schema.
Update statistics for the entire database	EXEC DBMS_STATS.GATHER_DATABASE_STATS;	Updates statistics for all schemas and tables in the database. (Time-consuming on large DBs)
Check last statistics update for a table	SELECT table_name, last_analyzed FROM user_tables WHERE table_name = 'EMPLOYEES';	Shows the last time statistics were gathered for the EMPLOYEES table.
Check last statistics update for an index	SELECT index_name, last_analyzed FROM user_indexes WHERE table_name = 'EMPLOYEES';	Shows the last time statistics were gathered for indexes of EMPLOYEES.
Update statistics in parallel for faster execution	EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES', degree => 4);	Runs statistics gathering in parallel for faster performance (using 4 CPU threads).
Update statistics for specific columns	EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES', columns => ('SALARY'));	Updates statistics only for the SALARY column.

---

Best Practices:

✔ Use cascade => TRUE to update both table and indexes.
✔ Use degree => 4 for faster execution on large tables.
✔ Schedule statistics updates during off-peak hours.

Would you like a PL/SQL script to automate this for multiple tables? 😊

Flush shared_pool & buffer_cache

 Manually flush  buffer cache & shared pool cache without bouncing the database:-

For Standalone Databases 

alter system flush buffer_cache;

alter system flush shared_pool;

For RAC Databases 

alter system flush buffer_cache global;

alter system flush shared_pool global;

------- only do in case of system performance issue or maintenance issue ----

Flush shared pool meaning flushing cached execution plan and sql Quries from memory.

Flush buffer cache meaning flushing cached data from memory.

Database restart which internally flush both 

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