Adding a Datafile to Temp Tablespace

When monitoring Tablespace Usage (see my Tablespace Usage blog post for more info), there comes a point when you need to add a datafile to the temp tablespace to allow for growth and more importantly get below a monitoring threshold for example in OEM or OpsView.

Query to see Current Temp Datafiles State

To see the current state of the temp datafiles:

set pages 999
set lines 400
col FILE_NAME format a75
select d.TABLESPACE_NAME, d.FILE_NAME, d.BYTES/1024/1024 SIZE_MB, d.AUTOEXTENSIBLE, d.MAXBYTES/1024/1024 MAXSIZE_MB, d.INCREMENT_BY*(v.BLOCK_SIZE/1024)/1024 INCREMENT_BY_MB
from dba_temp_files d,
 v$tempfile v
where d.FILE_ID = v.FILE#
order by d.TABLESPACE_NAME, d.FILE_NAME;

Please Note: For pluggable databases, please ensure you are in the right PDB as temp datafiles can be at container level and/or pluggable database level.

Output from Current Temp Datafiles State

This is the output you will get from the query:

TABLESPACE_NAME FILE_NAME SIZE_MB AUT MAXSIZE_MB INCREMENT_BY_MB
------------------------------ --------------------------------------------------------------------------- ---------- --- ---------- ---------------
TEMP +DATAC1/VERS/TEMPFILE/temp.451.891367325 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.452.891367321 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.454.891367305 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.455.891367301 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.457.891367299 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.458.891367295 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.459.891367159 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.461.891367165 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.463.891367201 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.465.891367203 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.466.891367207 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.468.891367223 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.469.891367227 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.471.891367243 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.472.891367251 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.473.891367255 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.475.891367261 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.477.891367289 32767 YES 32767 1024
...

38 rows selected.

SQL>

The … represents the several lines removed to make the output readable 🙂

Add Temp Datafile to Temp Tablespace

To add a temp datafile to ‘TEMP‘ to be initially 10G, auto extendable by 1G to maxsize of 32Gb:

ALTER TABLESPACE TEMP ADD TEMPFILE '+DATAC1' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 32767M;

See Oracle Documentation for more info and Syntax:
https://docs.oracle.com/database/121/SQLRF/statements_3002.htm

Once added you’ll see the temp datafile in the above query:

TABLESPACE_NAME FILE_NAME SIZE_MB AUT MAXSIZE_MB INCREMENT_BY_MB
------------------------------ --------------------------------------------------------------------------- ---------- --- ---------- ---------------
TEMP +DATAC1/VERS/TEMPFILE/temp.451.891367325 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.452.891367321 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.454.891367305 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.455.891367301 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.457.891367299 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.458.891367295 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.459.891367159 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.461.891367165 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.463.891367201 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.465.891367203 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.466.891367207 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.468.891367223 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.469.891367227 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.471.891367243 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.472.891367251 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.473.891367255 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.475.891367261 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.477.891367289 32767 YES 32767 1024
...
TEMP +DATAC1/VERS/TEMPFILE/temp.843.973079825 10240 YES 32767 1024

39 rows selected.

SQL>

Related Blog Posts:
Querying Tablespace Usage
Adding a Datafile to Tablespace

Updates
27th April 2018: Change the query to use v$tempfile instead of v$datafile as doesn’t work for PDBs.

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

Advertisements

How to fix queries on DBA_FREE_SPACE that are slow

I found myself in a situation where OpsView a monitoring tool, was having difficulty monitoring the tablespaces for a particular pluggable database.

Upon investigation it was found the queries against the dictionary table DBA_FREE_SPACE were taking a very long time:

SQL> set timing on
SQL> select nvl(sum(dfs.bytes)/1024/1024,0) from dba_free_space dfs where dfs.tablespace_name = 'USERS';

NVL(SUM(DFS.BYTES)/1024/1024,0)
-------------------------------
 70.75

Elapsed: 00:00:10.98

There are 60 tablespaces in this pluggable database, which the time varied querying each tablespace, but was by far where most the time was spent.

