Solved

Create tablespace

Posted on 2011-02-23
12
904 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 76

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 76

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 73

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 76

Expert Comment

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

that example is different from mine how?
0
 
LVL 73

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 76

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 76

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

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.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

813 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now