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$datafile v
where d.FILE_ID = v.FILE#
order by d.TABLESPACE_NAME, d.FILE_NAME;

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

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)

How to obtain the Historical Database Total Used and Allocated Size from OEM Repository

From time to time, it’s useful to know the total allocated size of a database at OS level, how much of it has been used and what the maximum total size the database can grow to at OS level, see blog post:
How to obtain the Database Total Used, Allocated and Max Size

However, it’s also good to know the historical size.  The below query will give you this from the Oracle Enterprise Manager (OEM) repository:

SELECT Database,
Month_Date,
round(sum(decode(metric_column, 'spaceUsed', maximum))/1024/1024, 3) Used_Size_Tb,
round(sum(decode(metric_column, 'spaceAllocated', maximum))/1024/1024, 3) Allocated_Size_Tb
FROM
(
SELECT target_name Database, trunc(rollup_timestamp, 'MONTH') Month_Date, key_value TB, metric_column, round(max(maximum),0) maximum
FROM mgmt$metric_daily
WHERE target_type = 'rac_database'
and metric_name = 'tbspAllocation'
and metric_column in ('spaceAllocated', 'spaceUsed')
and target_name in ('VERS')
GROUP BY target_name, key_value, trunc(rollup_timestamp, 'MONTH'), metric_column
)
GROUP BY Database, Month_Date
ORDER BY Database, Month_Date
/

Output:

DATABASE   MONTH_DAT USED_SIZE_TB ALLOCATED_SIZE_TB
---------- --------- ------------ -----------------
VERS       01-SEP-15        1.198             1.554
VERS       01-OCT-15        1.209             1.652
VERS       01-NOV-15          1.3             1.805
...
VERS       01-MAY-17        6.526             7.226
VERS       01-JUN-17        7.085             8.528
VERS       01-JUL-17        7.136             7.569

23 rows selected.

SQL>

The unit is in Tb, which should be suitable for most, however this can be changed by add/removing division of 1024.

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)

How to obtain the Database Total Used, Allocated and Max Size

From time to time, it’s useful to know the total allocated size of a database at OS level, how much of it has been used and what the maximum total size the database can grow to at OS level.

The below query will give you this:

SELECT round(sum(used_ts_size)/1024/1024, 2) total_used_db_size_tb,
 round(sum(curr_ts_size)/1024/1024, 2) total_current_db_size_tb,
 round(sum(max_ts_size)/1024/1024, 2) total_max_allocated_db_size_tb
FROM
(SELECT df.tablespace_name, (df.bytes - sum(fs.bytes)) / (1024 * 1024) used_ts_size,
df.bytes / (1024 * 1024) curr_ts_size,
df.maxbytes / (1024 * 1024) max_ts_size
FROM dba_free_space fs,
 (select tablespace_name,
 sum(bytes) bytes,
 sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes
 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);

Output:

SQL> SELECT round(sum(used_ts_size)/1024/1024, 2) total_used_db_size_tb,
 2 round(sum(curr_ts_size)/1024/1024, 2) total_current_db_size_tb,
 3 round(sum(max_ts_size)/1024/1024, 2) total_max_allocated_db_size_tb
 4 FROM
 5 (SELECT df.tablespace_name, (df.bytes - sum(fs.bytes)) / (1024 * 1024) used_ts_size,
 6 df.bytes / (1024 * 1024) curr_ts_size,
 7 df.maxbytes / (1024 * 1024) max_ts_size
 8 FROM dba_free_space fs,
 9 (select tablespace_name,
 10 sum(bytes) bytes,
 11 sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes
 12 from dba_data_files
 13 group by tablespace_name) df
 14 WHERE fs.tablespace_name (+) = df.tablespace_name
 15 GROUP BY df.tablespace_name,df.bytes,df.maxbytes);

TOTAL_USED_DB_SIZE_TB TOTAL_CURRENT_DB_SIZE_TB TOTAL_MAX_ALLOCATED_DB_SIZE_TB
--------------------- ------------------------ ------------------------------
                 7.15                     7.36                           9.04

SQL>

The unit is in Tb, which should be suitable for most, however this can be changed by add/removing division of 1024.

Related Post:
How to obtain the Historical Database Total Used and Allocated Size from OEM Repository

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)

How to find the Oldest and Newest Interval Partition

I was required to find the oldest and newest interval partition to be able to do some automated maintenance, but found query USER_TAB_PARTITIONS, ALL_TAB_PARTITIONS and DBA_TAB_PARTITIONS was a bit tricky as the HIGH_VALUE is a LONG data type 😦

