Solved

Create tablespace

Posted on 2011-02-23
12
901 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
 
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
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: 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 36

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.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

707 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

14 Experts available now in Live!

Get 1:1 Help Now