Home » RDBMS Server » Server Administration » Tablespace Allocation
Tablespace Allocation [message #235092] Fri, 04 May 2007 01:17 Go to next message
sweetkhaliq
Messages: 200
Registered: April 2006
Senior Member

I have made the project in which daily entries are 10 to 20.
I have not define any tablespace for my schema. It takes the default system tablespace. Is it necessary to define tablespace for my project. If it is necessary then how can we define table space with size allocation.
Both with sql and oracle utility.
Re: Tablespace Allocation [message #235094 is a reply to message #235092] Fri, 04 May 2007 01:19 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Assign the default tablespace to the user or to the database.
>> Is it necessary to define tablespace for my project
Yes. Do not have custom objects in system tablespace.

[Updated on: Fri, 04 May 2007 01:20]

Report message to a moderator

Re: Tablespace Allocation [message #235095 is a reply to message #235094] Fri, 04 May 2007 01:20 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>Both with sql and oracle utility.
????
Re: Tablespace Allocation [message #235096 is a reply to message #235092] Fri, 04 May 2007 01:22 Go to previous messageGo to next message
sweetkhaliq
Messages: 200
Registered: April 2006
Senior Member

Tell me the procedure of defining tablespace indetail both with sql query and oracle utiliyt for defining tablespace. And how much size should be for this for a small project.

Thanks for reply
Re: Tablespace Allocation [message #235099 is a reply to message #235096] Fri, 04 May 2007 01:27 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>oracle utiliyt
What do you mean by Oracle Utility?
>>And how much size should be for this for a small project.
How small is small for you? You have to decide that.

create your custom tablespace.
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14231/tspaces.htm#sthref1153
Alter your users default tablespace to use this custom tablespae.
sql>alter user xxx default tablespace custom_tablespace;
Re: Tablespace Allocation [message #235131 is a reply to message #235092] Fri, 04 May 2007 02:45 Go to previous messageGo to next message
orasupport
Messages: 11
Registered: May 2007
Junior Member
If you want to use Oracle utility consider-> "Oracle Enterprise Manager" .

Rgds,
Jayesh
Re: Tablespace Allocation [message #235138 is a reply to message #235092] Fri, 04 May 2007 03:02 Go to previous messageGo to next message
sweetkhaliq
Messages: 200
Registered: April 2006
Senior Member

Yes oracle utility means Oracle Enterprise Manager. From oracle enterprise manager we can define the tablespace.

We can also use sql query for defining tablespace. I just want to now both methods.
Please provide me in detail.
thanks
Re: Tablespace Allocation [message #235149 is a reply to message #235099] Fri, 04 May 2007 03:21 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
Quote:
sql>alter user xxx default tablespace custom_tablespace;

Does this work?

sys@NEWDB-SQL>>alter user scott default_tablespace new_users;
alter user scott default_tablespace new_users
                 *
ERROR at line 1:
ORA-00922: missing or invalid option


sys@NEWDB-SQL>>alter user scott identified by tiger default tablespace new_users;

User altered.

Besides, if according to the OP if he has already created his tables in the system tablespace, he will need to use
ALTER TABLE MOVE command to move his tables to the custom created tablespace.
Re: Tablespace Allocation [message #235211 is a reply to message #235149] Fri, 04 May 2007 06:19 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Quote:

Quote:
sql>alter user xxx default tablespace custom_tablespace;

Does this work?

Well, obviously it does.
scott@9i > alter user scott default tablespace tools;

User altered.

This failed, because your syntax was wrong.
Did you check the docs?
Quote:

sys@NEWDB-SQL>>alter user scott default_tablespace new_users;
alter user scott default_tablespace new_users
*
ERROR at line 1:
ORA-00922: missing or invalid option


You are quite correct. ALTER TABLE move will move the existing objects. But all future table will still be created in SYSTEM tablespace if the default tablespace is not changed.
Re: Tablespace Allocation [message #235236 is a reply to message #235211] Fri, 04 May 2007 07:38 Go to previous message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
Yes, I overlooked the pesky syntax error. thanks for pointing it out.
Previous Topic: Slow performance
Next Topic: DBA Studio & Enterprise Manager
Goto Forum:
  


Current Time: Tue Dec 03 12:42:49 CST 2024