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