Page 111
Your objective in creating these tablespaces is to separate operational and application data and functions. The TEMP, RBS, and RBS_02 tablespaces hold objects necessary for the Oracle database to function, while TOOLS, USER_DATA, and USER_INDEX hold user data. See Part VIII, "Performance Tuning," for more information on separating data, I/O, and functionality using tablespaces.
Also, create your production rollback segments. Create at least two rollback segments in the RBS tablespace to support OLTP transactions. These segments should be relatively small, with many extents. Name them RBS##; for example, RBS01, RBS02, and RBS03. Create one rollback segment in the RBS_02 tablespace for bulk data loads. This segment should be large with fewer extents. Call this rollback segment RBS_LOAD##, for example RBS_LOAD01. Bring the OLTP rollback segments online and place their names in the rollback_segments init.ora value list.
When you have finished these tasks, drop the temporary rollback segment you created earlier by issuing the following command:
drop rollback segment rbs_temp;
A new database always has two default system accountsSYS and SYSTEM. The initial password for SYS is CHANGE_ON_INSTALL, and SYSTEM is MANAGER. Change these immediately. It would be frightening to know how many databases can be broken into by logging in as system/manager.
Also, if you have installed the demo tables, consider changing the scott userid's password from the commonly known tiger to something else.
Finally, you need to register your new database in all of the configuration files on your system. If this is the first database on the system, you'll want to set your ORACLE_SID variable to reflect this. On a Windows NT server, you will need to create this value before you can set it. If you are on a UNIX server, you'll want to update oratab to reflect the new database information. Also, put the database in the tnsnames.ora and listener.ora files so they are reachable via SQL*Net.
At this point (assuming all of the previous steps completed successfully), you have a fully functional Oracle database to start your development, testing, or playing.
Anyone who works with Oracle for any length of time will sooner or later face the task of administering, troubleshooting, or working within a database environment that is completely unfamiliar to him or her. It is important to be able to quickly familiarize yourself with the custom configurations and setup of a foreign environment. This will ensure that your time is spent handling the critical task(s) at hand, rather than wasting it on time-consuming and trivial items
Page 112
such as what rollback segments you can use for your data load, what tablespace to store your user data in, and so on.
In the following sections, we'll look at how to extract information about the database from the database. The information in these pages coupled with the information covered in the previous sections will allow you to complete your picture of an unfamiliar database environment (and perhaps even your own).
The first order of business is to get a handle on the database and its objects. This includes looking at the datafiles, tablespaces, redo logs, and rollback segments, as well as memory allocations to the various caches and what background processes are running. Database jobs stored in the job queue and the users and security privileges assigned to database users are also of interest.
The v$database and v$thread data views are useful to locate information on the database and instance, respectively. v$database will show you the database name and archiving mode (whether the database is running archivelog or noarchivelog mode). v$thread is specifically a Parallel Server view, but it is useful because it gives the instance name (more of an issue on a Windows NT server than in the UNIX environment). You also can query v$instance for the database open time. In Oracle8, v$instance contains the startup_time field, which displays the instance start time. If you are using Oracle7, the following query will transform the v$instance startup information into a usable format:
SELECT TO_CHAR (TO_DATE (a.value, `J') + b.value/86400, `HH24:MI:SS DD-MON-RR') start_time FROM v$instance a, v$instance b WHERE a.key = `STARTUP TIME - JULIAN' AND b.key = `STARTUP TIME - SECONDS';
The output of this query will look similar to the following:
START_TIME ------------------ 22:48:37 06-MAR-98
In Oracle7, this rather elaborate query is necessary because the Julian date portion of the startup time is stored in one column in v$instance, while the time portion of the date is stored in a separate field (in seconds).
NOTE |
v$thread contains an OPEN_TIME field that may be different from the time displayed in v$instance. This is because one field displays the time the instance was created, while the other shows the time at which the database was opened and made available for access.n |
You can list the active controlfiles by querying v$controlfile. Rollback segment information as stored in dba_rollback_segs, v$rollstat, and v$rollname. Dba_rollback_segs will show information on all rollback segments in the database, while the v$ views will only list those rollback segments that are currently online. The following query will make sense of the online rollback segment information:
Page 113
SELECT a.segment_name, b.bytes, b.extents, a.tablespace_name, c.shrinks, c.extends, c.hwmsize FROM dba_rollback_segs a, dba_segments b, v$rollstat c WHERE a.segment_id = c.usn AND a.segment_name = b.segment_name;
This query will produce output similar to the following:
SEGMENT_NAME | BYTES | EXTENTS | TABLESPACE_NAME | SHRINKS | EXTENDS | HWMSIZE |
RBS01 | 102400 | 2 | ROLLBACK_DATA | 1 | 3 | 100352 |
RBS02 | 102400 | 2 | ROLLBACK_DATA | 0 | 0 | 100352 |
RBS03 | 102400 | 2 | ROLLBACK_DATA | 1 | 1 | 100352 |
SYSTEM | 204800 | 4 | SYSTEM | 0 | 0 | 202752 |
Information on the redo logs is stored in the v$log and v$logfile views. The following query will display pertinent information on the redo logs created for the database:
SELECT member, bytes, members, a.status FROM v$log a, v$logfile b WHERE a.group# = b.group# ORDER BY member;
The output of this query will look similar to the following:
MEMBER | BYTES | MEMBERS | STATUS |
C:\ORADATA\PPRD\REDO_PPRD_01A.LOG | 204800 | 1 | CURRENT |
C:\ORADATA\PPRD\REDO_PPRD_02A.LOG | 204800 | 1 | INACTIVE |
Mapping your datafiles and tablespaces is important. Query dba_data_files for this information. You can query information specific to your tablespaces from dba_tablespaces, and datafile- specific information from v$datafile.
Query v$sgastat for information on instance memory allocations. The following query may be helpful:
SELECT name, bytes FROM v$sgastat WHERE name in (`free memory','fixed_sga','db_block_buffers','log_buffer','dictionary cache', `library cache','sql area');
The results of this query run on a very small database are shown:
NAME | BYTES |
free memory | 2287676 |
fixed_sga | 35208 |
db_block_buffers | 409600 |
log_buffer | 8192 |
dictionary cache | 226376 |
library cache | 247636 |
sql area | 466040 |