Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1797
  • Last Modified:

Tablespace and Database in Oracle

What is the relationship between Tablespace and database in Oracle.

Extract from ebook material :

CREATE DATABASE TEST
  CONTROLFILE REUSE
  LOGFILE
        GROUP 1 .......
 DATAFILE 'C:\ORADATA\TEST\SYSTEM01.DBF" SIZE 500M

CREATE TABLESPACE HIST2004
   DATAFILE '/ORADATA/PROD/HIST2004.DBF' SIZE 25G

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 ?

 
0
AXISHK
Asked:
AXISHK
3 Solutions
 
sujit_kumarCommented:
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.
0
 
sujit_kumarCommented:
Furthermore, you ca read about high-level definition and conceptual knowledge about these following this URL. http://www.csee.umbc.edu/help/oracle8/server.815/a67781/c03space.htm
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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.
0
 
AXISHKAuthor Commented:
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'
0
 
schwertnerCommented:
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.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now