Big load on your database server, you know the PID but not the actual SQL ID that is eating your resources? Hereby a small query to help you start the quest: 1. Login with sysdba privileges
|
1 |
sqlplus / as sysdba |
2. Execute the
Oracle datafile usage human readable format
Using this query you can check the actual usage and size of the data files in existing Oracle instance:
|
1 2 3 4 5 6 7 |
SET linesize 300; column FILE_NAME format a45; SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024 "Allocated Size(MB)", ((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) "Used Size (MB)", NVL (SUM (dfs.bytes) / 1024 / 1024, 0) "Free Size(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 |
Example output:
|
1 2 3 4 5 6 |
FILE_NAME Allocated Size(MB) Used Size (MB) Free Size(MB) ------------------------------------------ ------------------- -------------- -------------- /opt/oracle/oradata/DISTRIB2/sysaux01.db 340 331.25 8.75 /opt/oracle/oradata/DISTRIB2/system01.db 500 489.375 10.625 /opt/oracle/oradata/DISTRIB2/undotbs01.d 630 20.3125 609.6875 /opt/oracle/oradata/DISTRIB2/users01.dbf 13188.75 13187.3125 1.4375 |