Oracle Database

If a tablespace is dropped, what happens to its data files? By default, data files will remain in OS file system, even if the tablespace they are mapped is dropped. Of course, you delete the data files using OS commands, if they are no longer needed.

Another way of deleting data files is to use the INCLUDING clause in the DROP TABLESPACE statement. Here is a SQL sample script:

SQL> CREATE TABLESPACE my_space

2 DATAFILE '/temp/my_space.dbf' SIZE 10M;

Tablespace created.

SQL> DROP TABLESPACE my_space INCLUDING CONTENTS

2 AND DATAFILES;

Tablespace dropped.

With the INCLUDING CONTENTS AND DATAFILES clause, all contents and mapped data files are also deleted.

After you have created a new tablespace, you can give it to your users for them to create tables in the new tablespace. To create a table in a specific tablespace, you need to use the TABLESPACE clause in the CREATE TABLE statement. Here is a sample script:

SQL> connect SYSTEM/globalguideline

Connected.

SQL> CREATE TABLESPACE my_space

2 DATAFILE '/temp/my_space.dbf' SIZE 10M;

Tablespace created.

SQL> connect HR/globalguideline

Connected.

SQL> CREATE TABLE my_team TABLESPACE my_space

2 AS SELECT * FROM employees;

Table created.

SQL> SELECT table_name, tablespace_name, num_rows

2 FROM USER_TABLES

3 WHERE tablespace_name in ('USERS', 'MY_SPACE');

TABLE_NAME    TABLESPACE_NAME NUM_ROWS

------------------------------ ---------------- ----------

MY_TEAM    MY_SPACE   - 

EMPLOYEES    USERS   107 

...

If you created a tablespace with a data file a month ago, now 80% of the data file is used, you should add another data file to the tablespace. This can be done by using the ALTER TABLESPACE ... ADD DATAFILE statement. See the following sample script:

SQL> connect HR/globalguideline

SQL> CREATE TABLESPACE my_space

2 DATAFILE '/temp/my_space.dbf' SIZE 10M;

Tablespace created.

SQL> ALTER TABLESPACE my_space

2 DATAFILE '/temp/my_space_2.dbf' SIZE 5M;

Tablespace altered.

SQL> SELECT TABLESPACE_NAME, FILE_NAME, BYTES

2 FROM DBA_DATA_FILES;

TABLESPACE_NAME FILE_NAME      BYTES

--------------- --------------------------------- ---------

USERS   C:ORACLEXEORADATAXEUSERS.DBF 104857600

SYSAUX  C:ORACLEXEORADATAXESYSAUX.DBF 461373440

UNDO  C:ORACLEXEORADATAXEUNDO.DBF 94371840

SYSTEM  C:ORACLEXEORADATAXESYSTEM.DBF 356515840

MY_SPACE  C:TEMPMY_SPACE.DBF   10485760

MY_SPACE  C:TEMPMY_SPACE_2.DBF   5242880

After you shutting down an Oracle database, you accidentally deleted a data file from the operating system. If you try to start the database again you will get error when Oracle tries to open the database after mounting the database. The following tutorial examples shows you what will happen if the data file c: empmy_space.dbf is deleted. Oracle can still start the database instance and mount the database. But it will fail on opening the database as shown below in a SQL*Plus session:

>sqlplus /nolog

SQL> connect SYSTEM/globalguideline AS SYSDBA

SQL> STARTUP

ORACLE instance started.

Total System Global Area 100663296 bytes

Fixed Size   1285956 bytes

Variable Size   58720444 bytes

Database Buffers   37748736 bytes

Redo Buffers   2908160 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 5 - see DBWR

trace file

ORA-01110: data file 5: 'C:TEMPMY_SPACE.DBF'

SQL> SHUTDOWN;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Let's say you have a corrupted data file or lost a data file. Oracle can mount the database. But it will not open the database. What you can do is to set the bad data file as offline befor opening the database. The tutorial exercise shows you how to set two data files offline and open the database without them:

>sqlplus /nolog

SQL> connect SYSTEM/globalguideline AS SYSDBA

SQL> STARTUP MOUNT;

ORACLE instance started.

Total System Global Area 100663296 bytes

Fixed Size   1285956 bytes

Variable Size   58720444 bytes

Database Buffers   37748736 bytes

Redo Buffers   2908160 bytes

Database mounted.

SQL> ALTER DATABASE DATAFILE ' empmy_space.dbf'

2 OFFLINE DROP;

Database altered.

SQL> ALTER DATABASE DATAFILE ' empmy_space_2.dbf'

