To Find table has any mview dependent

DECLARE
v_output VARCHAR2 (2000);
v_owner VARCHAR2 (30);
v_table VARCHAR2 (30);
BEGIN
v_owner := UPPER (‘<Owner_name’);
v_table := UPPER (‘<Table_name>’);
dbms_mview.get_mv_dependencies (v_owner || ‘.’ || v_table, v_output);
DBMS_OUTPUT.put_line (CHR (13));
DBMS_OUTPUT.put_line
(‘Materialized Views Dependent on table’ );
DBMS_OUTPUT.put_line (CHR (13));
DBMS_OUTPUT.put_line (v_output);
END;
/
select count(*) from DBA_MVIEW_LOGS where MASTER = ‘<Table_Name>’;

To find the index associated with table

column table_owner format a15
column table_name format a20
column index_name format a20
column column_name format a20

Select table_owner, table_name, index_name, column_name
FROM dba_ind_columns
Order by table_owner, table_name, column_name
Where table_owner=’OWNER_NAME’
AND table_name=’TABLE_NAME’;

To find the ROW Counts of tables that belongs to a Particular Schema

select table_name,num_rows counter from dba_tables where owner=’Schema_name’ order by table_name;

How to find NLS_CHARACTERSET and Lanuage

select DECODE(parameter, ‘NLS_CHARACTERSET’, ‘CHARACTER SET’,
‘NLS_LANGUAGE’, ‘LANGUAGE’,
‘NLS_TERRITORY’, ‘TERRITORY’) name,
value from v$nls_parameters
WHERE parameter IN ( ‘NLS_CHARACTERSET’, ‘NLS_LANGUAGE’, ‘NLS_TERRITORY’)
/

Query to display the session details with the OS pid

— Show user info including os pid

set linesize 300

col “SID/SERIAL” format a10

col username format a15

col osuser format a15

col program format a40

col status format a15

select s.sid || ‘,’|| s.serial#, s.username, s.osuser, p.spid, s.program, s.status from v$session s,v$process p Where s.paddr = p.addr order by to_number(p.spid);

Script to find open cursors details

set pages 999
select sess.username
, sess.sid
, sess.serial#
, stat.value cursors
from v$sesstat stat
, v$statname sn
, v$session sess
where sess.username is not null
and sess.sid = stat.sid
and stat.statistic# = sn.statistic#
and sn.name = ‘opened cursors current’
order by value
/

Script to check sufficient space for undo generation

select sum(decode(status,’EXPIRED’,bytes,0))/sum(bytes) EXPIRED_PERCENT, sum(decode(status,’UNEXPIRED’,bytes,0))/sum(bytes) UNEXPIRED_PERCENT, sum(decode(status,’ACTIVE’,bytes,0))/sum(bytes) ACTIVE_PERCENT from dba_undo_extents;

Useful script to get the basic status of the DB

Status

Startup time

select to_char(startup_time, ‘HH24:MI DD-MON-YY’) “Startup time” from v$instance /

======================================================================
How large is the database

SELECT ROUND (SUM (used.BYTES) / 1024 / 1024 / 1024) || ‘GB’ “Database
Size”,
ROUND (SUM (used.BYTES) / 1024 / 1024 / 1024)
– ROUND (SUM (free.BYTES) / 1024 / 1024 / 1024)
|| ‘GB’ “Used Space”,
ROUND (SUM (free.BYTES) / 1024 / 1024 / 1024) || ‘GB’ “Free
Space”
FROM (SELECT BYTES
FROM v$datafile
UNION ALL
SELECT BYTES
FROM v$tempfile
UNION ALL
SELECT BYTES
FROM v$log) used,
(SELECT BYTES
FROM dba_free_space) free;

=============================================================
Which schemas are taking up all of the space

SELECT obj.owner, obj.obj_cnt, seg.seg_size
FROM (SELECT owner, COUNT (*) obj_cnt
FROM dba_objects
GROUP BY owner) obj,
(SELECT owner, CEIL (SUM (BYTES) / 1024 / 1024) seg_size
FROM dba_segments
GROUP BY owner) seg
WHERE obj.owner = seg.owner
ORDER BY seg.seg_size desc;

====================================================
Distribution of objects and data

SELECT obj.owner “Owner”, obj_cnt “Objects”,
DECODE (seg_size, NULL, 0, seg_size) “size MB”
FROM (SELECT owner, COUNT (*) obj_cnt
FROM dba_objects
GROUP BY owner) obj,
(SELECT owner, CEIL (SUM (BYTES) / 1024 / 1024) seg_size
FROM dba_segments
GROUP BY owner) seg
WHERE obj.owner = seg.owner(+)
ORDER BY 3 DESC, 2 DESC, 1

======================================================
Show the ten largest objects in the database

SELECT owner, segment_name, segment_type, mb
FROM (SELECT owner, segment_name, segment_type, BYTES / 1024 / 1024
“MB”
FROM dba_segments
ORDER BY BYTES DESC)
WHERE ROWNUM < 11;

=====================================================
Is java installed in the database?

SELECT COUNT (*)
FROM all_objects
WHERE object_type LIKE ‘%JAVA%’ AND owner = ‘SYS’;

========================================================
Display character set information

SELECT *
FROM nls_database_parameters;

===================================================
Show all used features

SELECT NAME, detected_usages
FROM dba_feature_usage_statistics
WHERE detected_usages > 0;

Script to check AWR retention period

col snap_interval format a30
col retention format a30
select snap_interval,
retention
from dba_hist_wr_control
/

Script to find the DB size

select round(sum(bytes)/1024/1024/1024) ||’ GB – DATA’ as DATA from dba_data_files;

select round(sum(bytes)/1024/1024/1024) ||’ GB – TEMP’ as TEMP from dba_temp_files;

select round(sum(bytes)/1024/1024/1024) ||’ GB – LOGS’ as LOGS from v$log;

=============================================================================

To find the DB size including the Temp tablespace size:-
—————————————————–

select dbfile+logfile+tmpfile dp_space from (select sum(bytes)/(1024*1024*1024) dbfile from v$datafile),(select sum(bytes)/(1024*1024*1024) logfile from v$logfile a,v$log b where a.group#=b.group#),(select sum(bytes)/(1024*1024*1024) tmpfile from v$tempfile where status=’ONLINE’);

==============================================================================

To find the DB size without the Temp tablespace size:-
—————————————————–

— How large is the database

col “Database Size” format a20
col “Free space” format a20
col “Used space” format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ‘ GB’ “Database Size”
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) –
round(free.p / 1024 / 1024 / 1024) || ‘ GB’ “Used space”
, round(free.p / 1024 / 1024 / 1024) || ‘ GB’ “Free space”
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/