How To Find Database Objects With Datafiles?
SELECT DISTINCT t.ts#,
t.name AS Tablespace_name,
d.name AS DATAFILE,
s.owner,
s.segment_name,
s.segment_type,
s.bytes / 1024 / 1024 AS “MB”
FROM v$tablespace t,
v$datafile d,
dba_segments s
WHERE t.ts# = d.ts#
AND t.name = s.tablespace_name
AND s.owner = ‘APPS’;
List all datafiles with full paths
SELECT FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024 AS “SIZE_MB”, STATUS, AUTOEXTENSIBLE FROM DBA_DATA_FILES ORDER BY TABLESPACE_NAME;
List datafiles for a specific tablespace
SELECT FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = ‘UNDO’;
SELECT NAME, FILE#, STATUS FROM V$DATAFILE;
To check information about datafiles
select * from dba_data_files;
select * from v$datafile;
To check information about tablespace
select * from dba_tablespaces;
select * from v$tablespace;
To check size of datafiles
select file_name,bytes/1024/1024/1024 from v$datafile;
To check the tablespace size
SELECT df.tablespace_name “Tablespace”,
totalusedspace “Used MB”,
(df.totalspace – tu.totalusedspace) “Free MB”,
df.totalspace “Total MB”,
ROUND(100 * ( (df.totalspace – tu.totalusedspace)/ df.totalspace)) “% Free”
FROM
(SELECT tablespace_name,
ROUND(SUM(bytes) / 1048576) TotalSpace
FROM dba_data_files
GROUP BY tablespace_name
) df,
(SELECT ROUND(SUM(bytes)/(1024*1024)) totalusedspace,
tablespace_name
FROM dba_segments
GROUP BY tablespace_name
) tu
WHERE df.tablespace_name = tu.tablespace_name;
Read More Articles
List All Undo Datafiles With Status And Size
To Check When a Table is Last Analysed
List All Datafile Of Undo Tablespace
Undo Space Used By All Transactions
Undo Tablespace Utilization By All Sessions
To Check For Active Transactions
Datewise Occurrence Of ORA-1555
To Check Space Related Statistics Of Undo Tablespace
To Show ACTIVE/EXPIRED/UNEXPIRED Extents of Undo Tablespace
To Check Retention Guarantee For Undo Tablespace
Toral Inactive Sessions More Than 1Hour
Inactive Programs With Disk Reads Prompt Inactive Sessions
Total Count Of Sessions Ordered By Action
Total Count Of Sessions Ordered By Module
Total Sessions Count Ordered By Program
Sessions Which Are In Inactive Status From More Than 1 HOUR
SQL Queries to Check Active or Inactive Sessions
Query To Find Database Redolog Temp And Control File Size
Query To Find Long Running SQL Statements
Query To Find Undo Tablespace Usage
Query To Check For Table Locks
Query To Find Active Sessions In Database
Query To Check Users Sessions CPU and IO Consumption
Query To Check Open Cursor By User
Query To Check Top 10 Database Load Queries
Query To Check List Of Locks In The Database
Query To Check Blocking Sessions Per User
Query To Check Size Of All Tablespaces
Query To Find The Details of The Accounting Flexfield Structure
Query To Find a Scheduled Job for a particular program
How to Find Scheduled Concurrent Program
How to Find The Request Group Associated with a Program
Query To Verify Workflow IMAP Setting
Query To Find Complete Database Size
Query To Find Find EBS Profile Options For All Values
Query To Find Number Of Users Connected To EBS
Query To Find Who Is Connected When to Which Responsibilities
Query For Workflow Mailer Configurations
Query To Find All Responsibilities Assigned To a User
Query To Find All Users Assigned To Given Responsibility
Query To Find All Reports Registered Within Given Application
Query To Find Responsibility Name from a Concurrent Program
Query Count the number of reports registered In Each Module
Query To Find Scheduled Concurrent Request
Query To Find All Reports Status With User Name
Query To Count Pending Request
Query To find Concurrent Program Name, Phase Code And Status Code for a given Request ID
Query To Find a Concurrent Program Enabled with Trace
Query To Find Request Submitted By User
Query To Find Pending Request in Oracle Apps dba
Query To Find the Reports Finished With Error Code In Oracle Apps DBA