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='';
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;
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 tablespace_name, bytes, file_name from dba_data_files order by tablespace_name;

USERS
----------
set pagesize 0
set echo off

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