How To See Free Space of Each Tablespace?
Submitted by: AdministratorOne of the important DBA tasks is to watch the storage usage of all the tablespaces to make sure there are enough free space in each tablespace for database applications to function properly. Free space information can be monitored through the USER_FREE_SPACE view. Each record in USER_FREE_SPACE represents an extent, a contiguous area of space, of free space in a data file of a tablespace.
Here is SQL script example on how to see free space of a tablespace:
SQL> connect HR/globalguideline
Connected.
SQL> SELECT TABLESPACE_NAME, FILE_ID, BYTES
2 FROM USER_FREE_SPACE
3 WHERE TABLESPAE_NAME IN ('USERS', 'MY_SPACE');
Submitted by: Administrator
Here is SQL script example on how to see free space of a tablespace:
SQL> connect HR/globalguideline
Connected.
SQL> SELECT TABLESPACE_NAME, FILE_ID, BYTES
2 FROM USER_FREE_SPACE
3 WHERE TABLESPAE_NAME IN ('USERS', 'MY_SPACE');
Submitted by: Administrator
<pre>TABLESPACE_NAME FILE_ID BYTES
------------------------------ ---------- ----------
MY_SPACE 5 10354688
USERS 4 101974016
USERS 4 65536
USERS 4 65536
USERS 4 65536
USERS 4 65536
USERS 4 65536
USERS 4 65536
USERS 4 65536
USERS 4 65536
USERS 4 65536
USERS 4 65536
USERS 4 65536
USERS 4 65536</pre>
This tells us that:
► MY_SPACE has a single free extent of 10MB.
► USERS has one big free extent of 100MB, and many small free extents of 64KB.
Submitted by: Administrator
------------------------------ ---------- ----------
MY_SPACE 5 10354688
USERS 4 101974016
USERS 4 65536
USERS 4 65536
USERS 4 65536
USERS 4 65536
USERS 4 65536
USERS 4 65536
USERS 4 65536
USERS 4 65536
USERS 4 65536
USERS 4 65536
USERS 4 65536
USERS 4 65536</pre>
This tells us that:
► MY_SPACE has a single free extent of 10MB.
► USERS has one big free extent of 100MB, and many small free extents of 64KB.
Submitted by: Administrator
Read Online Oracle Database Job Interview Questions And Answers
Top Oracle Database Questions
☺ | What Is the Relation of a User Account and a Schema? |
☺ | How To Use Values from Other Tables in UPDATE Statements using Oracle? |
☺ | What Is Input Buffer in SQL*Plus? |
☺ | What Happens to Indexes If You Drop a Table? |
☺ | What Is Oracle Open Database Communication (ODBC)? |
Top DB Oracle Categories
☺ | Oracle PL-SQL Interview Questions. |
☺ | Oracle DBA Interview Questions. |
☺ | Oracle D2K Interview Questions. |
☺ | OCI Interview Questions. |
☺ | Oracle RMAN Interview Questions. |