No book on Personal Oracle would be complete without discussing some of the internal aspects of the product. In this chapter I describe the architectural elements of the Oracle database such as the System Global Area, data dictionary, and initialization parameters. Whether you're building Oracle database applications with Personal Oracle, the Oracle Workgroup Server, or the Oracle Enterprise Server, you'll benefit from the information contained in this chapter.
You can visualize an Oracle database as comprising several types of materials: operating system files, memory structures, and processes. Before an Oracle database is running, the database exists as a set of disk files. After the Oracle database has been started, a memory structure--;the System Global Area--;is allocated, and at least four background processes are activated to help Oracle perform its work. These background processes are discussed later in this chapter.
First, you need to take a closer look at the files used by Oracle.
The files that Oracle uses for storing data are referred to as datafiles. These are the operating system files--;in the case of Personal Oracle, MS-DOS files--;assigned to a tablespace. In addition to using datafiles, Oracle also uses two other types of operating system files:
NoteDon't confuse the database control file with a SQL*Loader control file. The database control file is a binary file, whereas a SQL*Loader control file is a text file. The file extension for a Personal Oracle control file is .ora, and the file extension for a SQL*Loader control file is .ctl.
The System Global Area--;or SGA--;is a shared memory structure that is allocated when an Oracle database is started. For Personal Oracle, the default size of the SGA is approximately 4MB.
Three important components of the SGA are
As you know, Personal Oracle for Windows 3.1 requires the installation of Microsoft's Win32 API. This layer provides Personal Oracle with the resources it needs to perform pseudo-multitasking. At a minimum, an Oracle database uses four processes.
Oracle maintains information about the database and its operations in a set of tables called the data dictionary. The owner of these tables is a special Oracle user named SYS. You can retrieve the names of these tables by submitting the following query:
SQL> select table_name 2 from dba_tables 3 where 4 owner = 'SYS'; TABLE_NAME ------------------------------ ACCESS$ ARGUMENT$ AUD$ AUDIT$ AUDIT_ACTIONS BOOTSTRAP$ CCOL$ CDEF$ CLU$ COL$ COM$ CON$ DBMS_ALERT_INFO DBMS_LOCK_ALLOCATED DEF$_CALL DEF$_CALLDEST DEF$_DEFAULTDEST DEF$_ERROR DEF$_SCHEDULE DEF$_TRAN DEF$_TRANDEST DEFROLE$ DEPENDENCY$ DUAL DUC$ ERROR$ EXPACT$ FET$ FILE$ HISTOGRAM$ ICOL$ IDL_CHAR$ IDL_SB4$ IDL_UB1$ IDL_UB2$ INCEXP INCFIL INCVID IND$ JOB$ LAB$ LINK$ MLOG$ OBJ$ OBJAUTH$ PENDING_SESSIONS$ PENDING_SUB_SESSIONS$ PENDING_TRANS$ PROCEDURE$ PRODUCT_PROFILE PROFILE$ PROFNAME$ PROPS$ PSTUBTBL RESOURCE_COST$ RESOURCE_MAP RGCHILD$ RGROUP$ SEG$ SEQ$ SLOG$ SNAP$ SOURCE$ STMT_AUDIT_OPTION_MAP SYN$ SYSAUTH$ SYSTEM_PRIVILEGE_MAP TAB$ TABLE_PRIVILEGE_MAP TRIGGER$ TRIGGERCOL$ TS$ TSQ$ UET$ UNDO$ USER$ USER_PROFILE VIEW$ _default_auditing_options_ 79 rows selected.
WarningNever, never, never modify--;or even try to modify--;the contents or structure of any of the data dictionary tables. If you do, you stand a very good chance of corrupting the Oracle database. The contents of the data dictionary tables are modified when you use Oracle statements such as CREATE TABLE and CREATE VIEW. Directly modifying the information in a data dictionary table is akin to modifying an MS-DOS file allocation table (FAT) instead of simply issuing a DOS command to create or delete a table.
If you describe one of these tables--;for instance, TAB$, which happens to be the table of tables--;you'll see that the columns are mostly numeric.
SQL> desc sys.tab$ Name Null? Type ------------------------------- -------- ---- OBJ# NOT NULL NUMBER TS# NOT NULL NUMBER FILE# NOT NULL NUMBER BLOCK# NOT NULL NUMBER CLU# NUMBER TAB# NUMBER COLS NOT NULL NUMBER CLUCOLS NUMBER PCTFREE$ NOT NULL NUMBER PCTUSED$ NOT NULL NUMBER INITRANS NOT NULL NUMBER MAXTRANS NOT NULL NUMBER MODIFIED NOT NULL NUMBER AUDIT$ NOT NULL VARCHAR2(32) ROWCNT NUMBER BLKCNT NUMBER EMPCNT NUMBER AVGSPC NUMBER CHNCNT NUMBER AVGRLN NUMBER SPARE1 NUMBER SPARE2 NUMBER
You can't really find anything useful by looking solely at TAB$; you must join TAB$ with several other tables to comprehend the table information. To simplify this operation, Oracle supplies a number of views, called data dictionary views, which join the relevant tables and return the retrieved values to appropriately named columns. Oracle has more than 150 data dictionary views, many of which fall into the following three categories:
You'll find the following views quite useful during the development and administration of an Oracle database. They are listed in logical, rather than alphabetical, order.
The DBA_OBJECTS view contains primary information about all database objects such as tables and indexes. To see the types of objects that exist in the database, use the following query:
SQL> select distinct object_type 2 from dba_objects; OBJECT_TYPE ------------- CLUSTER FUNCTION INDEX PACKAGE PACKAGE BODY PROCEDURE SEQUENCE SYNONYM TABLE TRIGGER VIEW
Here's how to retrieve all the indexes owned by a particular user--;for instance, SYS.
SQL> select object_name 2 from dba_objects 3 where 4 owner = 'SYS' and 5 object_type = 'INDEX' 6 order by object_name; OBJECT_NAME ---------------------------------------------------------- DEF$_CALLS_PRIMARY DEF$_DEFALUTDEST_PRIMARY DEF$_ERROR_PRIMARY DEF$_SCHEDULE_PRIMARY DFRPC$_TRAN_PRIMARY I_ARGUMENT1 I_AUD1 I_AUDIT . . . I_USER# I_USER1 I_VIEW1 SYS_C00317 SYS_C00318 72 rows selected.
The DBA_TABLES view supplies information about all the tables in an Oracle database in a form that is more usable than that supplied by TAB$. Its structure is
SQL> desc dba_tables Name Null? Type ------------------------------- -------- ---- OWNER NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) TABLESPACE_NAME NOT NULL VARCHAR2(30) CLUSTER_NAME VARCHAR2(30) PCT_FREE NOT NULL NUMBER PCT_USED NOT NULL NUMBER INI_TRANS NOT NULL NUMBER MAX_TRANS NOT NULL NUMBER INITIAL_EXTENT NUMBER NEXT_EXTENT NUMBER MIN_EXTENTS NUMBER MAX_EXTENTS NUMBER PCT_INCREASE NUMBER BACKED_UP VARCHAR2(1) NUM_ROWS NUMBER BLOCKS NUMBER EMPTY_BLOCKS NUMBER AVG_SPACE NUMBER CHAIN_CNT NUMBER AVG_ROW_LEN NUMBER DEGREE VARCHAR2(10) INSTANCES VARCHAR2(10) CACHE VARCHAR2(5)
Several of the columns in DBA_TABLES return data that is computed by an ANALYZE TABLE statement (which concerns the use of the Oracle cost-based optimizer, discussed in Chapter 32, "Performance Considerations"). The columns that you would typically select from this view are OWNER, TABLE_NAME, TABLESPACE_NAME, and other columns containing the table's storage parameters.
If you wanted to see all the tables whose name contained the pattern PROD, the following query will retrieve the information you want:
SQL> select owner, table_name 2 from dba_tables 3 where 4 table_name like '%PROD%' 5 order by owner, table_name; OWNER TABLE_NAME ------------------------------ ------------------------------ DLOCKMAN PRODUCT FRAYED_WIRES HOUSTON_PRODUCT FRAYED_WIRES PORTLAND_PRODUCT FRAYED_WIRES PRODUCT FRAYED_WIRES SAVE_HOUSTON_PRODUCT FRAYED_WIRES SAVE_PORTLAND_PRODUCT SCOTT PRODUCT SYS PRODUCT_PROFILE 8 rows selected.
As you might have noticed, the DBA_TABLES view doesn't provide any information about a table's columns; as a separate view, DBA_TAB_COLUMNS view presents a table's structure by returning the following values:
SQL> desc dba_tab_columns Name Null? Type ------------------------------- -------- ---- OWNER NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) COLUMN_NAME NOT NULL VARCHAR2(30) DATA_TYPE VARCHAR2(9) DATA_LENGTH NOT NULL NUMBER DATA_PRECISION NUMBER DATA_SCALE NUMBER NULLABLE VARCHAR2(1) COLUMN_ID NOT NULL NUMBER DEFAULT_LENGTH NUMBER DATA_DEFAULT LONG NUM_DISTINCT NUMBER LOW_VALUE RAW(32) HIGH_VALUE RAW(32) DENSITY NUMBER
To obtain a list of each tablespace in a database and its default storage parameters, submit the following SQL statement:
SQL> select * 2 from dba_tablespaces; TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXT MAX_EXT PCT_INCR STATUS ---------------- -------------- ----------- ------- ------- --------- ------ SYSTEM 4096 4096 1 121 10 ONLINE USER_DATA 4096 4096 1 121 10 ONLINE ROLLBACK_DATA 102400 102400 2 121 50 ONLINE TEMPORARY_DATA 4096 4096 1 121 10 ONLINE FRAYED_WIRES 77824 51200 1 121 15 ONLINE
The DBA_DATA_FILES data dictionary view returns the name of each datafile, its size, and its associated tablespace.
SQL> select file_name, bytes, tablespace_name 2 from dba_data_files; FILE_NAME BYTES TABLESPACE_NAME -------------------------------------------------- --------- ------------------ C:\ORAWIN\DBS\wdbrbs.ora 3145728 ROLLBACK_DATA C:\ORAWIN\DBS\wdbtemp.ora 2097152 TEMPORARY_DATA C:\ORAWIN\DBS\wdbuser.ora 3145728 USER_DATA C:\ORAWIN\DBS\wdbsys.ora 10485760 SYSTEM c:\orawin\dbs\wdbfray.ora 10485760 FRAYED_WIRES c:\orawin\dbs\wdbfray2.ora 5242880 FRAYED_WIRES 6 rows selected.
The DBA_EXTENTS view returns information about every extent that has been allocated in a database. This information includes the following:
SQL> desc dba_extents Name Null? Type ------------------------------- -------- ---- OWNER VARCHAR2(30) SEGMENT_NAME VARCHAR2(81) SEGMENT_TYPE VARCHAR2(17) TABLESPACE_NAME VARCHAR2(30) EXTENT_ID NOT NULL NUMBER FILE_ID NOT NULL NUMBER BLOCK_ID NOT NULL NUMBER BYTES NUMBER BLOCKS NOT NULL NUMBER
For example, suppose the Oracle account FRAYED_WIRES owns a table named XYZ that was created with an initial extent of 10,240 bytes. Once you've added so many rows that the first extent is filled, Oracle allocates another extent of 10,240 bytes. The extents can be seen by querying the DBA_EXTENTS view.
SQL> select segment_type, bytes 2 from sys.dba_extents 3 where 4 segment_name = 'XYZ'; SEGMENT_TYPE BYTES ----------------- --------- TABLE 10240 TABLE 10240
In one sense, the DBA_SEGMENTS view provides a summary of the data contained in DBA_EXTENTS. Using the previous example, two rows were returned when you queried the DBA_EXTENTS view to see what extents were allocated for table XYZ. If you query the DBA_SEGMENTS view, a single row returns data about the allocation of storage for table XYZ. Notice that the value of Bytes is the sum of the bytes returned by DBA_EXTENTS for the same segment name.
SQL> select segment_name, segment_type, tablespace_name, bytes 2 from dba_segments 3 where 4 segment_name = 'XYZ'; SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES -------------- ----------------- ------------------------------ --------- XYZ TABLE FRAYED_WIRES 20480
As discussed in Chapter 27, "Managing Users and Roles," you can obtain information about the free space in a tablespace by querying the DBA_FREE_SPACE view. You can get the total number of free bytes in a tablespace with the following query:
SQL> select sum(bytes) 2 from dba_free_space 3 where 4 tablespace_name = 'USER_DATA'; SUM(BYTES) ---------- 2809856
You can retrieve the size of each free extent by issuing the following statement:
SQL> select bytes
2 from dba_free_space
3 where
4 tablespace_name = 'USER_DATA';
BYTES
---------
2801664
8192
The DBA_TRIGGERS view provides information about each database trigger that exists in the database. You can retrieve information about all DELETE triggers with the following query:
SQL> select trigger_name, trigger_type, table_name 2 from dba_triggers 3 where 4 triggering_event = 'DELETE'; TRIGGER_NAME TRIGGER_TYPE TABLE_NAME ------------------------------ ---------------- ------------------------------ DELETE_INVOKE_DIS_PATIENTS AFTER STATEMENT INVOKE_DIS_PATIENTS
You can view the PL/SQL statements that compose a trigger by selecting the Trigger_Body column from DBA_TRIGGERS. Notice that the SQL*Plus variable long has been increased to a large number so that the entire trigger body is displayed.
SQL> set long 5000 SQL> set arraysize 1 SQL> select trigger_body 2 from dba_triggers 3 where 4 trigger_name = 'DELETE_INVOKE_DIS_PATIENTS'; TRIGGER_BODY ------------------------------------------------------------------- begin Dis_Patients_Normal_Temp; end;
The DBA_USERS view contains basic information about every Oracle user. Oracle assigns each user a user ID when it is created. This user ID is also referenced in other data dictionary tables and views. Here is a simple query of DBA_USERS that returns each Oracle user (or account) and its associated user ID.
SQL> select username, user_id 2 from dba_users; USERNAME USER_ID ------------------------------ --------- SYS 0 SYSTEM 5 SCOTT 8 FRAYED_WIRES 12 DLOCKMAN 10 ORDINARY_USER 13 JSMITH 14 VILLAGE_WIDGET 15
If you want to see the definition of a view, you can retrieve the text of the view's query with the following SQL statement. Notice that I've set the SQL*Plus variable long to 5000 and reduced another SQL*Plus variable arraysize to 1 so that the buffer overflow error doesn't occur.
SQL> set long 5000 SQL> set arraysize 1 SQL> select text 2 from dba_views 3 where 4 view_name = 'DBA_VIEWS'; TEXT -------------------------------------------------------------- select u.name, o.name, v.textlength, v.text from sys.obj$ o, sys.view$ v, sys.user$ u where o.obj# = v.obj# and o.owner# = u.user#
More than 50 virtual Oracle tables provide configuration and performance information about an Oracle database. By virtual, I mean that these tables don't actually use disk space to store their contents. They are born when the database is started and then die when the database is shut down. All of these tables begin with V$. In the following discussion I focus on the most useful tables.
The only way to use a SQL statement to retrieve information about a database's redo log files is by referencing V$LOGFILE. To illustrate, the following query returns the two redo log files that exist for the Personal Oracle starter database:
SQL> select *
2 from v$logfile;
GROUP# STATUS MEMBER
--------- ------- --------------------------------------------------
2 C:\ORAWIN\DBS\wdblog2.ora
1 STALE C:\ORAWIN\DBS\wdblog1.ora
V$PARAMETER can be quite useful. It provides the names and current values for all Oracle initialization parameters as shown:
SQL> select name, value
2 from v$parameter
3 order by name;
NAME VALUE
--------------------------------------------- ------------------------------
audit_trail NONE
background_dump_dest %RDBMS71%\trace
blank_trimming FALSE
cache_size_threshold 40
checkpoint_process FALSE
cleanup_rollback_entries 20
close_cached_open_cursors FALSE
commit_point_strength 1
compatible
compatible_no_recovery
control_files %RDBMS71_CONTROL%\ctl1.ora,
%RDBMS71_ARCHIVE%\ctl1.ora
cursor_space_for_time FALSE
db_block_buffers 400
db_block_checkpoint_batch 8
db_block_lru_extended_statistics 0
db_block_lru_statistics FALSE
db_block_size 2048
db_domain WORLD
db_file_multiblock_read_count 8
db_file_simultaneous_writes 4
db_files 32
db_name oracle
dblink_encrypt_login FALSE
discrete_transactions_enabled FALSE
distributed_lock_timeout 10
distributed_recovery_connection_hold_time 0
distributed_transactions 10
dml_locks 100
enqueue_resources 155
event
fixed_date
gc_rollback_segments 20
gc_segments 10
gc_tablespaces 5
global_names FALSE
ifile
job_queue_interval 60
job_queue_keep_connections FALSE
job_queue_processes 0
license_max_sessions 25
license_max_users 0
license_sessions_warning 0
log_archive_buffer_size 127
log_archive_buffers 4
log_archive_dest %RDBMS71_ARCHIVE%
log_archive_format ARC%S.%T
log_archive_start FALSE
log_buffer 65596
log_checkpoint_interval 1000
log_checkpoint_timeout 0
log_checkpoints_to_alert FALSE
log_files 255
log_simultaneous_copies 0
log_small_entry_max_size 800
max_commit_propagation_delay 90000
max_dump_file_size 5120
max_enabled_roles 8
max_rollback_segments 30
mts_dispatchers
mts_listener_address (address=(protocol=ipc)(key=%s))
mts_max_dispatchers 0
mts_max_servers 0
mts_servers 0
mts_service
nls_currency
nls_date_format
nls_date_language
nls_iso_currency
nls_language AMERICAN
nls_numeric_characters
nls_sort
nls_territory AMERICA
open_cursors 50
open_links 20
optimizer_comp_weight 0
optimizer_mode CHOOSE
os_authent_prefix OPS$
os_roles FALSE
parallel_default_max_instances 0
parallel_default_max_scans 0
parallel_default_scansize 100
parallel_max_servers 5
parallel_min_servers 0
parallel_server_idle_time 5
pre_page_sga FALSE
processes 50
recovery_parallelism 0
remote_login_passwordfile EXCLUSIVE
remote_os_authent FALSE
remote_os_roles FALSE
resource_limit FALSE
rollback_segments
row_cache_cursors 10
row_locking ALWAYS
sequence_cache_entries 10
sequence_cache_hash_buckets 10
serializable FALSE
session_cached_cursors 0
sessions 60
shared_pool_size 3500000
single_process FALSE
snapshot_refresh_interval 60
snapshot_refresh_keep_connections FALSE
snapshot_refresh_processes 0
sort_area_retained_size 262144
sort_area_size 262144
sort_mts_buffer_for_fetch_size 0
sort_read_fac 20
sort_spacemap_size 512
sql92_security FALSE
sql_trace FALSE
temporary_table_locks 60
thread 0
timed_statistics FALSE
transactions 66
transactions_per_rollback_segment 16
user_dump_dest %RDBMS71%\trace
117 rows selected.
The performance table V$SGA displays rudimentary data about the SGA's size, which is the same information that SQL*DBA displays when it is used to start an Oracle instance.
SQL> select * 2 from v$sga; NAME VALUE -------------------- --------- Fixed Size 36432 Variable Size 3846292 Database Buffers 819200 Redo Buffers 65596
Like SYSDATE and USER, ROWID is a pseudocolumn that returns a unique internal storage location for a table's rows. You can select ROWID from any table. Its format is
block-number.row-number.file_number
where block-number is an eight-digit hexadecimal number that represents the relative block number in the datafile referenced by file-number; row-number is the number of the row within its block, beginning with 1; and file_number is the database file number in which block-number resides.
To illustrate, here's how you can retrieve the ROWID for the rows in the Product table:
SQL> select rowid, Product_ID, Manufacturer_ID 2 from Product; ROWID PRODUCT_ID MANUFAC ------------------ ------------ ------- 00000043.0000.0002 A2001 TES801 00000043.0001.0002 A504 SEN101 00000043.0002.0002 A509 SEN101 00000043.0003.0002 A903 TES801 00000043.0004.0002 B901 TES801 00000043.0005.0002 B801 SEN101 00000043.0006.0002 C2002 MIT501 00000043.0007.0002 C2005 MIT501 00000043.0008.0002 C3002 SEN101 00000043.0009.0002 B311 TES801 00000043.000A.0002 B9310 SEN101 00000043.000B.0002 B384 TES801 00000043.000C.0002 D301 SEN101 00000043.000D.0002 TR901 TES801 00000043.000E.0002 X1000 GOL201 15 rows selected.
One way to distinguish between two rows that have identical values for every column is by retrieving each row's ROWID. Suppose a table named XYZ has two columns, Column1 and Column2, which are both declared as numbers. You can insert 1,000 rows into XYZ by running the following PL/SQL script:
SQL> create table XYZ (
2 Column1 number,
3 Column2 number);
Table created.
SQL>
SQL> declare
2
2 max_records constant int := 1000;
3 i int := 1;
4
4
4 begin
5
5 for i in 1..max_records loop
6
6 insert into XYZ
7 (Column1, Column2)
8 values
9 (i, i);
10
10 end loop;
11
11 commit;
12
12 end;
13 /
PL/SQL procedure successfully completed.
SQL> select count(*)
2 from XYZ;
COUNT(*)
---------
1000
Next add a duplicate row to XYZ to represent a bad row that you've inherited from some legacy data:
SQL> insert into XYZ 2 (Column1, Column2) 3 values 4 (1000, 1000); 1 row created. SQL> commit; Commit complete.
Here's a simple way to find the duplicate rows in the table:
SQL> select Column1, Column2
2 from XYZ
3 having count(*) > 1
4 group by Column1, Column2;
COLUMN1 COLUMN2
--------- ---------
1000 1000
SQL> select Column1, Column2
2 from XYZ
3 where
4 Column1 = 1000 and
5 Column2 = 1000;
COLUMN1 COLUMN2
--------- ---------
1000 1000
1000 1000
What can you do about these duplicate rows? Please ignore the fact that this table has only two columns for simplicity; in a real situation, you could encounter this problem with a table having more than 100 columns. You can't delete one row without deleting the other because you don't have any way to distinguish between them--;after all, they are duplicates. You can't update one of the rows to distinguish it from the other. However, you can identify these duplicate rows by their ROWIDs.
SQL> select ROWID, Column1, Column2 2 from XYZ 3 where 4 Column1 = 1000 and 5 Column2 = 1000; ROWID COLUMN1 COLUMN2 ------------------ --------- --------- 000000E1.0020.0006 1000 1000 000000E1.0021.0006 1000 1000
You can delete one of the rows--;whichever one you want--;by referencing its ROWID in this manner:
SQL> delete from XYZ 2 where 3 rowid = '000000E1.0020.0006'; 1 row deleted. SQL> select rowid, Column1, Column2 2 from XYZ; ROWID COLUMN1 COLUMN2 ------------------ --------- --------- 000000E1.0021.0006 1000 1000
STRONG ROWID VALUES
In their hunger for the secrets of Oracle, some developers are elated when they learn about the ROWID pseudocolumn. The developers think they've learned a shortcut when they encounter the chapter (Chapter 13) of the Oracle7 Server Concepts manual that deals with the rule-based optimizer and read that the fastest way to access a single row is by ROWID. "Great," the clever developer thinks. "I'll add a column to all of my tables to store ROWID, and then I'll select the ROWID of each row and store the value in my new column. I'll have the fastest Oracle database on my block." The clever developer has just one problem: He or she has no guarantee that a row's ROWID will never change. For example, if you export the contents of a table, delete its rows, and import those rows back into the table, each row will probably have a different ROWID from what it had before. The only way you could be sure that you had the correct ROWIDs before using them would be to select every row's ROWID and use those ROWIDs in your query. If you did so, you'd be performing an additional query every time you wanted to retrieve a row! And that's not counting the layer of ugly complexity you'd be adding to the database. If you remember only one thing from this book, remember this: Never store ROWID as a column in a table.
As with other components of Personal Oracle, the Oracle initialization parameters as supplied are designed to work for the majority of Personal Oracle applications. If you change the value of an initialization parameter, the change takes effect the next time the Oracle database is started. Changing an initialization parameter won't affect a database that is running. The next sections look at the procedure for changing an initialization parameter.
To demonstrate how you can change an Oracle initialization parameter, I will set two parameters--;SQL_TRACE and TIMED_STATISTICS--;to TRUE. When enabled, these parameters create for each Oracle session a trace file that contains each SQL statement processed during that session. If you do set these parameters to TRUE, you will see some decrease in the performance of the database because of the extra processing associated with the trace files.
In Personal Oracle for Windows 95, these initialization parameters are stored in C:\Orawin95\Database\Initorcl.ora. To set SQL_TRACE and TIMED_STATISTICS to TRUE, simply edit the appropriate parameters in the file. For example, make sure that the following two lines exist in the file:
sql_trace = true timed_statistics = true
The new value for an initialization parameter will not take effect until the next time the database is started.
In Personal Oracle for Windows 3.1, initialization parameters are changed with Database Manager. When you invoke Database Manager by double-clicking its icon in the Personal Oracle program group, you'll see a set of buttons on the right side of the screen. When you click the Config button, Database Manager brings up the Configure Initialization Parameters screen. (See Figure 33.1.)
You need to provide a configuration name so that you can save any modified initialization parameters. Click the Save As button and type initpo7. (See Figure 33.2.)
When you click the OK button to save the configuration, Database Manager displays a message to inform you that any changes to the initialization parameters won't take effect until the next time the database is started. (See Figure 33.3.)
Once more, the Configure Initialization Parameters screen appears. However, this time, the configuration name is initpo7. (See Figure 33.4.)
Click the Advanced button to modify individual initialization parameters. Database Manager will display a warning message that indicates that changing the value of an initialization parameter can cause the database to cease to function. (See Figure 33.5.) Please take this message to heart--;it's quite accurate. If you're willing to experiment, the worst thing that might happen is that you'll need to reinstall the starter database. Be sure to export your tables before you begin experimenting.
Database Manager brings up the Advanced Parameter Editing screen. (See Figure 33.6.) Click the down arrow on the Parameter drop-down list to display a list of all parameters. Click the SQL_TRACE parameter. (See Figure 33.7.)
In the Value field, type in the value TRUE. Click the Set button and then the OK button. (See Figure 33.8.)
Set the TIMED_STATISTICS parameter to TRUE by following the preceding steps. Click the Set button. (See Figure 33.9.) The next time that the Oracle instance starts, SQL_TRACE and TIMED_STATISTICS will be set to TRUE.
At this point, you should shut down the instance and restart it for the modified initialization parameters to take effect. Once you've had practice making these changes, I recommend that you set SQL_TRACE and TIMED_STATISTICS back to FALSE if you're not interested in generating trace files for each Oracle session.
Oracle is able to audit a user's database activity. Events that can be audited are classified into these three categories:
If you want to see the SQL statements that were processed during an Oracle session, set both the SQL_TRACE and TIMED_STATISTICS parameters to TRUE. By default, both of these parameters are FALSE. Setting these parameters to TRUE causes Oracle to generate a trace file for each Oracle session, which can then be processed by TKPROF, an Oracle utility. This utility is described in detail in Chapter 32.
The ROLLBACK_SEGMENTS parameter contains the names of existing rollback segments, if any, that have should be activated during the startup of the Oracle database. See Chapter 26, "Managing Space," for more information on creating rollback segments.
If you have created a copy of the control file when the database was cold (not running), the only way for Oracle to use the new control file--;the copy you made--;is by referencing it with the CONTROL_FILES initialization parameter. The reason for having multiple control files is to protect against a single point of failure. By having a second control file on another disk drive, you've added some redundancy to the database.
DB_BLOCK_SIZE and DB_FILES are two Oracle initialization parameters whose values can't be changed after a database has been created.
DB_BLOCK_SIZE determines the size of an Oracle block. By default, DB_BLOCK_SIZE is set to 2,048 bytes. Increasing the size of an Oracle block generally reduces the disk blocks that must be read to process a query. When the block is bigger, the header portion of each Oracle block represents a smaller percentage of the block. If you decide to create an Oracle database with a larger block size, remember to decrease the number of block buffers so that the overall size of the SGA is not too large for the available memory on your PC.
MAXDATAFILES specifies the maximum number of datafiles that can exist in a database. It is specified in the CREATE DATABASE statement that creates an Oracle database. (See the online Oracle7 Server SQL Language Reference Manual, which can be accessed via the Oracle7 Documentation icon in the Personal Oracle7 program group.) If MAXDATAFILES isn't specified in the CREATE DATABASE statement, the initialization parameter DB_FILES determines the maximum number of datafiles that can ever exist in the database. By default, DB_FILES is set to 20. When you install the Personal Oracle database, it automatically creates 4 datafiles, which means that you can add only another 16 files to the database. Accordingly, unless you want to re-create the database with a larger number for MAXDATAFILES, be judicious in expanding a tablespace with Database Expander or the ALTER TABLESPACE ADD statement. When you add a datafile to an existing tablespace or create a new tablespace, always add more space than you think you'll need.
A number of initialization parameters have no effect on a Personal Oracle database and can therefore be ignored. These include any parameter whose name begins with the following:
In this introduction to Oracle internals, you learned the following: