Tablespace and Database in Oracle

Posted on 2007-07-23
Last Modified: 2013-12-18
What is the relationship between Tablespace and database in Oracle.

Extract from ebook material :

        GROUP 1 .......


To my limited knowledge in Oracle, Tablespace is existed within a database and tables and index are created under tablespace. So, what is the point of creating datafile for database ? What does it contain ?

Default database orcl is created, when I try to create a Database based on the extract of ebook above, I told me that database parameter  oracl not match. Any idea  ??? Does it mean that only one database is allowed in a machine ?

Question by:AXISHK
    LVL 11

    Accepted Solution

    Datafile is a physical file that is created within a database. Tablespace is a logical structure which is created using a data file. As you said, i database can contain 1 or more Tablespaces (And data files).

    You can create more than 1 database in a machine, but 1 tablespace will exist in ONLY 1 database. Database is the super-set, Tablespace is the subset.
    LVL 11

    Expert Comment

    Furthermore, you ca read about high-level definition and conceptual knowledge about these following this URL.
    LVL 28

    Assisted Solution

    In addition to the above, database consists of tablespaces, data files, control files, users, objects like roles, tables, indexes, log files, rollback segments etc...

    tablespaces cannot exist without a database. I mean we can create tablespaces for the tablespace.

    tablespaces are logical structures , data files are physical files residing in some directory in the file system which contain the database objects data.

    just to give you a bit more understanding, we can have as many tablespaces you want for a tablespace. For example, small database with 2 or 3 users can have a separate tablespace for tables and a separate tablespace for indexes. This is just an example to give a better understanding.

    A tablespace cannot exist in the database without the physical file in the file system.

    Author Comment

    Thanks but how to create additional database on the same machine. Default database when install in orcl. And when I run the following script, it return with error "database name 'TEST' does not match parameter db_name 'orcl'". To me, it seem that I can only create tablespaces under "orcl", rather than create another database and start creating tablespaces under it. Tks.

    SQL> shutdown immediate
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup nomount
    ORACLE instance started.

    Total System Global Area  209715200 bytes
    Fixed Size                  1248116 bytes
    Variable Size              88081548 bytes
    Database Buffers          113246208 bytes
    Redo Buffers                7139328 bytes
    SQL> create database TEST
      2     controlfile reuse
      3     logfile
      4         group 1 'C:\oracle\product\10.2.0\oradata\test\redo01.log' size 10M
      5         group 2 'C:\oracle\product\10.2.0\oradata\test\redo02.log' size 10M
      6         group 3 'C:\oracle\product\10.2.0\oradata\test\redo03.log' size 10M
      7     datafile 'C:\oracle\product\10.2.0\oradata\test\system01.dbf' size 500M
      8     autoextend on next 50M maxsize unlimited
      9     sysaux datafile 'C:\oracle\product\10.2.0\oradata\test\sysaux01.dbf' size 250M
     10     autoextend on next 50M maxsize unlimited
     11     default temporary tablespace temp tempfile 'C:\oracle\product\10.2.0\ordata\test\temp01.dbf' size 100M
     12     autoextend on next 100M maxsize 8000M
     13     undo tablespace undo
     14     datafile  'C:\oracle\product\10.2.0\oradata\test\undo01.dbf' size 500M
     15     autoextend on next 100M maxsize unlimited
     16     character set we8mswin1252
     17     national character set al16utf16
     18     user sys identified by SOUPERSEEKRET
     19     user system identified by MYSEEKRET;
    create database TEST
    ERROR at line 1:
    ORA-01501: CREATE DATABASE failed
    ORA-01504: database name 'TEST' does not match parameter db_name 'orcl'
    LVL 47

    Assisted Solution

    You can create additional databases (instances) using the GUI called DBCA (Data base creation assistant)
    It is easy, but you have very carefully to plan the new Instance. The most important question are:
    1. What will be the Character set (we8mswin1252, AL32UTF8)
    2. How much free RAM I have and how RAm will I devote for the
    new database for the SGA. In Windows it is recommended to keep free
    50% of the RAM to avoid spooling problems.

    Tablespaces are logical sets used for keeping tables, indexes and other DB objects.
    The comprises of one or more data files that physically represents the tablespaces.
    On logical level you use tablespaces. Every user can have quota on every tablespace and usage
    privileges. This privileges are controlled by the DBA - it is very important.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
    This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

    734 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

    19 Experts available now in Live!

    Get 1:1 Help Now