asked on

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 ?

ASKER CERTIFIED SOLUTION

sujit_kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sujit_kumar

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

SOLUTION

Naveen Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AXISHK

ASKER

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'

SOLUTION

schwertner

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial