Sunday, October 21, 2018

7 Days of Oracle: Day #2 - dba_data_files

Much like how SQL Server has filegroups made up of datafiles, Oracle has tablespaces which are made up of datafiles. When supporting an Oracle environment, you will often receive incidents/tickets for a tablespace running low on space. Oracle stores information about datafiles in a table called dba_data_files which can be used to help investigate storage issues and plan a path forward.


Let’s look at the information provided by the dba_data_files table.  I will only be using a subset of the attributes below, however, it’s always good to know what’s available to you. 



SQL> desc dba_data_files

 Name                                              Null?    Type

 ----------------------------------------- -------- ----------------------------

 FILE_NAME                                                       VARCHAR2(513)

 FILE_ID                                                               NUMBER

 TABLESPACE_NAME                                     VARCHAR2(30)

 BYTES                                                                 NUMBER

 BLOCKS                                                              NUMBER

 STATUS                                                               VARCHAR2(9)

 RELATIVE_FNO                                               NUMBER

 AUTOEXTENSIBLE                                         VARCHAR2(3)

 MAXBYTES                                                       NUMBER

 MAXBLOCKS                                                    NUMBER

 INCREMENT_BY                                             NUMBER

 USER_BYTES                                                    NUMBER

 USER_BLOCKS                                                 NUMBER

 ONLINE_STATUS                                             VARCHAR2(7)

 LOST_WRITE_PROTECT                              VARCHAR2(7)



Let’s go through the process of investigating storage for a tablespace.  Start by finding the current size and location of the tablespace having space issues (in this example we will us the tablespace ‘DEMO_DATA’).



-- List all datafiles for a specific tablespace

-- Note the conversion of bytes to MB

select FILE_NAME, BYTES/1024/1024, TABLESPACE_NAME from dba_data_files where TABLESPACE_NAME ='DEMO_DATA'


FILE_NAME                                                                 BYTES/1024/1024            TABLESPACE_NAME

-----------------------------------------------------                   ---------------                          ------------------------------

/home/oracle/datafiles04/demo_data_01.dbf                 30720                                   DEMO_DATA

/home/oracle/datafiles05/demo_data_02.dbf                 15360                                   DEMO_DATA


Now that you have a list of the datafiles in a tablespace and their current size, you can determine if you can simply increase an existing datafile or if you need to add a new datafile to the tablespace (or both!).  Once you've decided which route to take, you will need to verify you have enough disk available to support the amount of space you want to add to your tablespace.  This is not as straight forward as you would hope!


First you can check the file system and verify how much space is available on the mount(s) where the datafiles are being stored.  If there is enough storage available to support your increase then that is great, however, your work is not done. 


Next you need to check all the datafiles in the database and check to see if any other datafile that is on the same mount is set to Autogrow.  If a datafile is set to Autogrow, then you need to answer the question "If this file were to grow to its max size, would there still be enough space left on the mount for the increase I need to resolve the incident/ticket?".  If the answer is no, then you need to increase the storage available at the OS level before you can increase your tablespace.


To check for files set to Autogrow:


--List all datafiles and their autogrowth/max size settings

select FILE_NAME, BYTES/1024/1024, AUTOEXTENSIBLE, MAXBYTES/1024/1024, TABLESPACE_NAME from dba_data_files order by AUTOEXTENSIBLE;


FILE_NAME                                                                BYTES/1024/1024              AUT       MAXBYTES/1024/1024                 TABLESPACE_NAME

-----------------------------------------------------                   ---------------                          ---           ------------------                     ------------------------------

/home/oracle/datafiles01/system01.dbf                        740                                         YES         32767.9844                        SYSTEM

/home/oracle/datafiles01/system02.dbf                        30720                                     YES         31744                                  SYSTEM

/home/oracle/datafiles02/sysaux01.dbf                        23250                                     YES         32767.9844                        SYSAUX

/home/oracle/datafiles03/undo01.dbf                           26955                                     YES         32767.9844                        UNDO01

/home/oracle/datafiles02/users01.dbf                           86.25                                      YES         32767.9844                        USERS

/home/oracle/datafiles03/undo02.dbf                           29814.8125                           YES         32767.9844                        UNDO02

/home/oracle/datafiles04/demo_data_01.dbf                512                                         YES         32767.9844                        DEMO_DATA

/home/oracle/datafiles05/demo_data_02.dbf                512                                         YES          15360                                 DEMO_DATA

/home/oracle/datafiles04/other_data_01.dbf                 512                                         YES         32767.9844                        OTHER_DATA

/home/oracle/datafiles05/other_data_02.dbf                 512                                         YES         32767.9844                        OTHER_DATA



There is one more piece of information you should gather....  dba_data_files does NOT include the files used for the temporary tablespace.  You can find that information in a table called dba_temp_files.


-- Find the autogrowth and max size settings for datafiles used by the temporary tablespace(s). 

select FILE_NAME, BYTES/1024/1024, AUTOEXTENSIBLE, MAXBYTES/1024/1024, TABLESPACE_NAME from dba_temp_files;


FILE_NAME                                        BYTES/1024/1024        AUT       MAXBYTES/1024/1024   TABLESPACE_NAME

------------------------------------------        ---------------                    ---           ------------------                     ------------------------------

/home/oracle/datafiles05/temp.dbf       50                                      YES       4096                                       TEMP



Lastly, if your database is replicated you need to verify you have enough storage on the secondary node(s) as well.  Mount sizes should match between nodes as a best practice, however, this can be forgotten and it’s always best to check before attempting to add space to a datafile.


Read the Oracle documentation to learn more about tablespaces.


No comments:

Post a Comment

PASS Summit - Day 5

Wakey Wakey My very last morning waking up in Seattle and I finally sleep until my alarm goes off.  It was a bitter sweet morning.  On one...