A quick search on the web and I found a perfect solution from my good friend Tim Hall (Oracle Base), who created a function to convert the LONG into a DATE by executing the HIGH VALUE 🙂

Script: part_hv_to_date.sql

For ease of access, script below (please check Tim’s blog for updates and always ask for permission if you’re going to quote 🙂 ):

CREATE OR REPLACE FUNCTION part_hv_to_date (p_table_owner IN VARCHAR2,
 p_table_name IN VARCHAR2,
 p_partition_name IN VARCHAR2)
 RETURN DATE
-- -----------------------------------------------------------------------------------
-- File Name : https://oracle-base.com/dba/miscellaneous/part_hv_to_date.sql
-- Author : Tim Hall
-- Description : Create a function to turn partition HIGH_VALUE column to a date.
-- Call Syntax : @part_hv_to_date
-- Last Modified: 19/01/2012
-- Notes : Has to re-select the value from the view as LONG cannot be passed as a parameter.
-- Example call:
--
-- SELECT a.partition_name, 
-- part_hv_to_date(a.table_owner, a.table_name, a.partition_name) as high_value
-- FROM all_tab_partitions a;
--
-- Does no error handling. 
-- -----------------------------------------------------------------------------------
AS
 l_high_value VARCHAR2(32767);
 l_date DATE;
BEGIN
 SELECT high_value
 INTO l_high_value
 FROM all_tab_partitions
 WHERE table_owner = p_table_owner
 AND table_name = p_table_name
 AND partition_name = p_partition_name;
 
 EXECUTE IMMEDIATE 'SELECT ' || l_high_value || ' FROM dual' INTO l_date;
 RETURN l_date;
END;
/

 

With this you can get the oldest interval partition by running the following SQL:

SELECT PARTITION_NAME
FROM DBA_TAB_PARTITIONS P
WHERE TABLE_OWNER = 'ZEDDBA'
AND TABLE_NAME = 'EXAMPLE'
AND PART_HV_TO_DATE(TABLE_OWNER, TABLE_NAME, PARTITION_NAME) = (
SELECT MIN(PART_HV_TO_DATE(TABLE_OWNER, TABLE_NAME, PARTITION_NAME))
FROM DBA_TAB_PARTITIONS
WHERE TABLE_OWNER = P.TABLE_OWNER
AND TABLE_NAME = P.TABLE_NAME);

Output:

SQL> SELECT PARTITION_NAME
   2 FROM DBA_TAB_PARTITIONS P
   3 WHERE TABLE_OWNER = 'ZEDDBA'
   4 AND TABLE_NAME = 'EXAMPLE'
   5 AND PART_HV_TO_DATE(TABLE_OWNER, TABLE_NAME, PARTITION_NAME) = (
   6 SELECT MIN(PART_HV_TO_DATE(TABLE_OWNER, TABLE_NAME, PARTITION_NAME))
   7 FROM DBA_TAB_PARTITIONS
   8 WHERE TABLE_OWNER = P.TABLE_OWNER
   9 AND TABLE_NAME = P.TABLE_NAME);

PARTITION_NAME
--------------------
SYS_P10590

SQL>

 

And the newest interval partition by running the following SQL:

SELECT PARTITION_NAME
FROM DBA_TAB_PARTITIONS P
WHERE TABLE_OWNER = 'ZEDDBA'
AND TABLE_NAME = 'EXAMPLE'
AND PART_HV_TO_DATE(TABLE_OWNER, TABLE_NAME, PARTITION_NAME) = (
SELECT MAX(PART_HV_TO_DATE(TABLE_OWNER, TABLE_NAME, PARTITION_NAME))
FROM DBA_TAB_PARTITIONS
WHERE TABLE_OWNER = P.TABLE_OWNER
AND TABLE_NAME = P.TABLE_NAME);

Output:

SQL> SELECT PARTITION_NAME
   2 FROM DBA_TAB_PARTITIONS P
   3 WHERE TABLE_OWNER = 'ZEDDBA'
   4 AND TABLE_NAME = 'EXAMPLE'
   5 AND PART_HV_TO_DATE(TABLE_OWNER, TABLE_NAME, PARTITION_NAME) = (
   6 SELECT MAX(PART_HV_TO_DATE(TABLE_OWNER, TABLE_NAME, PARTITION_NAME))
   7 FROM DBA_TAB_PARTITIONS
   8 WHERE TABLE_OWNER = P.TABLE_OWNER
   9 AND TABLE_NAME = P.TABLE_NAME);

PARTITION_NAME
--------------------
SYS_P17718

SQL>

 

Updates

8th July 2017, changed the query so the sub-query join onto the parent query to avoid having to set the predicates in the sub-query.

 

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)