------------------          DBA Views Queries          ------------------

-- Gives you a list of all the views available in the dictionary
select * from dict;
select * from cat;

------------------COLUMNS
-- Table columns info
select * from dba_tab_columns where table_name = 'DB_PREAUTH_CUSTOMER_RECORD';


------------------CONSTRAINTS
-- Constraint info
select table_name, constraint_name, constraint_type, status from dba_constraints where owner in('ADMIN', 'ADMADR')

-- List constraints with related constraint and its type
select constraint_name, r_constraint_name, constraint_type from dba_constraints where constraint_name = 'CONNECTION_PK'; 
select constraint_name, constraint_type from dba_constraints where r_constraint_name = 'CONNECTION_PK';


------------------DATAFILES
-- List all datafiles for an instance 
select substr(file_name,1,70) from dba_data_files order by file_name;


------------------DEPENDENCIES
-- List all dependencies for a referenced object
select * from user_dependencies where referenced_name = 'PLVCMT'


------------------INDEXES
-- Find all indexes for a specific table and list its columns
select index_name, column_name 
from dba_ind_columns 
where table_name = 'DB_CUSTOMER_RECORD' 
order by index_name, column_position


select index_name from dba_indexes where table_name = 'DB_ADDRESS';


------------------JOBS
-- Job queue info
select job, this_date, this_sec, next_date,  next_sec, interval, what,  failures, broken from dba_jobs;


------------------OBJECTS
-- Object info
select object_name from dba_objects where status <> 'VALID';


------------------PRIVILEGES
-- table privileges
desc dba_tab_privs 
select * from dba_tab_privs where grantee = 'SECURITYLOGON';
select * from dba_tab_privs where table_name = 'SECURITY_USERS';

-- role privileges
select * from dba_role_privs where grantee='SECURITYLOGON';

-- system privileges
select * from dba_sys_privs where grantee='SECURITYLOGON';

-- others:
DBA_COL_PRIVS
DBA_PRIV_AUDIT_OPTS


------------------SEGMENTS
-- Segment information
select segment_name, count(*), sum(bytes) from dba_extents where segment_name = 'DB_READ' group by segment_name ;
select * from dba_segments where segment_name = 'DB_READ';
select * from dba_segments@p1aqcis where segment_name = 'DB_READ';
select segment_name, count(*), sum(bytes) from dba_extents group by segment_name having count(*) > 1;
select * from dba_tables where table_name = 'DB_READ';

select segment_name, segment_type, bytes 
from dba_segments 
where segment_name in ('CIVIC_ADDRESS','DB_SERVICE','DB_ADDRESS','STREET','DB_METER');

select sum(bytes)
from dba_segments 
where segment_name in ('CIVIC_ADDRESS','DB_SERVICE','DB_ADDRESS','STREET','DB_METER');

-- list objects with number of extents greater than 
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
-- Source code from procedures, packages and functions
select owner,name,type,text from dba_source where upper(text) like '%ARRAY%';
select name,type,text from dba_source where upper(text) like '%REST_OF_LINE%';
select name,type,text from dba_source where upper(text) like '%ORU%';
select substr(line,1,4) line, text from dba_source where type = 'PACKAGE BODY' and name = 'PK_BATCH_JOBS'
select name,type,text from dba_source where upper(text) like '%POTENTIAL_DAYS%';
 

------------------TABLES
-- Determine 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 ('ADMIN','ADMADR');


------------------TABLESPACES
-- Tablespace info
select tablespace_name, bytes, file_name from dba_data_files order by tablespace_name;


------------------USERS
-- User info
set pagesize 0
set echo off
select 'ROLE '||GRANTED_ROLE from dba_role_privs where grantee='CAS';
select 'SYSTEM PRIVILEGE '||privilege from dba_sys_privs where grantee='CAS';
select privilege||' ON '||owner||'.'||table_name from dba_tab_privs where grantee='CAS';
set echo on


select * from dba_users where username = 'CNV';


------------------VIEWS
-- Find views that contain the string SEQ
select view_name from dba_views where view_name like '%SEQ%'