DBA Tips Archive for Oracle
by Jeff Hunter, Sr. Database AdministratorManually Resizing Datafiles
Automatically Extending Datafiles
As of Oracle 7.2, existing datafiles can be resized via the alter database and alter tablespace command. To manually extend a datafile, use the alter database comment, as show in the following example:alter database datafile 'D:\ORANT\DEV\DATAFILES\USER1.ORA' resize 350M;
After the alter database command is executed, the datafile (in the above example), would be resized to 350M in size. If the datafile had been more than 350M in size, it will be decreased in size to 350M.
To resize a datafile using the alter tablespace command, you must specify the name of the tablespace to which the datafile belongs. Here is an example using the alter tablespace command:alter tablespace USER_DATA datafile 'D:\ORANT\DEV\DATAFILES\USER1.ORA' resize 350M;
Querying the Data Dictionary
As of Oracle 7.2, you can specify parameters when creating datafiles that will automatically extend datafiles whenever their current allocated length is exceeded. Here are the three sizing parameters used when creating datafiles:
autoextend A flag that can be set to ON or OFF to indicate if the datafile should be allowed to automatically extend. If this parameter is set to OFF, the other sizing parameters (below) will be set to zero. next size The size, in bytes, of the area of disk space to allocate to the datafile when more space is required. You may qualify the size value with 'K' and 'M' for kilobytes and megabytes respectively. maxsize size The maximum size, in bytes, to which the datafile should be allowed to extend. You may qualify the size value with 'K' and 'M' for kilobytes and megabytes respectively.
Here is a small example:CREATE TABLESPACE user_data2 DATAFILE 'D:\ORANT\DEV\DATAFILES\USER2_1.ORA' SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE 900M;
The determine if AUTOEXTEND has been configured for a paritcular data file, query the view DBA_DATA_FILES:SELECT file_name "File Name" , autoextensible "Autoextensible?" , increment_by * p.value "Increment By" , maxbytes "Max Bytes" FROM dba_data_files d , (SELECT value FROM v$parameter WHERE name = 'db_block_size') p WHERE file_name = 'D:\ORANT\DEV\DATAFILES\USER1.ORA' / File Name Autoextensible? Increment By Max Bytes --------------------------------- --------------- ------------ ------------ D:\ORANT\DEV\DATAFILES\USER1.ORA YES 104,857,600 943,718,400
The ability to enlarge files was introduced in 7.2 but querying the data dictionary to determine if a datafile has "autoextended" its original size is very different between release 7.2 and 8.x (and higher).
With release 7.2, you had to query the "sys.filext$" internal view. Here is a description of that table back in release 7.2:Name Null? Type ------------------------------- -------- ---- FILE# NOT NULL NUMBER MAXEXTEND NOT NULL NUMBER INC NOT NULL NUMBER where: FILE# : the datafile number as it appears in "V$DATAFILE". MAXEXTEND : the maximum size the file can grow to, expressed in multiples of "DB_BLOCK_SIZE", not in bytes. INC : the size of each increase in the datafile, also in multiples of "DB_BLOCK_SIZE".
NOTE: To get the values in bytes, multiply each number by the "init.ora" parameter "DB_BLOCK_SIZE".
If a datafile in the database has never been created or altered to have "AUTOEXTEND" turned on, and the "sys.filext$" table is queried, the following error will be returned:SQL> select * from sys.filext$; select * from sys.filext$ * ORA-00942: table or view does not exist
This is because "FILEXT$" only gets created the first time you enable the AUTOEXTEND characteristic for a datafile in the database.
If "FILEXT$" does not exist, no datafiles have AUTOEXTEND capabilities.
Starting with Oracle8, the column "AUTOEXTENSIBLE" was added to DBA_DATA_FILES. AUTOEXTENSIBLE will have values 'YES' or 'NO' and it is only available in 8.x versions.
Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.
All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at firstname.lastname@example.org.
I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.