create user tablespace

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
dbaseekAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

barfiCommented:
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
barfiCommented:
I am sorry I did not pay attention to what db version are your running?
0
dbaseekAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

dbaseekAuthor Commented:
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
barfiCommented:
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
barfiCommented:
Oh !did you mean tablespace or datafile. Tablespaces store datafiles.
I just went back to your question.
0
barfiCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dbaseekAuthor Commented:
thanks everything is perfect only thing is why we are using dat ext instead of dbf
0
barfiCommented:
My apology it should be .dbf extension and not .dat. -TYPO :(
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.