Solved

Create tablespace

Posted on 2011-02-23
12
909 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 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 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 77

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 77

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 77

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 37

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 77

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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 how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

820 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