DBA Queries

--To Identify table names across schema which matches your parameter

SELECT TABLE_NAME FROM ALL_TABLES WHERE TABLE_NAME LIKE '%ACCT%' ORDER BY TABLE_NAME;

--To Identify list of table names which has common Column Name

select table_name, column_name from all_tab_columns where

column_name like '%ACCT_ATTR_CAN_RQS_CONV_CHK_IN%'

--To identify list schema name which has common Table Name

select * from all_objects where object_type in ('TABLE','VIEW')

and object_name = 'SHDW_ACCT_ATTR';

--To list all the owner, schema, table name, column name which matchs your columns.comments parameter

--and specific schema

desc ALL_COL_COMMENTS

select * from all_COL_COMMENTS where comments like '%Convenience%' and owner = 'PNET_RPTG'

--To list all the owner, schema, table name, column name which matchs your columns.comments parameter

--and specific table name

SELECT * FROM all_col_comments WHERE table_name='AUDT_RPT' and comments like '%name%'

SELECT 'comment on column '||table_name||'.'||column_name||' is '''||comments||''';'

FROM all_col_comments

WHERE comments is not null;

--To display Database Name & Current Session User

select sys_context('userenv','db_name'), sys_context('userenv','session_user') from dual

--To identify Owner, Object type and status

select owner,object_type, count(*),status from dba_objects GROUP BY OWNER, OBJECT_TYPE,status

ORDER BY 1,2,3

--To Display username of DBA objects

select username from dba_users minus select distinct owner from dba_objects ;

--=============================================================

desc pnet_rptg.audt_rpt

--Alternate Query for DESC a table--

SELECT

column_name "Name",

nullable "Null?",

concat(concat(concat(data_type,'('),data_length),')') "Type"

FROM all_tab_columns

WHERE table_name='AUDT_RPT';

--=============================================================