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