Oracle strongly recommended to use the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION
provided with the DBMS_MGMT package in order to move audit trail table out of SYSTEM tablespace. But the procedure SET_AUDIT_TRAIL_LOCATION by default is not available until 11g. It is available in 11g.
However with manual script you can move SYS.AUD$ table out of SYSTEM tablespace. But you need to remember moving AUD$ out of SYSTEM tablespace is not a
supported procedure. Oracle does not support changing ownership of AUD$, or any
triggers on it.
Below is the scripts that you can do as your own risk,
Step 01: Connect to database as SYS user.
conn / as sysdba
Step 02: Create tablespace where audit file will reside.
create tablespace AUDIT_TBS
datafile '/oradata2/datafile/aud01.dbf' size 10M autoextend on maxsize unlimited;
Step 03: Create audit table inside AUDIT_TBS
create table aud_aux tablespace AUDIT_TBS
as select * from aud$ where 1 = 2;
Note that no rows will be created in this state.
Step 04: Rename the original Audit table.
rename AUD$ to AUD$$;
Step 05: Rename the aud_aux to AUD$
rename aud_aux to aud$;
Step 06: Create Index on the AUD$ table.
create index aud_i
on aud$(sessionid, ses$tid)
tablespace AUDIT_TBS;
Showing posts with label TableSpace. Show all posts
Showing posts with label TableSpace. Show all posts
Thursday, January 15, 2009
How to move audit table out of SYSTEM tablespace
Oracle strongly recommended to use the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION
provided with the DBMS_MGMT package in order to move audit trail table out of SYSTEM tablespace. But the procedure SET_AUDIT_TRAIL_LOCATION by default is not available until 11g. It is available in 11g.
However with manual script you can move SYS.AUD$ table out of SYSTEM tablespace. But you need to remember moving AUD$ out of SYSTEM tablespace is not a
supported procedure. Oracle does not support changing ownership of AUD$, or any
triggers on it.
Below is the scripts that you can do as your own risk,
Step 01: Connect to database as SYS user.
conn / as sysdba
Step 02: Create tablespace where audit file will reside.
create tablespace AUDIT_TBS
datafile '/oradata2/datafile/aud01.dbf' size 10M autoextend on maxsize unlimited;
Step 03: Create audit table inside AUDIT_TBS
create table aud_aux tablespace AUDIT_TBS
as select * from aud$ where 1 = 2;
Note that no rows will be created in this state.
Step 04: Rename the original Audit table.
rename AUD$ to AUD$$;
Step 05: Rename the aud_aux to AUD$
rename aud_aux to aud$;
Step 06: Create Index on the AUD$ table.
create index aud_i
on aud$(sessionid, ses$tid)
tablespace AUDIT_TBS;
provided with the DBMS_MGMT package in order to move audit trail table out of SYSTEM tablespace. But the procedure SET_AUDIT_TRAIL_LOCATION by default is not available until 11g. It is available in 11g.
However with manual script you can move SYS.AUD$ table out of SYSTEM tablespace. But you need to remember moving AUD$ out of SYSTEM tablespace is not a
supported procedure. Oracle does not support changing ownership of AUD$, or any
triggers on it.
Below is the scripts that you can do as your own risk,
Step 01: Connect to database as SYS user.
conn / as sysdba
Step 02: Create tablespace where audit file will reside.
create tablespace AUDIT_TBS
datafile '/oradata2/datafile/aud01.dbf' size 10M autoextend on maxsize unlimited;
Step 03: Create audit table inside AUDIT_TBS
create table aud_aux tablespace AUDIT_TBS
as select * from aud$ where 1 = 2;
Note that no rows will be created in this state.
Step 04: Rename the original Audit table.
rename AUD$ to AUD$$;
Step 05: Rename the aud_aux to AUD$
rename aud_aux to aud$;
Step 06: Create Index on the AUD$ table.
create index aud_i
on aud$(sessionid, ses$tid)
tablespace AUDIT_TBS;
Friday, January 9, 2009
How to Resize a Datafile
There may be situations when you need to increase or decrease your datafile size. Prior to oracle 7.2 there was no easy way to resize the datafile. Before 7.2 the solutions was to drop and recreate the tablespace with different sized datafiles, or to add more datafiles to a tablespace whenever you need more space in the tablespace.
Before 7.2 the RESIZE command will raise error,
ORA-00923: FROM keyword not found where expected
Before entering into resize datafile let's be familiar with several views related to datafile.
From V$DATAFILE.CREATION_TIME we can see the timestamp of the datafile creation time.
From V$DATAFILE.BYTES we can see the current datafile size in bytes. 0 in this fields indicate the datafile is inaccessible.
From V$DATAFILE.CREATE_BYTES we can the datafile size when it was created.
From V$DBA_DATA_FILES.MAXBYTES we can see the maximum size of the datafile.
Before going into resize I just create one tablespace containing one datafile of size 10M which can be extended up to 100M.
SQL> create tablespace test_tbs datafile 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\test_tbs01.dbf' size 10M autoextend on maxsize 100M;
Tablespace created.
Now have a look at the current size of maximum size of this data file from dba_data_files view.
SQL> select bytes/1024/1024, maxbytes/1024/1024 from dba_data_files where file_id=6;
BYTES/1024/1024 MAXBYTES/1024/1024
--------------- ------------------
10 100
Increase datafile size
To see the current settings of the datafile query from v$datafile view. The BYTES column shows the current size of the datafile, and the CREATE_BYTES column shows what the size was
specified when the file was created.
Current size is 10M we can increase it upto 15M by,
SQL> alter database datafile 6 resize 15M;
Database altered.
SQL> select bytes/1024/1024, maxbytes/1024/1024 from dba_data_files where file_id=6;
BYTES/1024/1024 MAXBYTES/1024/1024
--------------- ------------------
15 100
SQL> select bytes/1024/1024, create_bytes/1024/1024 from v$datafile where file#=6;
BYTES/1024/1024 CREATE_BYTES/1024/1024
--------------- ----------------------
15 10
Here 12 is current size and 10 is creation time size.
Decrease Datafile size
Downsizing a datafile is more complicated than increasing the size of a datafile. Because you cannot deallocate space from a datafile that is currently being used by database objects. To remove space from a datafile, you have to have contiguous space at the END of the datafile. From DBA_FREE_SPACE we can see the free space in the datafile.
To resize our datafile to 2M issue,
SQL> alter database datafile 6 resize 2M;
If you try to resize a datafile to a size smaller than is needed to contain all the database objects in that datafile, the following error is returned,
ORA-03297: file contains blocks of data beyond requested RESIZE value
Or
ORA-03297: file contains used data beyond requested RESIZE value
Before 7.2 the RESIZE command will raise error,
ORA-00923: FROM keyword not found where expected
Before entering into resize datafile let's be familiar with several views related to datafile.
From V$DATAFILE.CREATION_TIME we can see the timestamp of the datafile creation time.
From V$DATAFILE.BYTES we can see the current datafile size in bytes. 0 in this fields indicate the datafile is inaccessible.
From V$DATAFILE.CREATE_BYTES we can the datafile size when it was created.
From V$DBA_DATA_FILES.MAXBYTES we can see the maximum size of the datafile.
Before going into resize I just create one tablespace containing one datafile of size 10M which can be extended up to 100M.
SQL> create tablespace test_tbs datafile 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\test_tbs01.dbf' size 10M autoextend on maxsize 100M;
Tablespace created.
Now have a look at the current size of maximum size of this data file from dba_data_files view.
SQL> select bytes/1024/1024, maxbytes/1024/1024 from dba_data_files where file_id=6;
BYTES/1024/1024 MAXBYTES/1024/1024
--------------- ------------------
10 100
Increase datafile size
To see the current settings of the datafile query from v$datafile view. The BYTES column shows the current size of the datafile, and the CREATE_BYTES column shows what the size was
specified when the file was created.
Current size is 10M we can increase it upto 15M by,
SQL> alter database datafile 6 resize 15M;
Database altered.
SQL> select bytes/1024/1024, maxbytes/1024/1024 from dba_data_files where file_id=6;
BYTES/1024/1024 MAXBYTES/1024/1024
--------------- ------------------
15 100
SQL> select bytes/1024/1024, create_bytes/1024/1024 from v$datafile where file#=6;
BYTES/1024/1024 CREATE_BYTES/1024/1024
--------------- ----------------------
15 10
Here 12 is current size and 10 is creation time size.
Decrease Datafile size
Downsizing a datafile is more complicated than increasing the size of a datafile. Because you cannot deallocate space from a datafile that is currently being used by database objects. To remove space from a datafile, you have to have contiguous space at the END of the datafile. From DBA_FREE_SPACE we can see the free space in the datafile.
To resize our datafile to 2M issue,
SQL> alter database datafile 6 resize 2M;
If you try to resize a datafile to a size smaller than is needed to contain all the database objects in that datafile, the following error is returned,
ORA-03297: file contains
Or
ORA-03297: file contains used data beyond requested RESIZE value
MAXDATAFILES , DB_FILES parameters and ORA-00059
Problem Description
Whenever I try to create tablespace it fails with error message, ORA-00059.
SQL> create tablespace test_tbs;
create tablespace test_tbs
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded
Cause of The Problem
You have reached the limit of DB_FILES parameter. Before entering into solution part let's have an idea about DB_FILES and MAXDATAFILES parameter.
The DB_FILES parameter limits the maximum number of datafile can exist in oracle database. We can't change this parameter dynamically. We have to change it spfile by using ALTER SYSTEM .... SCOPE=SPFILE or in the pfile.
And the MAXDATAFILES parameter you can find with the CREATE DATABASE command or in CREATE CONTROLFILE command. It is also a limitation of maximum number of datafiles can be in the datafile. But starting from oracle 8 this hard limit parameter can be easily expanded up to DB_FILES parameter. So, if you attempt to add a new file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, the MAXDATAFILES parameter of the control file will expand automatically to accommodate more files.
So, if you are after oracle 8i then we should just forget about MAXDATAFILES parameter. We should rather think about DB_FILES parameter.
Solution of The Problem
If you use pfile to startup the database then edit pfile and add/modify the DB_FILES parameter to a greater value so that it can accommodate higher number of datafiles. Then start the database using that pfile.
If you use spfile to startup the database then issue,
SQL> alter system set db_files=300 scope=spfile;
System altered.
Then start your database and either create tablespace or add datafile to an existing tablespace. Hopefully it will work.
SQL> conn / as sysdba
SQL> startup force
SQL> CREATE TABLESPACE or ALTER TABLESPACE command to add datafile.
Whenever I try to create tablespace it fails with error message, ORA-00059.
SQL> create tablespace test_tbs;
create tablespace test_tbs
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded
Cause of The Problem
You have reached the limit of DB_FILES parameter. Before entering into solution part let's have an idea about DB_FILES and MAXDATAFILES parameter.
The DB_FILES parameter limits the maximum number of datafile can exist in oracle database. We can't change this parameter dynamically. We have to change it spfile by using ALTER SYSTEM .... SCOPE=SPFILE or in the pfile.
And the MAXDATAFILES parameter you can find with the CREATE DATABASE command or in CREATE CONTROLFILE command. It is also a limitation of maximum number of datafiles can be in the datafile. But starting from oracle 8 this hard limit parameter can be easily expanded up to DB_FILES parameter. So, if you attempt to add a new file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, the MAXDATAFILES parameter of the control file will expand automatically to accommodate more files.
So, if you are after oracle 8i then we should just forget about MAXDATAFILES parameter. We should rather think about DB_FILES parameter.
Solution of The Problem
If you use pfile to startup the database then edit pfile and add/modify the DB_FILES parameter to a greater value so that it can accommodate higher number of datafiles. Then start the database using that pfile.
If you use spfile to startup the database then issue,
SQL> alter system set db_files=300 scope=spfile;
System altered.
Then start your database and either create tablespace or add datafile to an existing tablespace. Hopefully it will work.
SQL> conn / as sysdba
SQL> startup force
SQL> CREATE TABLESPACE or ALTER TABLESPACE command to add datafile.
How to Resize a Datafile
There may be situations when you need to increase or decrease your datafile size. Prior to oracle 7.2 there was no easy way to resize the datafile. Before 7.2 the solutions was to drop and recreate the tablespace with different sized datafiles, or to add more datafiles to a tablespace whenever you need more space in the tablespace.
Before 7.2 the RESIZE command will raise error,
ORA-00923: FROM keyword not found where expected
Before entering into resize datafile let's be familiar with several views related to datafile.
From V$DATAFILE.CREATION_TIME we can see the timestamp of the datafile creation time.
From V$DATAFILE.BYTES we can see the current datafile size in bytes. 0 in this fields indicate the datafile is inaccessible.
From V$DATAFILE.CREATE_BYTES we can the datafile size when it was created.
From V$DBA_DATA_FILES.MAXBYTES we can see the maximum size of the datafile.
Before going into resize I just create one tablespace containing one datafile of size 10M which can be extended up to 100M.
SQL> create tablespace test_tbs datafile 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\test_tbs01.dbf' size 10M autoextend on maxsize 100M;
Tablespace created.
Now have a look at the current size of maximum size of this data file from dba_data_files view.
SQL> select bytes/1024/1024, maxbytes/1024/1024 from dba_data_files where file_id=6;
BYTES/1024/1024 MAXBYTES/1024/1024
--------------- ------------------
10 100
Increase datafile size
To see the current settings of the datafile query from v$datafile view. The BYTES column shows the current size of the datafile, and the CREATE_BYTES column shows what the size was
specified when the file was created.
Current size is 10M we can increase it upto 15M by,
SQL> alter database datafile 6 resize 15M;
Database altered.
SQL> select bytes/1024/1024, maxbytes/1024/1024 from dba_data_files where file_id=6;
BYTES/1024/1024 MAXBYTES/1024/1024
--------------- ------------------
15 100
SQL> select bytes/1024/1024, create_bytes/1024/1024 from v$datafile where file#=6;
BYTES/1024/1024 CREATE_BYTES/1024/1024
--------------- ----------------------
15 10
Here 12 is current size and 10 is creation time size.
Decrease Datafile size
Downsizing a datafile is more complicated than increasing the size of a datafile. Because you cannot deallocate space from a datafile that is currently being used by database objects. To remove space from a datafile, you have to have contiguous space at the END of the datafile. From DBA_FREE_SPACE we can see the free space in the datafile.
To resize our datafile to 2M issue,
SQL> alter database datafile 6 resize 2M;
If you try to resize a datafile to a size smaller than is needed to contain all the database objects in that datafile, the following error is returned,
ORA-03297: file contains blocks of data beyond requested RESIZE value
Or
ORA-03297: file contains used data beyond requested RESIZE value
Before 7.2 the RESIZE command will raise error,
ORA-00923: FROM keyword not found where expected
Before entering into resize datafile let's be familiar with several views related to datafile.
From V$DATAFILE.CREATION_TIME we can see the timestamp of the datafile creation time.
From V$DATAFILE.BYTES we can see the current datafile size in bytes. 0 in this fields indicate the datafile is inaccessible.
From V$DATAFILE.CREATE_BYTES we can the datafile size when it was created.
From V$DBA_DATA_FILES.MAXBYTES we can see the maximum size of the datafile.
Before going into resize I just create one tablespace containing one datafile of size 10M which can be extended up to 100M.
SQL> create tablespace test_tbs datafile 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\test_tbs01.dbf' size 10M autoextend on maxsize 100M;
Tablespace created.
Now have a look at the current size of maximum size of this data file from dba_data_files view.
SQL> select bytes/1024/1024, maxbytes/1024/1024 from dba_data_files where file_id=6;
BYTES/1024/1024 MAXBYTES/1024/1024
--------------- ------------------
10 100
Increase datafile size
To see the current settings of the datafile query from v$datafile view. The BYTES column shows the current size of the datafile, and the CREATE_BYTES column shows what the size was
specified when the file was created.
Current size is 10M we can increase it upto 15M by,
SQL> alter database datafile 6 resize 15M;
Database altered.
SQL> select bytes/1024/1024, maxbytes/1024/1024 from dba_data_files where file_id=6;
BYTES/1024/1024 MAXBYTES/1024/1024
--------------- ------------------
15 100
SQL> select bytes/1024/1024, create_bytes/1024/1024 from v$datafile where file#=6;
BYTES/1024/1024 CREATE_BYTES/1024/1024
--------------- ----------------------
15 10
Here 12 is current size and 10 is creation time size.
Decrease Datafile size
Downsizing a datafile is more complicated than increasing the size of a datafile. Because you cannot deallocate space from a datafile that is currently being used by database objects. To remove space from a datafile, you have to have contiguous space at the END of the datafile. From DBA_FREE_SPACE we can see the free space in the datafile.
To resize our datafile to 2M issue,
SQL> alter database datafile 6 resize 2M;
If you try to resize a datafile to a size smaller than is needed to contain all the database objects in that datafile, the following error is returned,
ORA-03297: file contains
Or
ORA-03297: file contains used data beyond requested RESIZE value
MAXDATAFILES , DB_FILES parameters and ORA-00059
Problem Description
Whenever I try to create tablespace it fails with error message, ORA-00059.
SQL> create tablespace test_tbs;
create tablespace test_tbs
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded
Cause of The Problem
You have reached the limit of DB_FILES parameter. Before entering into solution part let's have an idea about DB_FILES and MAXDATAFILES parameter.
The DB_FILES parameter limits the maximum number of datafile can exist in oracle database. We can't change this parameter dynamically. We have to change it spfile by using ALTER SYSTEM .... SCOPE=SPFILE or in the pfile.
And the MAXDATAFILES parameter you can find with the CREATE DATABASE command or in CREATE CONTROLFILE command. It is also a limitation of maximum number of datafiles can be in the datafile. But starting from oracle 8 this hard limit parameter can be easily expanded up to DB_FILES parameter. So, if you attempt to add a new file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, the MAXDATAFILES parameter of the control file will expand automatically to accommodate more files.
So, if you are after oracle 8i then we should just forget about MAXDATAFILES parameter. We should rather think about DB_FILES parameter.
Solution of The Problem
If you use pfile to startup the database then edit pfile and add/modify the DB_FILES parameter to a greater value so that it can accommodate higher number of datafiles. Then start the database using that pfile.
If you use spfile to startup the database then issue,
SQL> alter system set db_files=300 scope=spfile;
System altered.
Then start your database and either create tablespace or add datafile to an existing tablespace. Hopefully it will work.
SQL> conn / as sysdba
SQL> startup force
SQL> CREATE TABLESPACE or ALTER TABLESPACE command to add datafile.
Whenever I try to create tablespace it fails with error message, ORA-00059.
SQL> create tablespace test_tbs;
create tablespace test_tbs
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded
Cause of The Problem
You have reached the limit of DB_FILES parameter. Before entering into solution part let's have an idea about DB_FILES and MAXDATAFILES parameter.
The DB_FILES parameter limits the maximum number of datafile can exist in oracle database. We can't change this parameter dynamically. We have to change it spfile by using ALTER SYSTEM .... SCOPE=SPFILE or in the pfile.
And the MAXDATAFILES parameter you can find with the CREATE DATABASE command or in CREATE CONTROLFILE command. It is also a limitation of maximum number of datafiles can be in the datafile. But starting from oracle 8 this hard limit parameter can be easily expanded up to DB_FILES parameter. So, if you attempt to add a new file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, the MAXDATAFILES parameter of the control file will expand automatically to accommodate more files.
So, if you are after oracle 8i then we should just forget about MAXDATAFILES parameter. We should rather think about DB_FILES parameter.
Solution of The Problem
If you use pfile to startup the database then edit pfile and add/modify the DB_FILES parameter to a greater value so that it can accommodate higher number of datafiles. Then start the database using that pfile.
If you use spfile to startup the database then issue,
SQL> alter system set db_files=300 scope=spfile;
System altered.
Then start your database and either create tablespace or add datafile to an existing tablespace. Hopefully it will work.
SQL> conn / as sysdba
SQL> startup force
SQL> CREATE TABLESPACE or ALTER TABLESPACE command to add datafile.
Limitations When Adding Datafiles to a Tablespace
Before discussing limitations of adding datafiles to a tablespace let's think about DB_FILES parameter. DB_FILES is an initialization parameter in oracle. When an oracle instance starts, this parameter indicates the amount of SGA space to reserve for datafile information and thus, the maximum number of datafiles that can be created for the instance. This parameter exists throughout the life cycle of the instance. So, changes to DB_FILES affect only after restarting the instance.
As this parameter indicates the amount of SGA space to reserve for datafile information the bigger value settings indicates memory is unnecessarily consumed.
And smaller value setting of this parameter impose limit to create another datafile.
So setting of DB_FILES parameter value is important.
Now let's look about possible limitations when adding datafiles to a tablespace.
Possible Limitations When Adding Datafiles to a Tablespace
•OS imposes a limit of the number of files a process can be opened at a time.
•OS imposes a limit of number and size of datafiles.
•The database imposes a maximum limit on the number of datafiles for any Oracle Database opened by any instance. This limit is operating system specific.
•Number of datafiles can't exceed DB_FILES initialization parameter.
•It is good to know that after oracle 8i the MAXDATAFILES parameter (of CREATE CONTROLFILE or CREATE DATABASE command) is not responsible of the limitations of adding datafiles.
•If you attempt to add a new file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, the MAXDATAFILES parameter of the control file will expand automatically to accommodate more files.
As this parameter indicates the amount of SGA space to reserve for datafile information the bigger value settings indicates memory is unnecessarily consumed.
And smaller value setting of this parameter impose limit to create another datafile.
So setting of DB_FILES parameter value is important.
Now let's look about possible limitations when adding datafiles to a tablespace.
Possible Limitations When Adding Datafiles to a Tablespace
•OS imposes a limit of the number of files a process can be opened at a time.
•OS imposes a limit of number and size of datafiles.
•The database imposes a maximum limit on the number of datafiles for any Oracle Database opened by any instance. This limit is operating system specific.
•Number of datafiles can't exceed DB_FILES initialization parameter.
•It is good to know that after oracle 8i the MAXDATAFILES parameter (of CREATE CONTROLFILE or CREATE DATABASE command) is not responsible of the limitations of adding datafiles.
•If you attempt to add a new file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, the MAXDATAFILES parameter of the control file will expand automatically to accommodate more files.
Limitations When Adding Datafiles to a Tablespace
Before discussing limitations of adding datafiles to a tablespace let's think about DB_FILES parameter. DB_FILES is an initialization parameter in oracle. When an oracle instance starts, this parameter indicates the amount of SGA space to reserve for datafile information and thus, the maximum number of datafiles that can be created for the instance. This parameter exists throughout the life cycle of the instance. So, changes to DB_FILES affect only after restarting the instance.
As this parameter indicates the amount of SGA space to reserve for datafile information the bigger value settings indicates memory is unnecessarily consumed.
And smaller value setting of this parameter impose limit to create another datafile.
So setting of DB_FILES parameter value is important.
Now let's look about possible limitations when adding datafiles to a tablespace.
Possible Limitations When Adding Datafiles to a Tablespace
•OS imposes a limit of the number of files a process can be opened at a time.
•OS imposes a limit of number and size of datafiles.
•The database imposes a maximum limit on the number of datafiles for any Oracle Database opened by any instance. This limit is operating system specific.
•Number of datafiles can't exceed DB_FILES initialization parameter.
•It is good to know that after oracle 8i the MAXDATAFILES parameter (of CREATE CONTROLFILE or CREATE DATABASE command) is not responsible of the limitations of adding datafiles.
•If you attempt to add a new file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, the MAXDATAFILES parameter of the control file will expand automatically to accommodate more files.
As this parameter indicates the amount of SGA space to reserve for datafile information the bigger value settings indicates memory is unnecessarily consumed.
And smaller value setting of this parameter impose limit to create another datafile.
So setting of DB_FILES parameter value is important.
Now let's look about possible limitations when adding datafiles to a tablespace.
Possible Limitations When Adding Datafiles to a Tablespace
•OS imposes a limit of the number of files a process can be opened at a time.
•OS imposes a limit of number and size of datafiles.
•The database imposes a maximum limit on the number of datafiles for any Oracle Database opened by any instance. This limit is operating system specific.
•Number of datafiles can't exceed DB_FILES initialization parameter.
•It is good to know that after oracle 8i the MAXDATAFILES parameter (of CREATE CONTROLFILE or CREATE DATABASE command) is not responsible of the limitations of adding datafiles.
•If you attempt to add a new file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, the MAXDATAFILES parameter of the control file will expand automatically to accommodate more files.
Subscribe to:
Posts (Atom)
