| user management [message #509364] |
Fri, 27 May 2011 06:35  |
venkatgargeyagmailcom
Messages: 20
Registered: October 2010
Location: hyderabad
|
Junior Member |
|
|
hi,
i have a tablespace which contains 121 datafile(max limit reached)
as a dba what we have to do?
creating a new tablespace with a datafile and assign the users to the current tablespace which i created now.is this a correct solution?
if the above process is correct,after some time the tablespace which was filled up got freed up.now can i give the access to the users previous (i.e. freed up tablespace) and current tablespaces
|
|
|
|
|
|
|
|
| Re: user management [message #509372 is a reply to message #509367] |
Fri, 27 May 2011 07:02  |
venkatgargeyagmailcom
Messages: 20
Registered: October 2010
Location: hyderabad
|
Junior Member |
|
|
if u take the trace of the controlfile in that trace file u will find the max datafiles that db can have.in 10g a tablespace can have a max of 121 datafile by default
the above situation which was asked by interviewer i said the above answer.i am checking is that correct or not?
[Updated on: Fri, 27 May 2011 07:04] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: user management [message #509383 is a reply to message #509372] |
Fri, 27 May 2011 07:27 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Hi - I think your interviewer was not testing what you think he was testing: he was testing whether you know that there is no such limit as 121 datafiles per tablespace. The limit is in fact over a thousand.
Mahesh - the controlfile setting for MAXDATAFILES is no longer meaningful. If you test it (I last tested it in 10.1, I think), you'll find that you can create as many datafiles as you want, up to the db_files parameter limit, when you get an ora-59.
|
|
|
|
| Re: user management [message #509388 is a reply to message #509383] |
Fri, 27 May 2011 07:34 |
 |
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
|
Senior Member |
|
|
Thank god finally ....
Hope at-least Now OP will read the links.
C:\Windows\System32>sqlplus system/tejajun20
SQL*Plus: Release 11.2.0.1.0 Production on Fri May 27 20:20:19 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select value from v$parameter where name = 'db_files';
VALUE
--------------------------------------------------------------------------------
10
SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';
RECORDS_TOTAL
-------------
100
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
D:\APP1\SRIRAM\ORADATA\ORAFAQ\USERS01.DBF
D:\APP1\SRIRAM\ORADATA\ORAFAQ\UNDOTBS01.DBF
D:\APP1\SRIRAM\ORADATA\ORAFAQ\SYSAUX01.DBF
D:\APP1\SRIRAM\ORADATA\ORAFAQ\SYSTEM01.DBF
D:\APP1\SRIRAM\ORADATA\ORAFAQ\EXAMPLE01.DBF
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User2.dbf' size 100m ;
Tablespace altered.
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User3.dbf' size 100m ;
Tablespace altered.
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User4.dbf' size 100m ;
Tablespace altered.
SQL> select count(*) from v$datafile;
COUNT(*)
----------
8
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User5.dbf' size 100m ;
Tablespace altered.
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User6.dbf' size 100m ;
Tablespace altered.
SQL> select count(*) from v$datafile;
COUNT(*)
----------
10
SQL> select value from v$parameter where name = 'db_files';
VALUE
--------------------------------------------------------------------------------
10
SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';
RECORDS_TOTAL
-------------
100
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User7.dbf' size 100m ;
alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User7.dbf' size 100m
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded
SQL> alter system set db_files=20 scope=spfile;
System altered.
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 401743872 bytes
Fixed Size 1374892 bytes
Variable Size 268436820 bytes
Database Buffers 125829120 bytes
Redo Buffers 6103040 bytes
Database mounted.
Database opened.
SQL> select value from v$parameter where name = 'db_files';
VALUE
--------------------------------------------------------------------------------
20
SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';
RECORDS_TOTAL
-------------
100
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User7.dbf' size 100m ;
Tablespace altered.
SQL>
SQL> alter system set db_files=150 scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 401743872 bytes
Fixed Size 1374892 bytes
Variable Size 268436820 bytes
Database Buffers 125829120 bytes
Redo Buffers 6103040 bytes
Database mounted.
Database opened.
SQL> select value from v$parameter where name = 'db_files';
VALUE
--------------------------------------------------------------------------------
150
SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';
RECORDS_TOTAL
-------------
100
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User8.dbf' size 1m ;
Tablespace altered.
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User9.dbf' size 1m ;
Tablespace altered.
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User10.dbf' size 1m ;
Tablespace altered.
SQL> select count(*) from v$datafile;
COUNT(*)
----------
14
--- In another window I am adding datafiles upto the limit "100"
SQL> /
COUNT(*)
----------
48
SQL> /
COUNT(*)
----------
55
SQL> /
COUNT(*)
----------
82
SQL> /
COUNT(*)
----------
98
SQL> /
COUNT(*)
----------
98
SQL> /
COUNT(*)
----------
98
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User95.dbf' size 1m ;
Tablespace altered.
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User96.dbf' size 1m ;
Tablespace altered.
SQL>
SQL> select value from v$parameter where name = 'db_files';
VALUE
--------------------------------------------------------------------------------
150
SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';
RECORDS_TOTAL
-------------
100
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User97.dbf' size 1m ;
Tablespace altered.
SQL> select value from v$parameter where name = 'db_files';
VALUE
--------------------------------------------------------------------------------
150
SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';
RECORDS_TOTAL
-------------
200
----Observe it Automatically changed..
SQL>
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams049.htm#REFRN10039
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/dfiles.htm#sthref1343
Quote:Consider Possible Limitations When Adding Datafiles to a Tablespace
You can add datafiles to traditional smallfile tablespaces, subject to the following limitations:
Operating systems often impose a limit on the number of files a process can open simultaneously. More datafiles cannot be created when the operating system limit of open files is reached.
Operating systems impose limits on the 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.
You cannot exceed the number of datafiles specified by the DB_FILES initialization parameter.
When you issue CREATE DATABASE or CREATE CONTROLFILE statements, the MAXDATAFILES parameter specifies an initial size of the datafile portion of the control file. However, if you attempt to add a new file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, the control file will expand automatically so that the datafiles section can accommodate more files. Quote:DB_FILES specifies the maximum number of database files that can be opened for this database.
The maximum valid value is the maximum number of files, subject to operating system constraint,
that will ever be specified for the database, including files to be added by ADD DATAFILE statements.
If you increase the value of DB_FILES, then you must shut down and restart all instances
accessing the database before the new value can take effect. If you have a primary and standby database,
then they should have the same value for this parameter.
Sriram 
[Updated on: Fri, 27 May 2011 11:43] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
