OraFAQ Forum: Server Administration » Tablespace Allocation

Home » RDBMS Server » Server Administration » Tablespace Allocation

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Tablespace Allocation [message #235092]

Fri, 04 May 2007 01:17

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.

Report message to a moderator

Re: Tablespace Allocation [message #235094 is a reply to message #235092]

Fri, 04 May 2007 01:19

Mahesh Rajendran
Messages: 10707
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

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>>Both with sql and oracle utility.
????

Report message to a moderator

Re: Tablespace Allocation [message #235096 is a reply to message #235092]

Fri, 04 May 2007 01:22

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

Report message to a moderator

Re: Tablespace Allocation [message #235099 is a reply to message #235096]

Fri, 04 May 2007 01:27

Mahesh Rajendran
Messages: 10707
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;

Report message to a moderator

Re: Tablespace Allocation [message #235131 is a reply to message #235092]

Fri, 04 May 2007 02:45

orasupport
Messages: 11
Registered: May 2007

Junior Member

If you want to use Oracle utility consider-> "Oracle Enterprise Manager" .

Rgds,
Jayesh

Report message to a moderator

Re: Tablespace Allocation [message #235138 is a reply to message #235092]

Fri, 04 May 2007 03:02

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

Report message to a moderator

Re: Tablespace Allocation [message #235149 is a reply to message #235099]

Fri, 04 May 2007 03:21

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.

Report message to a moderator

Re: Tablespace Allocation [message #235211 is a reply to message #235149]

Fri, 04 May 2007 06:19

Mahesh Rajendran
Messages: 10707
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.

Report message to a moderator

Re: Tablespace Allocation [message #235236 is a reply to message #235211]

Fri, 04 May 2007 07:38

saibal
Messages: 111
Registered: March 2007
Location: India

Senior Member

Yes, I overlooked the pesky syntax error. thanks for pointing it out.

Report message to a moderator

Previous Topic:	Slow performance
Next Topic:	DBA Studio & Enterprise Manager

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Sep 19 23:06:49 CDT 2024