Postgresql and Oracle regular used DB Query

SET POSTGRES (PG) ENVIRONMENT IN WINDOWS CMD

set PGHOST=10.177.0.17

set PGPORT=5432

set PGDATABASE=ABCDEF3

set PGUSER=postgres

set PGPASSWORD=^&94Y2Y]Rb}Cp?!`@

set PATH=D:\Users\MANOJ\AppData\Local\Programs\pgAdmin 4\v6\runtime;%PATH%

psql

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

DUPLICATE ROW FINDING-

select

source, source_name,count(*)

 from ABC_adminTEST.LOCKUP_source

 group by

 source, source_name

 having count(*)>1

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

ROW_COUNT_of_Table

SELECT  schemaname as table_schema, relname as table_name, n_live_tup as row_count 

FROM  pg_stat_user_tables where schemaname='ABC_agn_%';

ORACLE_ROW_COUNT-

SET SERVEROUTPUT ON;

DECLARE

val NUMBER;

BEGIN

FOR R IN (SELECT OWNER, TABLE_NAME FROM DBA_TABLES WHERE OWNER='TEST' and table_name IN ('TEST')  OD BY TABLE_NAME) LOOP

EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || R.OWNER || '.' || R.TABLE_NAME INTO val;

DBMS_OUTPUT.PUT_LINE(R.TABLE_NAME || ',' || val );

END LOOP;

END;

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

BACKUP AND RESTORE -

pg_dump DBNAME > DBNAME.sql   ---DB Backup 

pg_dump -d ABCDEF3_DML --table=PUBLIC.DATA > ABC_WORKTEST_DATA.sql 

psql  -d ABCDEF3_DML  --table ABC_WORKTEST.DATA < ABC_WORKTEST_DATA.sql 

pg_restore -d ABCDEF3_DML  -t PUBLIC.DATA  ABC_WORKTEST_DATA.sql 

psql --dbname ABCDEF3_DML -f ABC_WORKTEST_DATA.sql

pg_restore  -d "ABCDEF3_DML" -t "PUBLIC.DATA" "ABC_WORKTEST_DATA.sql"

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

DIRECTLY COPY THE TABLE FROM ONE DB TO ANOTHER 

pg_dump -t PUBLIC.LOCKUP_SOURCE_28JULYDBBKP_NEW ABCDEF3_JULY_280722 | psql  ABCDEF3

ONLY DATA WILL BE COPIED TO ANOTHER DATABASE'S TABLE.

pg_dump -a -t PUBLIC.GN_TEST_TABLE_BKP ABCDEF3_DML | psql ABCRDW3 

pg_dump -a -t table_to_copy source_db | psql target_db

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

GRANT FOR ALL the related OBJECTS-

select 'grant select, insert, update, delete on '||owner||'.'||objecT_name||' to FNATION_svc;' from dba_objects where object_type

in ('TABLE','SEQUENCE','TRIGGER') and owner='ABC_RADHETEST' and object_name like '%AFTER_%';

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

CHECK ALL OBJECTS IN ONE SCHEMA EXCEPT PACKAGE/PROC/FUNC

select 

    nsp.nspname as SchemaName

    ,cls.relname as ObjectName 

    ,rol.rolname as ObjectOwner

    ,case cls.relkind

        when 'r' then 'TABLE'

        when 'm' then 'MATERIALIZED_VIEW'

        when 'i' then 'INDEX'

        when 'S' then 'SEQUENCE'

        when 'v' then 'VIEW'

        when 'c' then 'TYPE'

        else cls.relkind::text

    end as ObjectType

from pg_class cls

join pg_roles rol 

on rol.oid = cls.relowner

join pg_namespace nsp 

on nsp.oid = cls.relnamespace

where nsp.nspname not in ('information_schema', 'pg_catalog')

    and nsp.nspname not like 'pg_toast%'

    and rol.rolname = 'postgres'  

OD by nsp.nspname, cls.relname

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

TABLE_ROW_DIFFIRENCE AND MERGE THE DATA-

insert into public.LOCKUP_source_orig_merge (id, source, source_name, audit_info)

select  * from  public.LOCKUP_source_28julydbbkp_new

where id not in

   (select id from ABC_adminTEST."source")

   

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

CREATE TABLE public.LOCKUP_source_ORIG_MERGE AS SELECT * FROM  ABC_adminTEST."LOCKUP_source"

WHERE 1=1;

CREATE TABLE MANOJ.PT_payment_method_bkp1 AS SELECT * FROM MANOJ.PT_payment_method

WHERE 1=2;

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

DIRECTLY COPY THE TABLE FROM ONE POSTGRES DB TO ANOTHER DB 

pg_dump  -t PUBLIC.G_ENTITY_FIELD ABCDEF3_DML | psql  ABCDEF3

ONLY DATA WILL BE COPIED TO ANOTHER DATABASE'S TABLE.

pg_dump -a -t PUBLIC.GN_TEST_TABLE_BKP ABCDEF3_DML | psql ABCRDW3 

pg_dump -a -t table_to_copy source_db | psql target_db

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

TABLE_SEQUENCE_GRANT

select 'grant select, insert, update, delete on '||owner||'.'||objecT_name||' to FNATION_svc;' from dba_objects where object_type

in ('TABLE','SEQUENCE') and owner='ABC_ADMINTEST' and object_name like '%TEST_%';

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

FOR TABLE and other except Triggers constraints and other-

pg_dump --section=pre-data -d ABCDEF3 | findstr "GRANT"

for triggers, etc you'll need --section=post-data

10:15

"Post-data items include definitions of indexes, triggers, rules, and constraints other than validated check constraints. Pre-data items include all other data definition items."

Manoj Mishra  10:37 PM

ok for trigg-

pg_dump --section=post-data -d ABCDEF3_JULY_280722 | findstr "GRANT"

===========================PG-DDL-PACKAGE-FUNCTION====================================================================================

SELECT pg_get_functiondef(f.oid) as func

FROM pg_catalog.pg_proc f

INNER JOIN pg_catalog.pg_namespace n ON (f.pronamespace = n.oid)

left outer join pg_aggregate pa on pa.aggfnoid=f.oid

WHERE

pa.aggfnoid is null

and f.prokind in ('p','f') -- = 'f'

and n.nspname = 'MANOJ'

and pg_get_functiondef(f.oid) like '%_pkg_%';

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

ORACLE_TIMEZONE_SETTING

alter session set nls_timestamp_format = "DD-MON-YYYY HH:MI:SS.FF6 PM";

ORACLE_QUERY_FOR_GRANT-

select 'grant select, insert, update, delete on '||owner||'.'||objecT_name||' to FNATION_svc;' from dba_objects where object_type in ('TABLE','VIEW','PACKAGE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE', 'SYNONYM') and owner='ABC_ADMINTEST' and object_name='TEAM_';

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

ORACLE_COLUMN-

select table_name, column_name, data_type, data_length from all_tab_columns where  TABLE_NAME='DURGA' AND column_name in ('B','STATUS');

PG_COLUMN-

SELECT COLUMN_NAME FROM information_schema.columns  where table_schema = 'MANOJ' AND table_name = 'state';

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

TO_FOREIGN_KEY ON TABLE-

 SELECT conrelid::regclass AS table_name, 

       conname AS foreign_key, 

       pg_get_constraintdef(oid) 

FROM   pg_constraint 

WHERE  contype = 'f' 

AND    connamespace = 'TEST'::regnamespace   

OD  BY conrelid::regclass::text, contype DESC;

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

OBJECTS IN .SQL FILE-

select 

'psql -tA -f gen_func.sql -v fn='||n.nspname||'.'||p.proname||' > '||n.nspname||'.'||p.proname||'.sql'

--n.nspname

--,p.proname

--pg_get_functiondef(p.oid)||chr(10)||'/'||chr(10)

from pg_proc p

JOIN pg_namespace n on n.oid = p.pronamespace

where

n.nspname='MANOJ'

and p.prokind in ('f','p')

and p.proname not like 'trigger%'

;

select routine_schema

,routine_name 

--,r.*

from 

information_schema.routines r

where 

routine_schema in 

(

'MANOJ'

--,'datasuppt'

--,'entitymgr'

--,'intmgr'

----,'oracle'

----,'public'

--,'refmgr'

--,'sammgr'

--,'usermgr'

)

and routine_name not like 'trigger%'

OD by 1,2

select 

--n.nspname

--,p.proname

pg_get_functiondef(p.oid)||chr(10)||'/'||chr(10)

from pg_proc p

JOIN pg_namespace n on n.oid = p.pronamespace

where

p.prokind in ('f','p')

and (nspname||'.'||proname)::text = :'fn'

;

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

GET .SQL FOR FUNCTION

psql -tA -f gen_func.sql -v fn=MANOJ.acl_alternate_ndc_pkg_addalternatendc >MANOJ.acl_alternate_ndc_pkg_addalternatendc.sql

psql -tA -f gen_func.sql -v fn=MANOJ.acl_alternate_ndc_pkg_deletealternatendc >MANOJ.acl_alternate_ndc_pkg_deletealternatendc.sql

psql -tA -f gen_func.sql -v fn=MANOJ.acl_assistrx_pkg_addnodesforactionconversions >MANOJ.acl_assistrx_pkg_addnodesforactionconversions.sql

psql -tA -f gen_func.sql -v fn=MANOJ.acl_cc_detail_pkg_insertccdetail >MANOJ.acl_cc_detail_pkg_insertccdetail.sql

psql -tA -f gen_func.sql -v fn=MANOJ.acl_clinical_action_pkg_biogen_fdp_insert_insurance >MANOJ.acl_clinical_action_pkg_biogen_fdp_insert_insurance.sql

psql -tA -f gen_func.sql -v fn=MANOJ.acl_clinical_action_pkg_update_biogen_fdp_pap_enddate >MANOJ.acl_clinical_action_pkg_update_biogen_fdp_pap_enddate.sql

psql -tA -f gen_func.sql -v fn=MANOJ.AC_CLpkg_autoenrolluser >MANOJ.AC_CLpkg_autoenrolluser.sql

psql -tA -f gen_func.sql -v fn=MANOJ.AC_CLpkg_insertCLBRcord >MANOJ.AC_CLpkg_insertCLBRcord.sql

psql -tA -f gen_func.sql -v fn=MANOJ.AC_CLpkg_insertPINCODElocation >MANOJ.AC_CLpkg_insertPINCODElocation.sql

psql -tA -f gen_func.sql -v fn=MANOJ.AC_CLpkg_INRC >MANOJ.AC_CLpkg_INRC.sql

psql -tA -f gen_func.sql -v fn=MANOJ.AC_CLpkg_insertPTassociation >MANOJ.AC_CLpkg_insertPTassociation.sql

psql -tA -f gen_func.sql -v fn=MANOJ.acl_PINCODE_method_pkg_addPTPINCODEmethodconsent >MANOJ.acl_PINCODE_method_pkg_addPTPINCODEmethodconsent.sql

psql -tA -f gen_func.sql -v fn=MANOJ.acl_PINCODE_method_pkg_updatedefaultPINCODEmethod >MANOJ.acl_PINCODE_method_pkg_updatedefaultPINCODEmethod.sql

psql -tA -f gen_func.sql -v fn=MANOJ.acl_PINCODE_method_pkg_updatePTPINCODEmethodconsent >MANOJ.acl_PINCODE_method_pkg_updatePTPINCODEmethodconsent.sql

psql -tA -f gen_func.sql -v fn=MANOJ.acl_CRP_pkg_insertCRP >MANOJ.acl_CRP_pkg_insertCRP.sql

psql -tA -f gen_func.sql -v fn=MANOJ.acl_CRP_pkg_updateCRP >MANOJ.acl_CRP_pkg_updateCRP.sql

psql -tA -f gen_func.sql -v fn=MANOJ.acl_CRP_pkg_updaterecord >MANOJ.acl_CRP_pkg_updaterecord.sql

psql -tA -f gen_func.sql -v fn=MANOJ.acl_create_OPCprescription_pkg_calculateDURGAtax >MANOJ.acl_create_OPCprescription_pkg_calculateDURGAtax.sql

psql -tA -f gen_func.sql -v fn=MANOJ.acl_create_OPCprescription_pkg_calculaterevisedreODdate >MANOJ.acl_create_OPCprescription_pkg_calculaterevisedreODdate.sql

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

PARANT CHILD RELATIONSHIP TABLE- 

select 

fk.* 

--,row_number() over(partition by schema_name,table_name,r_schema_name,column_list,r_column_list OD by constraint_name) rn

from 

(

select 

c.conname constraint_name

,(select nspname from pg_namespace where oid=c.connamespace) "schema_name"

,(select relname from pg_class where oid=c.conrelid) table_name

--,pg_get_constraintdef(oid)

,substring(pg_get_constraintdef(oid),'FOREIGN KEY \((.+)\) REFERENCES') column_list

,(select n.nspname from pg_class cl join pg_namespace n on n.oid=cl.relnamespace where cl.oid=c.confrelid) r_schema_name

,(select relname from pg_class where oid=c.confrelid) r_table_name

,substring(pg_get_constraintdef(oid),'REFERENCES .+\((.+)\)') r_column_list

from

pg_constraint c

where

c.contype='f'

) fk

where

schema_name in ('MANOJ')

--and table_name in ('award','idv','place_of_performance')

OD by

schema_name

,table_name

--,constraint_name

,r_schema_name

,column_list

,r_column_list

,constraint_name

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

select kcu.table_schema || '.' ||kcu.table_name as foreign_table,

       '>-' as rel,

       rel_tco.table_schema || '.' || rel_tco.table_name as primary_table,

       string_agg(kcu.column_name, ', ') as fk_columns,

       kcu.constraint_name

from information_schema.table_constraints tco

join information_schema.key_column_usage kcu

          on tco.constraint_schema = kcu.constraint_schema

          and tco.constraint_name = kcu.constraint_name

join information_schema.referential_constraints rco

          on tco.constraint_schema = rco.constraint_schema

          and tco.constraint_name = rco.constraint_name

join information_schema.table_constraints rel_tco

          on rco.unique_constraint_schema = rel_tco.constraint_schema

          and rco.unique_constraint_name = rel_tco.constraint_name

where tco.constraint_type = 'FOREIGN KEY'

group by kcu.table_schema,

         kcu.table_name,

         rel_tco.table_name,

         rel_tco.table_schema,

         kcu.constraint_name

OD by kcu.table_schema,

         kcu.table_name;

 

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

CHECK_COLUMN_DATA_TYPE  

 

 SELECT * FROM  information_schema.columns WHERE DATA_TYPE like '%numeric%' 

and table_schema like '%ABC_%'

and columns.numeric_precision=19 and columns.numeric_scale not in(10) OD by table_schema,column_name ;

 

 

 SELECT * FROM  information_schema.columns WHERE DATA_TYPE like '%numeric%' 

and table_schema like '%ABC_%'

and columns.numeric_precision=19 and columns.numeric_scale not in(10) OD by table_schema,column_name ;

 

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

TO_REMOVE_FOREIGN_KEY COLUMN ON TABLE-

Here is a function for removing all FKs on a column (yes, PG for some 

reason allows multiple similar FKs on a column):

create or replace function remove_fk_by_table_and_column(p_table_name 

varchar, p_column_name varchar) returns INTEGER as $$

declare

     v_fk_name varchar := NULL;

     v_fk_num_removed INTEGER := 0;

begin

     FOR v_fk_name IN (SELECT ss2.conname

         FROM pg_attribute af, pg_attribute a,

             (SELECT conname, conrelid,confrelid,conkey[i] AS conkey, 

confkey[i] AS confkey

                 FROM (SELECT conname, conrelid,confrelid,conkey,confkey,

                     generate_series(1,array_upper(conkey,1)) AS i

                     FROM pg_constraint WHERE contype = 'f') ss) ss2

         WHERE af.attnum = confkey

             AND af.attrelid = confrelid

             AND a.attnum = conkey

             AND a.attrelid = conrelid

             AND a.attrelid = p_table_name::regclass

             AND a.attname = p_column_name) LOOP

         execute 'alter table ' || quote_ident(p_table_name) || ' drop 

constraint ' || quote_ident(v_fk_name);

         v_fk_num_removed = v_fk_num_removed + 1;

     END LOOP;

     return v_fk_num_removed;

end;

$$ language plpgsql;

Usage:

select remove_fk_by_table_and_column('my_table', 'some_column');

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

TO_REMOVE_FOREIGN_KEY ON ONE TABLE-

ALTER TABLE  USER.TABLE_NAME DROP CONSTRAINT FO_KEY_NAME;

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

CHARACTOR SET IN ORACLE

select * from nls_database_parameters where parameter in ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

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

‘associated_ponumber ‘ missing in ABC_INVTR.INV_ODHEADER

CHECK_COLUMN

SELECT * 

FROM information_schema.columns 

WHERE table_name='DURGA_inv'

and column_name like '%is_velocity_calculation_excluded%';

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

BLOCKING_SESSION_PG

CHECK_ALL_SESSION

select pid as process_id, 

       usename as username, 

       datname as database_name, 

       client_addr as client_address, 

       application_name,

       backend_start,

       state,

       state_change

from pg_stat_activity where state='idle';

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

KILL_SESSION

Select pg_terminate_backend (2997732)

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

CREATE_VIEW_OF_BLOCKING_SESSION

create or replace view ACTIVE_SESSION as  

select pid as process_id, 

       usename as username, 

       datname as database_name, 

       client_addr as client_address, 

       application_name,

       backend_start,

       state,

       state_change

from pg_stat_activity where state='active';

SELECT pg_terminate_backend(pid)

FROM pg_stat_activity

WHERE datname = 'Database_Name'

AND pid <> pg_backend_pid()

AND state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled') 

AND state_change < current_timestamp - INTERVAL '60' MINUTE ;

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

CHECK_SESSION_ACTIVITY

select * from pg_stat_activity where pid=(2982288)

({2982288,2982485,2982194,2967741})

CHEK_BLOCKING_sESSION

select pg_blocking_pids(pid) as blocked_by

from pg_stat_activity

where cardinality(pg_blocking_pids(pid)) > 0;

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

select pid as process_id, 

       usename as username, 

       datname as database_name, 

       client_addr as client_address, 

       application_name,

       backend_start,

       state,

       state_change

from pg_stat_activity where state='active';

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

KILL_SESSION

Select pg_terminate_backend (2979162)

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

DDL for ORACLE OBJECTS

SELECT DBMS_METADATA.GET_DDL('&OBJECT_TYPE','&OBJECT_NAME','&OWNER') from dual;

ROLES and GRANTS-

select dbms_metadata.get_granted_ddl('OBJECT_GRANT','ABC_ADMINTEST') from dual;

select dbms_metadata.get_granted_ddl('ROLE_GRANT','ABC_ADMINTEST') from dual;

select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','ABC_ADMINTEST') from dual;

===========

SELECT

    tc.table_schema, 

    tc.constraint_name, 

    tc.table_name, 

    kcu.column_name, 

    ccu.table_schema AS foreign_table_schema,

    ccu.table_name AS foreign_table_name,

    ccu.column_name AS foreign_column_name 

FROM 

    information_schema.table_constraints AS tc 

    JOIN information_schema.key_column_usage AS kcu

      ON tc.constraint_name = kcu.constraint_name

      AND tc.table_schema = kcu.table_schema

    JOIN information_schema.constraint_column_usage AS ccu

      ON ccu.constraint_name = tc.constraint_name

      AND ccu.table_schema = tc.table_schema

WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_name='mytable';

==================CHECK_DEPEDENT_OBJECTS_VIEW=================

SELECT dependent_ns.nspname as dependent_schema

, dependent_view.relname as dependent_view

, source_ns.nspname as source_schema

, source_table.relname as source_table

, pg_attribute.attname as column_name

FROM pg_depend

JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid

JOIN pg_class as dependent_view ON pg_rewrite.ev_class = dependent_view.oid

JOIN pg_class as source_table ON pg_depend.refobjid = source_table.oid

JOIN pg_attribute ON pg_depend.refobjid = pg_attribute.attrelid

    AND pg_depend.refobjsubid = pg_attribute.attnum

JOIN pg_namespace dependent_ns ON dependent_ns.oid = dependent_view.relnamespace

JOIN pg_namespace source_ns ON source_ns.oid = source_table.relnamespace

WHERE

source_ns.nspname = 'MANOJ'

AND source_table.relname = 'DURGA'

AND pg_attribute.attnum > 0

AND pg_attribute.attname = 'ext_dispense_uom_qty_factor'

OD BY 1,2;

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

CONVERT charactor varrying TO BOOLEAN

select count(*) from ABC_WORKTEST.SCOPE WHERE upper(NAME) = upper('DeanNumber') 

ALTER TABLE MANOJ.PT_PHONE  

    ALTER COLUMN MAX_UNITS_PER_COMPARTMENT DROP DEFAULT,

    ALTER COLUMN MAX_UNITS_PER_COMPARTMENT TYPE BOOLEAN USING PREFERRED::BOOLEAN,

    ALTER COLUMN MAX_UNITS_PER_COMPARTMENT SET DEFAULT FALSE;

SELECT MAX_UNITS_PER_COMPARTMENT FROM TESTTEST.AVDH_PKG_SYSTEM

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

CONVERT INTEGER TO BOOLEAN

ABC_WORKTEST.WORK_STAGE 

ALTER TABLE TESTTEST.AVDH_PKG_SYSTEM

  ALTER MAX_UNITS_PER_COMPARTMENT  DROP DEFAULT

 ,ALTER MAX_UNITS_PER_COMPARTMENT  TYPE bool USING (MAX_UNITS_PER_COMPARTMENT::bool::int)

 ,ALTER MAX_UNITS_PER_COMPARTMENT  SET NOT NULL

 ,ALTER MAX_UNITS_PER_COMPARTMENT  SET DEFAULT false;

 

ALTER TABLE MANOJ.PT_T

  ALTER PRG_STATUS_CD DROP DEFAULT

 ,ALTER PRG_STATUS_CD TYPE int USING (PRG_STATUS_CD::int :: numeric(10,   0))

 ,ALTER PRG_STATUS_CD SET NOT NULL;

 

 ALTER TABLE MANOJ.PT_T ALTER COLUMN PRG_STATUS_CD TYPE numeric(10,   0) using  cast(PRG_STATUS_CD as numeric); 

 

 alter table presales alter column code type numeric(10,   0) using cast(code as numeric);

 alter table presales alter column code type numeric(10,   0) using code::numeric;

 

::int :: numeric(10,   0)

 

 alter table MANOJ.PT_T

alter column PRG_status_cd type integer

using case when IS_ALL_LINES_IN_STAGE_REQUIRED then 1 else 2 end

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

CONVERT_BOOLEAN_TO INTEGER

ALTER TABLE TESTTEST.AVDH_PKG_SYSTEM ALTER MAX_UNITS_PER_COMPARTMENT SET DEFAULT null;

ALTER TABLE TESTTEST.AVDH_PKG_SYSTEM

ALTER MAX_UNITS_PER_COMPARTMENT TYPE INTEGER

USING

CASE

WHEN false THEN 0 ELSE 1

END;

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

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

ALTER TABLE MANOJ.DURGA

ALTER COLUMN  compound_item_type TYPE TEXT USING  compound_item_type :: TEXT;

CHECK DEPEDENTS VIEW-=============================== 

 SELECT dependent_ns.nspname as dependent_schema

, dependent_view.relname as dependent_view

, source_ns.nspname as source_schema

, source_table.relname as source_table

, pg_attribute.attname as column_name

FROM pg_depend

JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid

JOIN pg_class as dependent_view ON pg_rewrite.ev_class = dependent_view.oid

JOIN pg_class as source_table ON pg_depend.refobjid = source_table.oid

JOIN pg_attribute ON pg_depend.refobjid = pg_attribute.attrelid

    AND pg_depend.refobjsubid = pg_attribute.attnum

JOIN pg_namespace dependent_ns ON dependent_ns.oid = dependent_view.relnamespace

JOIN pg_namespace source_ns ON source_ns.oid = source_table.relnamespace

WHERE

source_ns.nspname = 'MANOJ'

AND source_table.relname = 'PT_t'

AND pg_attribute.attnum > 0

AND pg_attribute.attname = 'PRG_status_cd'

OD BY 1,2;

CHECK_TRIGGER==============================

select * from information_schema.triggers WHERE TRIGGER_SCHEMA like '%RADHE%'

SET SERVEROUTPUT ON;

DECLARE

val NUMBER;

BEGIN

FOR R IN (SELECT OWNER, TABLE_NAME FROM DBA_TABLES WHERE OWNER='TEST' and table_name IN ('FAX_HISTORY_LINE',',OPCDOCUMENT_DETAIL','FILL_AVDH','OPCDOCUMENT','REFERRAL_NOTIFICATION_REQUEST','FILL_AVDH_DOSE','FILL_AVDH_FREQUENCY_ATTRIBUTE','FAX_CRP_XREF','ANSI_837_ATTACHMENT_FAX','BUNDLE_FAX','CENTRALIZED_NOTE','DEPS_SAVED_DDL','FAX_NOTE','FAX_HEADER_SCOPES','FAX_HISTORY_SCOPES')  OD BY TABLE_NAME) LOOP

EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || R.OWNER || '.' || R.TABLE_NAME INTO val;

DBMS_OUTPUT.PUT_LINE(R.TABLE_NAME || ',' || val );

END LOOP;

END;

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

HIDDEN PARAMETER IN ORACLE -

SELECT x.ksppinm "Parameter",

       Y.ksppstvl "Session Value",

       Z.ksppstvl "Instance Value"

FROM   x$ksppi X,

       x$ksppcv Y,

       x$ksppsv Z

WHERE  x.indx = Y.indx

AND    x.indx = z.indx

AND    x.ksppinm LIKE '/_%' escape '/' and x.ksppinm='_db_block_cache_protect'

OD by x.ksppinm;

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