| Navi |
Posted: Aug 08, 2007 12:14:35 PM |
Total Post: 60
Joined: Jun, 2007
|
Hi,
Please help me in this matter i am getting really confused.
I have created two tablespaces:-
Tbsnav11 is correct
Tbsnav12 is wrong
First
SQL> create tablespace Tbsnav11
2 datafile 'C:\oracle\oradata\Chandu\dbfnav11.dbf' size 3m
3 default storage
4 (
5 initial 10k
6 next 20k
7 pctincrease 50
8 );
Tablespace created.
SQL> select extent_management from dba_tablespaces where tablespace_name='TBSNAV11';
EXTENT_MAN
----------
LOCAL
Second:-
SQL> create tablespace Tbsnav12
2 datafile 'C:\oracle\oradata\Chandu\dbfnav12.dbf' size 3m
3 extent management local
4 default storage
5 (
6 initial 10k
7 next 20k
8 pctincrease 50
9 );
create tablespace tbsnav12
*
ERROR at line 1:
ORA-25143: default storage clause is not compatible with allocation policy
While browsing I saw that in Locally Managed Tablespace , you cannot specify DEFAULT STORAGE, MINIMUM EXTENT or TEMPORARY.
Question:-
In oracle9i even though if we don’t specify extent management it is LOCAL then while creating tablespace Tbsnav11 why it is not giving error.
And while creating tablespace Tbsnav12 why it is giving error.
Regards,
Naveen |
|
|
 Mohammed Taj |
| Posted: Aug 08, 2007 12:39:22 PM |  |
|
Total Post: 694
Joined: Jul, 2007
|
Hi,
ORA-25143: default storage clause is not compatible with allocation policy
YOu cann't specify BOTH LOCAL or DICTIONARY management for tablespace.
4.When we not specify explicity "EXTENT MANAGEMENT CLAUSE" then database determines extent management as fellows.
1.If we omit DEFAULT storage clause in create tablespace statement then database create tablespace in "Locally Managaned + Autoallocated".
create tablespace &tablespace
datafile 'path' size xxxk;
2.If we specify DEFAULT storage clause then
1.If MINIMUN EXTENT + INITIAL + NEXT are EQUAL AND PCTINCREASE is 0.then database create "Locally Managed + Uniform".
SQL> create tablespace TEST
2 datafile 'c:\test.dbf' size 2m
3 default storage (
4 initial 100k
5 next 100k
6 pctincrease 0);
Tablespace created.
|
|
|
|
|
| Mohammed Taj |
| Posted: Aug 08, 2007 01:03:58 PM | |
|
Total Post: 694
Joined: Jul, 2007
|
ORA-25143: default storage clause is not compatible with allocation policy
Cause: default storage clause was specified for a tablespace with AUTOALLOCATE or UNIFORM policy
Action: Omit the storage clause
|
|
|
|
|
| Navi |
| Posted: Aug 08, 2007 01:26:19 PM | |
|
Total Post: 60
Joined: Jun, 2007
|
Hi Taj,
Sorry. I am still having lit bit of doubts. Please clarify it.
You said:-YOu cann't specify BOTH LOCAL or DICTIONARY management for tablespace.
Dictionary Managed Tablespace has these parameters:-
Default Storage ie
Initial, Next, PctIncrease, MinExtent, MaxExtent
Locally Managed Tablespace has these parameters:-
Uniform, Autoallocate
Q1) If default storage clause is specified while creating tablespace means it is Dictionary
Managed otherwise it is Locally Managed. Is this statement correct.
Q2) Why it is showing LOCAL in Extent_Management when i created tablespace Tbsnav11.
Q3) Please tell me if i have leftout any Dictionary and Locally Managed Parameters.
Waiting for reply.
Regards,
Naveen
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
