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