Solved

create user tablespace

Posted on 2008-10-23
9
505 Views
Last Modified: 2013-12-18
In my database there is no user tablespace and I want to create that as the default tablespace.
In a database in terabyte range...What is the ideal way to create a user tablespace..
Thanks in advance
0
Comment
Question by:dbaseek
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
9 Comments
 
LVL 2

Expert Comment

by:barfi
ID: 22790272
Here is how you create users tablespace in a terabyte database in 10g :

Creating a Bigfile Tablespace: Example

The following example creates a bigfile tablespace bigtbs_01 with a datafile bigtbs_f1.dat of 10 MB:

CREATE BIGFILE TABLESPACE bigtbs_01
  DATAFILE 'bigtbs_f1.dat'
  SIZE 20M AUTOEXTEND ON;

A bigfile tablespace contains only one datafile or tempfile, which can contain up to 232 or 4G blocks. The maximum size of the single datafile or tempfile is 128 terabytes (TB) for a tablespace with 32K blocks and 32TB for a tablespace with 8K blocks
0
 
LVL 2

Expert Comment

by:barfi
ID: 22790282
I am sorry I did not pay attention to what db version are your running?
0
 

Author Comment

by:dbaseek
ID: 22790641
Sir why here datafile ext is dat ok and where can set the path of that file
which location it should be created for example
C:\oracle\product\10.2.0\oradata\
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:dbaseek
ID: 22790690
CREATE BIGFILE TABLESPACE bigtbs_01
  DATAFILE 'bigtbs_f1.dat'
  SIZE 20M AUTOEXTEND ON;
Tablespace created
ALTER DATABASE DEFAULT TABLESPACE bigtbs_01;
database altered
my question now is where is this bigtbs_01 located
0
 
LVL 2

Expert Comment

by:barfi
ID: 22790906
It should be with rest of your other db files. Do followin query to see the path; You can also give your own path while creating.
select * from v$datafile;
and look under Name column it should show you all the paths to all the datafiles.
0
 
LVL 2

Expert Comment

by:barfi
ID: 22790928
Oh !did you mean tablespace or datafile. Tablespaces store datafiles.
I just went back to your question.
0
 
LVL 2

Accepted Solution

by:
barfi earned 500 total points
ID: 22790950
If you are asking the location of the tablespace, here is your answer:

A tablespace is a logical storage unit within an Oracle database. It is logical because a tablespace is not visible in the file system of the machine on which the database resides. A tablespace, in turn, consists of at least one datafile or more datafiles which, in turn, are physically located in the file system of the server. Btw, a datafile belongs to exactly one tablespace.
0
 

Author Comment

by:dbaseek
ID: 22791279
thanks everything is perfect only thing is why we are using dat ext instead of dbf
0
 
LVL 2

Expert Comment

by:barfi
ID: 22797307
My apology it should be .dbf extension and not .dat. -TYPO :(
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Email query results in HTML 6 49
Create file system directory from Oracle 10g 4 46
only show the last 365 days 6 35
Oracle cluster . 1 25
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…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

739 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