Query - DBA views

Query - DBA views

DBA Views Queries

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

select * from dict;

select * from cat;

TABLE COLUMNS

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

select * from dba_tab_columns where table_name = 'table_name';

CONSTRAINTS

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

select table_name, constraint_name, constraint_type, status from dba_constraints where owner in('', '')

select constraint_name, r_constraint_name, constraint_type from dba_constraints where constraint_name = ''; 

select constraint_name, constraint_type from dba_constraints where r_constraint_name = '';

DATAFILES

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

select substr(file_name,1,70) from dba_data_files order by file_name;

DEPENDENCIES

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

select * from user_dependencies where referenced_name = ''

INDEXES

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

select index_name, column_name from dba_ind_columns where table_name = '' order by index_name, column_position

select index_name from dba_indexes where table_name = '';

JOBS

--------

select job, this_date, this_sec, next_date,  next_sec, interval, what,  failures, broken from dba_jobs;

OBJECTS

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

select object_name from dba_objects where status <> 'VALID';

TABLE PRIVILEGES

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

desc dba_tab_privs 

select * from dba_tab_privs where grantee = '';

select * from dba_tab_privs where table_name = '';

ROLE PRIVILEGES

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

select * from dba_role_privs where grantee='';

SYSTEM PRIVILEGES

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

select * from dba_sys_privs where grantee='';

OTHERS

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

DBA_COL_PRIVS

DBA_PRIV_AUDIT_OPTS

SEGMENTS

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

select segment_name, count(*), sum(bytes) from dba_extents where segment_name = '' group by segment_name ;

select * from dba_segments where segment_name = '';

select * from dba_segments@p1aqcis where segment_name = '';

select segment_name, count(*), sum(bytes) from dba_extents group by segment_name having count(*) > 1;

select * from dba_tables where table_name = '';

select segment_name, segment_type, bytes from dba_segments where segment_name in ('','','','','');

select sum(bytes) from dba_segments where segment_name in ('','','','','');

SELECT substr(segment_name, 1, 30) "OBJECT", max(extents) FROM dba_segments group by segment_name having max(extents) > &max_extents_greater_than;

  

SOURCE CODE

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

select owner,name,type,text from dba_source where upper(text) like '%%';

select name,type,text from dba_source where upper(text) like '%%';

select name,type,text from dba_source where upper(text) like '%%';

select substr(line,1,4) line, text from dba_source where type = 'PACKAGE BODY' and name = ''

select name,type,text from dba_source where upper(text) like '%%';

TABLES

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

space allocation for tables using computed statistics

select table_name, num_rows, avg_row_len, (num_rows * avg_row_len) from dba_tables where owner in ('','');

TABLESPACES

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

select tablespace_name, bytes, file_name from dba_data_files order by tablespace_name;

USERS

----------

set pagesize 0

set echo off

select 'ROLE '||GRANTED_ROLE from dba_role_privs where grantee='';

select 'SYSTEM PRIVILEGE '||privilege from dba_sys_privs where grantee='';

select privilege||' ON '||owner||'.'||table_name from dba_tab_privs where grantee='';

set echo on

select * from dba_users where username = '';

VIEWS

----------

select view_name from dba_views where view_name like '%%';

"Hope for the best, prepare for the worst"
-Dharmendra