Some DB Qrys

SQL>1
DATA FILE DETAILS UNDER THE TABLESPACE
==================================================================================================
select tablespace_name, file_name, file_id
from dba_data_files
where tablespace_name = 'PSAPSR3';



SQL>3
DATA FILE USAGE AND FREE SPACE  CALCULATION
===================================================================================================
select d.tablespace_name, d.file_id, d.file_name, d.bytes allocated, nvl(f.bytes,0) free
from (select tablespace_name, file_id, file_name, sum(bytes) bytes from dba_data_files
group by tablespace_name, file_id, file_name) d,
(select tablespace_name, file_id, sum(bytes) bytes from dba_free_space group by tablespace_name, file_id) f
where d.tablespace_name in ('PSAPSR3')
and d.tablespace_name = f.tablespace_name(+)
and d.file_id = f.file_id(+)
order by tablespace_name, file_id;


SQL>3
THE FOLLOWING SQL SCRIPT WHICH SHOW YOU THE ALLOCATED, FREE AND USED SPACE IN EACH OF DATAFILES.
 ==================================================================================================

SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024 allocated_mb,
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))
used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name


SQL>4
LARGE INDEX AND SEGMENTS
======================================================================================================
SELECT * FROM ( SELECT OWNER, SEGMENT_NAME, BYTES/1024/1024/1024 SIZE_GB
FROM DBA_SEGMENTS WHERE SEGMENT_TYPE = 'INDEX'
ORDER BY BYTES/1024/1024/1024  DESC ) WHERE ROWNUM <= 10
 
    SQL>5
USE BELOW SCRIPT TO DETERMIN WHICH TABLE HAS A LOT OF UNUSED SPACE.
=======================================================================================================
select a.owner, a.segment_name, a.segment_type, round(a.bytes/1024/1024,0) MBS,
round((a.bytes-(b.num_rows*b.avg_row_len) )/1024/1024,0) WASTED
from dba_segments a, dba_tables b where a.owner=b.owner
and a.owner not like 'SYS%' and a.segment_name = b.table_name
and a.segment_type='TABLE' group by a.owner, a.segment_name,
a.segment_type, round(a.bytes/1024/1024,0) ,round((a.bytes-(b.num_rows*b.avg_row_len) )/1024/1024,0)
having round(bytes/1024/1024,0) >100 order by round(bytes/1024/1024,0) desc ; 


  SQL> 6
 BELOW SCRIPT WILL RETRIVE THE DATA FILE AND CREATION OR ADDED DATE;
 ======================================================================================================
select a.file_name,a.tablespace_name,b.creation_time,
a.BYTES /1024/1024/1024 Size_GB from dba_data_files a,v$datafile b
where a.file_id=b.file# order by tablespace_name;


SQL> 7
 BELOW SCRIPT WILL RETRIVE THE DATA FILE SIZE AND FREE SIZE %USAGE;
===================================================================
COL     TABLESPACE_NAME FORMAT a16
SELECT A.TABLESPACE_NAME, A.STATUS,
ROUND(C.SIZE_GB/1024/1024/1024,2) SIZE_GB,
ROUND((C.SIZE_GB-B.FREE_GB)/1024/1024/1024,2) USAGE_GB,
ROUND(B.FREE_GB/1024/1024/1024,2) FREE_GB,
ROUND(100*(1-(FREE_GB/SIZE_GB)),2) PCT_USAGE
FROM (SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES) A,
(SELECT tablespace_name,
SUM(BYTES) FREE_GB
FROM DBA_FREE_SPACE
GROUP BY tablespace_name)B,
(SELECT TABLESPACE_NAME, SUM(BYTES) SIZE_GB
FROM dba_data_files
GROUP BY tablespace_name) C
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
AND A.TABLESPACE_NAME = C.TABLESPACE_NAME
ORDER BY TABLESPACE_NAME;




SQL> 8
 BELOW SCRIPT WILL SHOW YOU THE DATABASE SESSION UPDATE PROCESS;
===================================================================

SELECT sid, to_char(start_time,'hh24:mi:ss') stime,
message,( sofar/totalwork)* 100 percent
FROM v$session_longops
WHERE sofar/totalwork < 1;
SQL> 9 BUNDLE PATCH STATUS
===================================================================select action_time, version, bundle_series, comments
from dba_registry_history
where bundle_series = 'WINBUNDLE'
order by action_time desc;


SQL> 10  Execute the following query to see if your database was started with a PFILE or SPFILE:
===================================================================SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"
FROM sys.v_$parameter WHERE name = 'spfile';