| tablespace parameter [message #128928] |
Thu, 21 July 2005 05:00  |
khroner
Messages: 11
Registered: June 2005
|
Junior Member |
|
|
hi,
Oracle version 9i windows.
I had manually create the db with scripts from command prompt.
And in the CreateDBFiles.sql I
set the below parameter to create the table space "DRSYS"
CREATE TABLESPACE "DRSYS" LOGGING DATAFILE '%ORACLE_DATA%\%ORACLE_SID%\drsys01.dbf' SIZE 25M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 1000M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 8K;
Now I want to make this tablespace "DRSYS" to
SIZE 25M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
is it possbile to do it without removeing the database?
thanx.
cheers
sam
|
|
|
|
| Re: tablespace parameter [message #128961 is a reply to message #128928] |
Thu, 21 July 2005 06:59  |
tarundua
Messages: 1080
Registered: June 2005
Location: India
|
Senior Member |
|
|
Hi sam,
As i know SIZE, AUTOEXTEND, MAXSIZE parameters are set at datafile level and EXTENT MANAGEMENT , SEGMENT SPACE MANAGEMENT are set at tablespace level. So u have to use separate commands to achieve this. Try this out ---
Datafile ---
SQL> alter database datafile
2 'D:\ORACLE\ORADATA\TANUDB1\DRSYS01.DBF' resize 25M;
Database altered.
SQL> alter database datafile
2 'D:\ORACLE\ORADATA\TANUDB1\DRSYS01.DBF'
3 autoextend on next 640k Maxsize unlimited;
Database altered.
Tablespace ---
Extent management wil remain LOCAL because its already local so no need to do that.
And by default 9i tablespaces have segment space management auto , u can check it out with the help of following command.
SQL> select tablespace_name,block_size,extent_management,allocation_type,segment_space_management from dba_tablespaces where tablespace_name='DRSYS';
TABLESPACE_NAME BLOCK_SIZE EXTENT_MAN ALLOCATIO SEGMENT
------------------------------ ---------- ---------- --------- ------
DRSYS 8192 LOCAL SYSTEM AUTO
Regards,
Tarun
|
|
|
|
| Re: tablespace parameter [message #128979 is a reply to message #128928] |
Thu, 21 July 2005 07:49 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
|
Excellent answer by Tarun above, just wanted to add that I thought that in 9i ASSM was not by default, that this is new in 10gR2. Either way can run the query shown above to find out and then alter the tablespace if needed.
|
|
|
|
|
|
| Re: tablespace parameter [message #129122 is a reply to message #128961] |
Fri, 22 July 2005 01:26 |
khroner
Messages: 11
Registered: June 2005
|
Junior Member |
|
|
hi tarun,
i tried the points you mentioned but doesn't seem to change .
SQL> alter database datafile 'C:\oracle\oradata\mydb\DRSYS01.DBF' resize 25M;
Database altered.
SQL> alter database datafile 'C:\oracle\oradata\mydb\DRSYS01.DBF' autoextend on next 640k Maxsi
ze unlimited;
Database altered.
when i run below sql it shows as
SQL>
select tablespace_name,block_size,extent_management,allocation_type,segment_space_management from
dba_tablespaces where tablespace_name='DRSYS';
TABLESPACE_NAME BLOCK_SIZE EXTENT_MAN ALLOCATIO SEGMEN
------------------------------ ---------- ---------- --------- ------
DRSYS 8192 LOCAL UNIFORM MANUAL
thanx.
cheers
sam
|
|
|
|
| Re: tablespace parameter [message #129132 is a reply to message #129122] |
Fri, 22 July 2005 01:58 |
tarundua
Messages: 1080
Registered: June 2005
Location: India
|
Senior Member |
|
|
Hi Sam,
I think all the issues like
-- autoextend
-- maxsize etc is over.
Now u want to switch ur tablespace from manual to automatic segment space management, if i m getting it correct.
Unfortunately, u can't perform this task with the help of 'ALTER TABLESPACE' command. But if u really want to do then u can create a new tablespace with segment space management-- auto and move the objects to this new tablespace.
SQL> ALTER OBJECT obj_name TABLESPACE tabls_name;
or u can use EXPORT/IMPORT for the same purpose. So no need to remove the database.
Regards,
Tarun
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