2 OFFLINE DROP;

Database altered.

Based on Oracle's Administrator Guide, there are 11 steps to create a database with the CREATE DATABASE statement:

► Step 1: Decide on Your Instance Identifier (SID)

► Step 2: Establish the Database Administrator Authentication Method

► Step 3: Create the Initialization Parameter File

► Step 4: Connect to the Instance

► Step 5: Create a Server Parameter File (Recommended)

► Step 6: Start the Instance

► Step 7: Issue the CREATE DATABASE Statement

► Step 8: Create Additional Tablespaces

► Step 9: Run Scripts to Build Data Dictionary Views

► Step 10: Run Scripts to Install Additional Options (Optional)

► Step 11: Back Up the Database.

Other items in this FAQ collection will follow those steps to help you creating a new database manually from beginning to end.

This is Step 6. Now you are ready to start the new Oracle Instance without any database. This instance will be used to create a database. Starting an instance without database can be done by using STARTUP NOMOUNT statement as shown below:

>.insqlplus /nolog

SQL> CONNECT / AS SYSDBA

Connected.

SQL> SHUTDOWN

ORACLE instance shut down.

SQL> STARTUP NOMOUNT

ORA-00821: Specified value of sga_target 16M is too small,

needs to be at least 20M

The SHUTDOWN command is need to bring the default instance XE down.

The STARTUP NOMOUNT command failed because it tried to start the default instance XE, and there is a bad parameter in the XE instance SPFile.

See the next FAQ question to find another way to start the new instance ggl.

The sample initialization parameter file provided by Oracle seems to be not working. But we can try to start the new instance with a minimal initialization parameter file (PFile). First you can create another PFile, $ORACLE_HOME/database/initggl_ini_min.ora, as shown below:

db_name=ggl

control_files=("oraclexeoradatagglcontrol.dbf")

undo_management=AUTO

Then start the ggl instance again:

SQL> CONNECT / AS SYSDBA

Connected.

SQL> STARTUP NOMOUNT

PFILE=$ORACLE_HOME/database/initggl_ini_min.ora

ORACLE instance started.

Total System Global Area 113246208 bytes

Fixed Size   1286028 bytes

Variable Size   58720372 bytes

Database Buffers   50331648 bytes

Redo Buffers   2908160 bytes

This is Step 7. Oracle Administrator Guide provided a sample CREATE DATABASE statement. But it is a long statement. You can modify and same it in a file, $ORACLE_HOME/configscripts/create_database_ggl.sql, and run the file within SQL*Plus. Here is a copy of the modified CREATE DATABASE statement:

CREATE DATABASE ggl

USER SYS IDENTIFIED BY globalguideline

USER SYSTEM IDENTIFIED BY globalguideline

LOGFILE GROUP 1 ('/oraclexe/oradata/ggl/redo01.log') SIZE 10M,

GROUP 2 ('/oraclexe/oradata/ggl/redo02.log') SIZE 10M,

GROUP 3 ('/oraclexe/oradata/ggl/redo03.log') SIZE 10M

MAXLOGFILES 5

MAXLOGMEMBERS 5

MAXLOGHISTORY 1

MAXDATAFILES 100

MAXINSTANCES 1

CHARACTER SET US7ASCII

NATIONAL CHARACTER SET AL16UTF16

DATAFILE '/oraclexe/oradata/ggl/system01.dbf' SIZE 32M REUSE

EXTENT MANAGEMENT LOCAL

SYSAUX DATAFILE '/oraclexe/oradata/ggl/sysaux01.dbf' SIZE 32M REUSE

DEFAULT TABLESPACE tbs_1

DEFAULT TEMPORARY TABLESPACE tempts1

TEMPFILE '/oraclexe/oradata/ggl/temp01.dbf'SIZE 20M REUSE

UNDO TABLESPACE undotbs

DATAFILE '/oraclexe/oradata/ggl/undotbs01.dbf'

SIZE 20M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

To better organize data files, you should create a dedicated directory for each Oracle database. This can be done by using Windows file explorer to create the oraclexeoradataggl directory. Try the CREATE DATABASE statement again, when you have the directory ready.

If your CREATE DATABASE statement failed half way again, you may have to clean up the partial result of the CREATE DATABASE statement. Here is a list of suggestions for you:

► Run SHUTDOWN command to stop the partial started database instance.

► Remove all files in ggl directory: oraclexeoradataggl

► Run STARTUP NOMOUNT PFILE command to start the empty instance again to be ready for CREATE DATABASE statement.