[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Create tablespace

Posted on 2011-02-23
12
Medium Priority
?
930 Views
Last Modified: 2013-12-19
Hey how do I create a oracle tablespace with initial size as 10gb and expandable to 50gb. Can I have the command for it. I really appreciate your time and help
0
Comment
Question by:hussainkhan22
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 34963370
THE DOCS (the docs,the docs,the docs,the docs,the docs):
http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_7003.htm#i2202468

Everything you need to know is in the docs.

example from the docs.  Just change your values.

CREATE TABLESPACE tbs_02
   DATAFILE 'diskb:tbs_f5.dbf' SIZE 500K REUSE
   AUTOEXTEND ON NEXT 500K MAXSIZE 100M;
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34963382
It's even simple to find in the docs.

Two second search:
http://www.oracle.com/pls/db112/search?remark=quick_search&word=create+tablespace
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34963506
Note,  you don't create tablespaces of specific size.

Tablespaces are a logical construct.

You create (as illustrated above) datafiles with specific sizes.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 18

Expert Comment

by:sventhan
ID: 34963516
http://psoug.org/reference/tablespaces.html

CREATE TABLESPACE your_tbs_name LOGGING
DATAFILE '/u01/oradata/' SIZE 10G  ------------
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34963525
sventhan,

that example is different from mine how?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34963555
If you're using OMF, you don't need to specify the file name and location, only the parameters.
If you don't want to create a single 50G datafile, you can create the tablespace with multiple files.
In this example it would be 5 datafiles that can each grow to 10Gb from an initial size of 2 each

CREATE TABLESPACE yourtablespace DATAFILE
   SIZE 2G AUTOEXTEND ON NEXT 256M MAXSIZE 10G,
   SIZE 2G AUTOEXTEND ON NEXT 256M MAXSIZE 10G,
   SIZE 2G AUTOEXTEND ON NEXT 256M MAXSIZE 10G,
   SIZE 2G AUTOEXTEND ON NEXT 256M MAXSIZE 10G,
   SIZE 2G AUTOEXTEND ON NEXT 256M MAXSIZE 10G
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
0
 
LVL 18

Expert Comment

by:sventhan
ID: 34963571
@ slightwv

< that example is different from mine how?

Why do you think its same ;)

Is SEGMENT SPACE MANAGEMENT AUTO a default?  Its a different link.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34963605
>>Is SEGMENT SPACE MANAGEMENT AUTO a default?  Its a different link.

The docs apply to all.  Excerpt form the link I posted:

This setting is called automatic segment-space management and is the default.

>>Its a different link.

and?  Still basically a duplicate example from a previous post and didn't really add anything new to the question.
0
 
LVL 18

Expert Comment

by:sventhan
ID: 34963647
Agreed. No points for me.
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 34965221
get toad and click on 'create tablespace'
the dba version of toad that is :)

no one posted that comment yet :)

0
 

Expert Comment

by:khadermoinuddin
ID: 34969954
try this syntax i am opening it will work

CREATE TABLESPACE users
   DATAFILE 'diskb:users.dbf' SIZE 10g
   AUTOEXTEND ON
NEXT 2G MAXSIZE 50G;
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34970083
khadermoinuddin,

The 'exact' datafile syntax will be based on how the database is set up.  Also, the size parameters should be straight forward from the many examples above.

also in your post:  'diskb:users.dbf' looks like it might have come from the documentation since Oracle likes to use it:  re:the example I posted above from the docs.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question