I wrote a PL/SQL block to mimic Opsview as I didn’t want to create an object (procedure) in this customer’s database:

SET SERVEROUTPUT ON
SET TIMING ON
DECLARE
 cursor ts_names is select tablespace_name from dba_tablespaces where contents != 'TEMPORARY';
 sql_used VARCHAR(200) := 'select nvl(sum(dfs.bytes)/1024/1024,0) from dba_free_space dfs where dfs.tablespace_name = ''text_string''';
 sql_free VARCHAR(200) := 'select nvl(sum(dfs.bytes)/1024/1024,0) from dba_free_space dfs where dfs.tablespace_name = ''text_string''';
 sql_max VARCHAR(200) := 'select sum(maxbytes-bytes)/1024/1024 from dba_data_files where AUTOEXTENSIBLE = ''YES'' and maxbytes>bytes and tablespace_name = ''text_string''';
 num_out NUMBER;
BEGIN
 FOR ts_name in ts_names
 LOOP
 --sql for used space
 EXECUTE IMMEDIATE replace(sql_used, 'text_string', ts_name.tablespace_name) into num_out;
 dbms_output.put_line(replace(sql_used, 'text_string', ts_name.tablespace_name));
 dbms_output.put_line(num_out);
 --sql for free space
 EXECUTE IMMEDIATE replace(sql_free, 'text_string', ts_name.tablespace_name) into num_out;
 dbms_output.put_line(replace(sql_free, 'text_string', ts_name.tablespace_name));
 dbms_output.put_line(num_out);
 --sql for max
 EXECUTE IMMEDIATE replace(sql_max, 'text_string', ts_name.tablespace_name) into num_out;
 dbms_output.put_line(replace(sql_max, 'text_string', ts_name.tablespace_name));
 dbms_output.put_line(num_out);
 END LOOP;
END;
/

I ran this and the total time was shocking 😐 :

SQL> --SET SERVEROUTPUT ON
SQL> SET TIMING ON
SQL> DECLARE
 2 cursor ts_names is select tablespace_name from dba_tablespaces where contents != 'TEMPORARY';
 3 sql_used VARCHAR(200) := 'select nvl(sum(dfs.bytes)/1024/1024,0) from dba_free_space dfs where dfs.tablespace_name = ''text_string''';
 4 sql_free VARCHAR(200) := 'select nvl(sum(dfs.bytes)/1024/1024,0) from dba_free_space dfs where dfs.tablespace_name = ''text_string''';
 5 sql_max VARCHAR(200) := 'select sum(maxbytes-bytes)/1024/1024 from dba_data_files where AUTOEXTENSIBLE = ''YES'' and maxbytes>bytes and tablespace_name = ''text_string''';
 6 num_out NUMBER;
 7 BEGIN
 8 FOR ts_name in ts_names
 9 LOOP
 10 --sql for used space
 11 EXECUTE IMMEDIATE replace(sql_used, 'text_string', ts_name.tablespace_name) into num_out;
 12 dbms_output.put_line(replace(sql_used, 'text_string', ts_name.tablespace_name));
 13 dbms_output.put_line(num_out);
 14 --sql for free space
 15 EXECUTE IMMEDIATE replace(sql_free, 'text_string', ts_name.tablespace_name) into num_out;
 16 dbms_output.put_line(replace(sql_free, 'text_string', ts_name.tablespace_name));
 17 dbms_output.put_line(num_out);
 --sql for max
 18 19 EXECUTE IMMEDIATE replace(sql_max, 'text_string', ts_name.tablespace_name) into num_out;
 20 dbms_output.put_line(replace(sql_max, 'text_string', ts_name.tablespace_name));
 21 dbms_output.put_line(num_out);
 22 END LOOP;
END;
 23 24 /

PL/SQL procedure successfully completed.

Elapsed: 00:21:30.94
SQL>

So I searched My Oracle Support (MOS) and found the following MOS note:
Queries on DBA_FREE_SPACE are Slow (Doc ID 271169.1)

Which states:
“1) In release 10g, the view dba_free_space was modified to also include objects in the recycle bin.

