1.6 How do I…Create new tablespaces?
Problem
I have created a new Oracle instance and want to prepare it for user account creation. What tablespaces should I add for a minimally sufficient production instance, and how do I add them?
Technique
To determine the tablespaces and data files that currently comprise the instance, query the data dictionary views DBA_TABLESPACES and DBA_DATA_FILES. The structures of these views appear in Figure 1.9.
The Oracle server will enable you to open a database for production work and add users, even if the only tablespace in the instance is SYSTEM. Unfortunately, this architecture is likely to introduce fragmentation in the SYSTEM tablespace. This situation is certain to adversely effect database performance. A good rule of thumb is to add three additional tablespaces prior to adding user accounts:
A tablespace (suggested name: TEMP) to contain temporary segments, such as those produced by a sort or an index creation
A tablespace (suggested name: USERS) to act as the default tablespace for all users
A tablespace (suggested name: RBS) to contain rollback segments
Use the CREATE TABLESPACE statement to create new tablespaces and data files.\
Steps
1. Run SQL*Plus and connect as the SYSTEM user account. The script called CHP1_1.SQL queries the DBA_TABLESPACES and DBA_DATA_FILES data dictionary views to summarize the current tablespaces in the instance. The results of this script appear in Figure 1.10.
2. Run the script named CHP1_2.SQL to create the USERS, TEMP, and RBS tablespaces. This script and its results appear in Figure 1.11.
3. Run the CHP1_1.SQL script again to verify that the new tablespaces exist, reside in the intended location, and are online. Figure 1.12 shows that the CHP1_2.SQL script successfully created three new tablespaces.
How It Works
The script in Step 1 creates a report of the current tablespaces and data files that comprise the database. Step 2 creates three tablespaces commonly found in Oracle instances, and Step 3 verifies the correct operation of the script in Step 2.
Comments
Don’t attempt the reverse operation, dropping tablespaces and data files, at the operating system level without using the DROP TABLESPACE command first. Failure to use this Server Manager command prior to physically dropping a data file will cause Oracle to report that it cannot find a data file when you attempt to open the database. This error indicates a conflict between the physical structure of the database and the structure reflected in the control file.
After completion of this How-To, connect to Oracle as SYSTEM or as any user with DBA privilege, and run the INSTALL.SQL script to create the WAITE account. This CREATE USER script names the TEMP and USER tablespaces created in Step 2 as the temporary and default tablespaces, respectively, for the WAITE account.
SQL> start install
SQL>
SQL> create user waite identified by waite
2 temporary tablespace temp
3 default tablespace users;
User created.
SQL>
SQL> grant dba to waite;
Grant succeeded.