2) Large number of objects in the recyclebin can slow down queries on  dba_free_space.

3) This is a normal behaviour.

4) For release 11g, the view dba_free_space doesn’t contain a hint which in case when there is only few objects in recyclebin, you may want to gather underlying stats of tables/dictionary to get better performance.”

The database indeed did have a lot of objects in the recycle bin (in the pluggable database):

SQL> SELECT count(*) from dba_recyclebin;

 COUNT(*)
----------
 27615

SQL>

With most of them drop recently:

SQL> select trunc(to_date(DROPTIME,'YYYY-MM-DD:HH24:MI:SS')), count(*) from dba_recyclebin group by trunc(to_date(DROPTIME,'YYYY-MM-DD:HH24:MI:SS'))
  2  order by 1
  3 /

TRUNC(TO_ COUNT(*)
--------- ----------
24-SEP-16 2
...
19-JAN-18 2506
20-JAN-18 4322
21-JAN-18 4321
22-JAN-18 4320
23-JAN-18 4321
24-JAN-18 4321
25-JAN-18 2446

421 rows selected.

SQL>

So I purged the recycle bin (with customers permission)  and re-ran the check:

SQL> purge dba_recyclebin;

DBA Recyclebin purged.

Elapsed: 00:06:30.39
SQL> --SET SERVEROUTPUT ON
SET TIMING ON
SQL> SQL> DECLARE
 2 cursor ts_names is select tablespace_name from dba_tablespaces where contents != 'TEMPORARY';
 3 sql_used VARCHAR(200) := 'select nvl(sum(dfs.bytes)/1024/1024,0) from dba_free_space dfs where dfs.tablespace_name = ''text_string''';
 4 sql_free VARCHAR(200) := 'select nvl(sum(dfs.bytes)/1024/1024,0) from dba_free_space dfs where dfs.tablespace_name = ''text_string''';
 5 sql_max VARCHAR(200) := 'select sum(maxbytes-bytes)/1024/1024 from dba_data_files where AUTOEXTENSIBLE = ''YES'' and maxbytes>bytes and tablespace_name = ''text_string''';
 6 num_out NUMBER;
 7 BEGIN
 8 FOR ts_name in ts_names
 9 LOOP
 10 --sql for used space
 11 EXECUTE IMMEDIATE replace(sql_used, 'text_string', ts_name.tablespace_name) into num_out;
 12 dbms_output.put_line(replace(sql_used, 'text_string', ts_name.tablespace_name));
 13 dbms_output.put_line(num_out);
 14 --sql for free space
 15 EXECUTE IMMEDIATE replace(sql_free, 'text_string', ts_name.tablespace_name) into num_out;
 16 dbms_output.put_line(replace(sql_free, 'text_string', ts_name.tablespace_name));
 17 dbms_output.put_line(num_out);
 18 --sql for max
 19 EXECUTE IMMEDIATE replace(sql_max, 'text_string', ts_name.tablespace_name) into num_out;
 20 dbms_output.put_line(replace(sql_max, 'text_string', ts_name.tablespace_name));
 21 dbms_output.put_line(num_out);
 22 END LOOP;
 23 END;
 24 /

PL/SQL procedure successfully completed.

Elapsed: 00:02:46.25
SQL>

Result, the duration of the PL/SQL block went from 21 minutes to just under 3 minutes.  However I need it to go under 2 minutes as this was the timeout for OpsView.

So I proceed with the next recommendation in the MOS note of gather dictionary and fixed table stats (with customers permission) using MOS note:
How to Gather Statistics on Objects Owned by the ‘SYS’ User and ‘Fixed’ Objects (Doc ID 457926.1)

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.

Elapsed: 00:00:20.49

SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

PL/SQL procedure successfully completed.

Elapsed: 00:04:28.07

SQL> --SET SERVEROUTPUT ON
SET TIMING ON
SQL> SQL> DECLARE
 2 cursor ts_names is select tablespace_name from dba_tablespaces where contents != 'TEMPORARY';
 3 sql_used VARCHAR(200) := 'select nvl(sum(dfs.bytes)/1024/1024,0) from dba_free_space dfs where dfs.tablespace_name = ''text_string''';
 4 sql_free VARCHAR(200) := 'select nvl(sum(dfs.bytes)/1024/1024,0) from dba_free_space dfs where dfs.tablespace_name = ''text_string''';
 5 sql_max VARCHAR(200) := 'select sum(maxbytes-bytes)/1024/1024 from dba_data_files where AUTOEXTENSIBLE = ''YES'' and maxbytes>bytes and tablespace_name = ''text_string''';
 6 num_out NUMBER;
 7 BEGIN
 8 FOR ts_name in ts_names
 9 LOOP
 10 --sql for used space
 11 EXECUTE IMMEDIATE replace(sql_used, 'text_string', ts_name.tablespace_name) into num_out;
 12 dbms_output.put_line(replace(sql_used, 'text_string', ts_name.tablespace_name));
 13 dbms_output.put_line(num_out);
 14 --sql for free space
 15 EXECUTE IMMEDIATE replace(sql_free, 'text_string', ts_name.tablespace_name) into num_out;
 16 dbms_output.put_line(replace(sql_free, 'text_string', ts_name.tablespace_name));
 17 dbms_output.put_line(num_out);
 18 --sql for max
 19 EXECUTE IMMEDIATE replace(sql_max, 'text_string', ts_name.tablespace_name) into num_out;
 20 dbms_output.put_line(replace(sql_max, 'text_string', ts_name.tablespace_name));
 21 dbms_output.put_line(num_out);
 22 END LOOP;
 23 END;
 24 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.53
SQL>

Bingo! the duration of the PL/SQL block went down to 4 seconds 🙂

PLEASE NOTE: This still effects non-pluggable databases, however in pluggable databases, you need to purge the recycle bin for where the dropped objects are, the container database and the pluggable databases require independent purge.

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

New READ Object Privilege in 12cR1

In writing a blog post about:
Creating a Read Only Database User Account in an Oracle Database

It came to my attention of the new “READ” object privilege, which is a New Feature in 12.1.0.2:
Changes in Oracle Database 12c Release 1 (12.1.0.2)
READ and SELECT Object Privileges

The “SELECT” object privilege in addition to querying the table, allows the user to:
LOCK TABLE table_name IN EXCLUSIVE MODE;
SELECTFROM table_name FOR UPDATE;

The New Feature of “READ” object privilege, does not allow the user to lock tables in exclusive mode nor select table for update.

Prior to 12.1.0.2, the “SELECT” object privilege is only available which allows the locking:

GRANT SELECT ON ... TO ...;

12.1.0.2 onwards, the new “READ” object privilege is available which doesn’t allow the locking:

GRANT READ ON ... TO ...;

This also applies to the “SELECT ON ANY TABLE“, prior to 12.1.0.2 which allows the locking:

GRANT SELECT ANY TABLE TO ...;

12.1.0.2 onwards, the new “READ ON ANY TABLE” object privilege is available which doesn’t allow the locking:

GRANT READ ANY TABLE TO ...;

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

Creating a Read Only Database User Account in an Oracle Database

It can be quite common to create a “Read Only” database user account in an Oracle database.  To do this is pretty simple using the principle of least privilege:

CREATE USER READ_ONLY IDENTIFIED BY "password";
GRANT CREATE SESSION TO READ_ONLY;

Expected output:

SQL> CREATE USER READ_ONLY IDENTIFIED BY "password";

User created.

SQL> GRANT CREATE SESSION TO READ_ONLY;

Grant succeeded.

SQL>

Depending on the Oracle Version, it’s recommend to use “READ” object privilege new in 12.1.0.2 instead of “SELECT” to prevent locking.  See the following blog post for more info:
New READ Object Privilege in 12cR1

PLEASE NOTE: For anyone using Oracle Version prior to 12.1.0.2, replace the object privilege “READ” with “SELECT“.

To read specific tables for a schema:

SET HEADING OFF
SET PAGES 9999 LINES 400
SPOOL TABLE_GRANTS.sql
SELECT 'GRANT READ ON ' || OWNER || '.' || TABLE_NAME || ' TO READ_ONLY;'
FROM DBA_TABLES
WHERE OWNER IN ('ZEDDBA')
ORDER BY 1;
SPOOL OFF
@TABLE_GRANTS.sql
!rm TABLE_GRANTS.sql

Expected output, where you can see the user “READ_ONLY” able to select from the table owned by user “ZEDDBA“:

SQL> SET HEADING OFF
SQL> SET PAGES 9999 LINES 400
SQL> SPOOL TABLE_GRANTS.sql
SQL> SELECT 'GRANT READ ON ' || OWNER || '.' || TABLE_NAME || ' TO READ_ONLY;'
 2 FROM DBA_TABLES
 3 WHERE OWNER IN ('ZEDDBA')
 4 ORDER BY 1;

GRANT READ ON ZEDDBA.DEMO TO READ_ONLY;

SQL> SPOOL OFF
SQL> @TABLE_GRANTS.sql
SP2-0734: unknown command beginning "SQL> SELEC..." - rest of line ignored.

Grant succeeded.

SP2-0734: unknown command beginning "SQL> SPOOL..." - rest of line ignored.
SQL> !rm TABLE_GRANTS.sql

SQL> conn read_only/password
Connected.
SQL> SELECT * FROM ZEDDBA.DEMO;

Read Only example.

SQL> exit

To read all tables in the databases:

GRANT READ ANY TABLE TO READ_ONLY;

Expected output, where you can see the user “READ_ONLY” able to select from the table owned by user “ZEDDBA“, even after the revoke and the user “ZEDDBA2“, which hasn’t been explicitly referenced:

SQL> conn zeddba/zeddba
Connected.
SQL> REVOKE READ ON ZEDDBA.DEMO FROM READ_ONLY;

Revoke succeeded.

SQL> GRANT READ ANY TABLE TO READ_ONLY;

Grant succeeded.

SQL> conn read_only/password
Connected.
SQL> SELECT * FROM ZEDDBA.DEMO;

COMMENTS
--------------------------------------------------------------------------------
Read Only example.

SQL> SELECT * FROM ZEDDBA2.DEMO;

COMMENTS
--------------------------------------------------------------------------------
Read Only example.

SQL>

To grant access to dictionary tables:

GRANT SELECT ANY DICTIONARY TO READ_ONLY;

Expected output, where you can see the user “READ_ONLY” able to select from dictionary i.e. table starting with DBA and V$:

SQL> conn zeddba/zeddba
Connected.
SQL> GRANT SELECT ANY DICTIONARY TO READ_ONLY;

Grant succeeded.

SQL> conn read_only/password
Connected.
SQL> SELECT TABLE_NAME
 2 FROM DBA_TABLES
 3 WHERE OWNER = 'ZEDDBA';

TABLE_NAME
--------------------------------------------------------------------------------
DEMO

SQL> SELECT NAME FROM V$DATABASE;

NAME
---------
ZEDDBA

SQL>

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

Adding a Datafile to Tablespace

When monitoring Tablespace Usage (see my Tablespace Usage blog post for more info), there comes a point when you need to add a datafile to a tablespace to allow for growth and more importantly get below a monitoring threshold for example in OEM or OpsView.

Query to see Current Datafiles State

To see the current state of the datafiles:

set pages 999
set lines 400
col FILE_NAME format a75
select d.TABLESPACE_NAME, d.FILE_NAME, d.BYTES/1024/1024 SIZE_MB, d.AUTOEXTENSIBLE, d.MAXBYTES/1024/1024 MAXSIZE_MB, d.INCREMENT_BY*(v.BLOCK_SIZE/1024)/1024 INCREMENT_BY_MB
from dba_data_files d,
 v$datafile v
where d.FILE_ID = v.FILE#
order by d.TABLESPACE_NAME, d.FILE_NAME;

Output from Current Datafiles State

This is the output you will get from the query:

TABLESPACE_NAME FILE_NAME                                      SIZE_MB AUT MAXSIZE_MB INCREMENT_BY_MB
--------------- ---------------------------------------------- ------- --- ---------- ---------------
VERS_DATA       +DATAC1/VERS/DATAFILE/vers_data.395.891367103    32767 YES      32767            1024
...
VERS_DATA       +DATAC1/VERS/DATAFILE/vers_data.772.947087161     4096 YES      32767            1024
VERS_INDEX      +DATAC1/VERS/DATAFILE/vers_index.434.891367657    3072 YES      32767            1024
VERS_INDX       +DATAC1/VERS/DATAFILE/vers_indx.422.891367801    32767 YES      32767            1024
...
VERS_INDX       +DATAC1/VERS/DATAFILE/vers_indx.783.947764359    10240 YES      32767            1024
SNP_DATA        +DATAC1/VERS/DATAFILE/snp_data.421.891367815      1024 YES      32767            1024
SYSAUX          +DATAC1/VERS/DATAFILE/sysaux.488.891349085       31500 YES 32767.9844              10
SYSAUX          +DATAC1/VERS/DATAFILE/sysaux.577.909753421       12288 YES      12288              10
SYSTEM          +DATAC1/VERS/DATAFILE/system.487.891349083         890 YES 32767.9844              10
UNDOTBS1        +DATAC1/VERS/DATAFILE/undotbs1.489.891349085     30565 YES 32767.9844               5
UNDOTBS2        +DATAC1/VERS/DATAFILE/undotbs2.491.891349091     20555 YES 32767.9844               5
USERS           +DATAC1/VERS/DATAFILE/users.492.891349091            5 YES 32767.9844            1.25

273 rows selected.

SQL>

The … represents the several lines removed to make the output readable 🙂

Add Datafile to Tablespace

To add a datafile to ‘VERS_DATA‘ to be initially 10G, auto extendable by 1G to maxsize of 32Gb:

ALTER TABLESPACE VERS_DATA ADD DATAFILE '+DATAC1' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 32767M;

See Oracle Documentation for more info and Syntax:

https://docs.oracle.com/database/121/SQLRF/statements_3002.htm

Once added you’ll see the datafile in the above query:

TABLESPACE_NAME FILE_NAME                                      SIZE_MB AUT MAXSIZE_MB INCREMENT_BY_MB
--------------- ---------------------------------------------- ------- --- ---------- ---------------
VERS_DATA       +DATAC1/VERS/DATAFILE/vers_data.395.891367103    32767 YES      32767            1024
...
VERS_DATA       +DATAC1/VERS/DATAFILE/vers_data.772.947087161     4096 YES      32767            1024
VERS_DATA       +DATAC1/VERS/DATAFILE/vers_data.774.947426643    10240 YES      32767            1024
VERS_INDEX      +DATAC1/VERS/DATAFILE/vers_index.434.891367657    3072 YES      32767            1024
VERS_INDX       +DATAC1/VERS/DATAFILE/vers_indx.422.891367801    32767 YES      32767            1024
...
VERS_INDX       +DATAC1/VERS/DATAFILE/vers_indx.783.947764359    10240 YES      32767            1024
SNP_DATA        +DATAC1/VERS/DATAFILE/snp_data.421.891367815      1024 YES      32767            1024
SYSAUX          +DATAC1/VERS/DATAFILE/sysaux.488.891349085       31500 YES 32767.9844              10
SYSAUX          +DATAC1/VERS/DATAFILE/sysaux.577.909753421       12288 YES      12288              10
SYSTEM          +DATAC1/VERS/DATAFILE/system.487.891349083         890 YES 32767.9844              10
UNDOTBS1        +DATAC1/VERS/DATAFILE/undotbs1.489.891349085     30565 YES 32767.9844               5
UNDOTBS2        +DATAC1/VERS/DATAFILE/undotbs2.491.891349091     20555 YES 32767.9844               5
USERS           +DATAC1/VERS/DATAFILE/users.492.891349091            5 YES 32767.9844            1.25

274 rows selected.

SQL>

Related Blog Posts:
Querying Tablespace Usage
Adding a Datafile to Temp Tablespace

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

Querying Tablespace Usage

Tablespace management in an Oracle database is important and something a DBA will need to do quite frequently.  Therefore I wrote a query, which I believe I originally got from the internet and then it evolved by me adding in temp tablespace and few other things 🙂

Query for Tablespace Usage

Query to obtain Tablespace Usage:

set pages 999
set lines 400
SELECT df.tablespace_name tablespace_name,
 max(df.autoextensible) auto_ext,
 round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
 round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used,
 round(df.bytes / (1024 * 1024), 2) curr_ts_size,
 round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,
 round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used,
 round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size,
 nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free
FROM dba_free_space fs,
 (select tablespace_name,
 sum(bytes) bytes,
 sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
 max(autoextensible) autoextensible
 from dba_data_files
 group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
UNION ALL
SELECT df.tablespace_name tablespace_name,
 max(df.autoextensible) auto_ext,
 round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
 round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used,
 round(df.bytes / (1024 * 1024), 2) curr_ts_size,
 round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,
 round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used,
 round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size,
 nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free
FROM (select tablespace_name, bytes_used bytes
 from V$temp_space_header
 group by tablespace_name, bytes_free, bytes_used) fs,
 (select tablespace_name,
 sum(bytes) bytes,
 sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
 max(autoextensible) autoextensible
 from dba_temp_files
 group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
ORDER BY 4 DESC;

Output from Tablespace Usage Query

This is the output you will get from the query:

TABLESPACE_NAME AUT MAX_TS_SIZE MAX_TS_PCT_USED CURR_TS_SIZE USED_TS_SIZE TS_PCT_USED FREE_TS_SIZE TS_PCT_FREE
--------------- --- ----------- --------------- ------------ ------------ ----------- ------------ -----------
VERS_DATA       YES     6061895           85.81      5270187   5201788.31        98.7     68398.69           1
VERS_INDX       YES  2391991.98           85.55   2101084.98   2046384.98        97.4        54700           3
SYSAUX          YES    45055.98           73.43        43788     33084.44       75.56     10703.56          24
UNDOTBS1        YES    32767.98           53.95        30565     17678.44       57.84     12886.56          42
VERS_INDEX      YES       32767            6.95         3072      2278.88       74.18       793.13          26
TEMP            YES  1245165.69            4.72       134141        58728       43.78        75413          56
SYSTEM          YES    32767.98            2.69          890       882.19       99.12         7.81           1
UNDOTBS2        YES    32767.98              .2        20555        64.38         .31     20490.63         100
SNP_DATA        YES       32767               0         1024            1          .1         1023         100
USERS           YES    32767.98               0            5         1.38        27.5         3.63          73

10 rows selected.

Column definitions

TABLESPACE_NAME: This is the Tablespace Name.

AUTO_EXT: If the datafiles are ‘Auto Extendable’ or not.

Please Note: This is using a max function, so if all are ‘NO’, then the ‘NO’ is true for all datafiles, however if one is ‘YES’, then the ‘YES’ is possible for one through to all of the datafiles.

MAX_TS_SIZE: This is the maximum Tablespace Size if all the datafile reach their max size.

MAX_TS_PCT_USED: This is the percent of MAX_TS_SIZE reached and is the most important value in the query, as this reflects the true usage before DBA intervention is required.

CURR_TS_SIZE: This is the current size of the Tablespace.

USED_TS_SIZE: This is how much of the CURR_TS_SIZE is used.

TS_PCT_USED: This is the percent of CURR_TS_SIZE which if ‘Auto Extendable’ is on, is a little meaningless.  Use MAX_TS_PCT_USED for actual usage.

FREE_TS_SIZE: This is how much is free in CURR_TS_SIZE.

TS_PCT_FREE: This is how much is free in CURR_TS_SIZE as a percent.

Please Note: All sizes are in Megabytes, this can be changed to Gigabytes by added a ‘/1024’ to the columns.

Related Blog Posts
Adding a datafile to tablespace
Adding a Datafile to Temp Tablespace

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)