Oracle Enhancements in SQL* Plus

In SQL*Plus 10.1.0.2, you can include the database username and what the user is connected as in the prompt using the following command:

Date can also be displayed:

Note: the date will be displayed in a format based on the active NLS_DATE_FORMAT value Database identified can also be added:

In 10g, the spool command can append to an existing one:

If you want to overwrite it, simply omit the append clause or use REPLACE instead, which is the default.

The following will check the existence of the file before writing to prevent the overwriting:

In Oracle Database 10g, the file login.sql is not only executed at SQL*Plus startup time, but at connect time as well. Therefore, each time you successfully issue the connect command, the login.sql script will be executed from the current directory.

The only condition for using the easy connect naming method is that you should have support for the TCP/IP protocol on both the client and the server.

The new easy connect method is referred to as EZCONNECT in a sqlnet.ora file.

Only the host name is mandatory.

A dispatcher will start automatically when you start a database instance, but no shared server process will start. If you want to start a shared server while your instance is running, you can do so by setting a non-zero value for the SHARED_SERVER initialization parameter, as shown here:

RESUMABLE_TIMEOUT parameter enables resumable statements at the system or the session level in seconds. Its default is zero which means it is disabled.

In the session level, the following statement should be issued as well:

In Oracle Database 10g Release 2, when you start the instance, only 10% of the buffer cache is initialized; the rest is initialized after the database is opened by the checkpoint process. This new approach reduces instance startup time significantly. Bear in mind, however, that until the entire buffer cache is initialized, automatic buffer cache sizing is not available.

You can now simply specify a time or SCN, and LogMiner will automatically add the necessary redo log files by scanning the control files for the log information. You must use the DBMS_LOGMNR.CONTINUOUS_MINE procedure to facilitate this automatic gathering of redo log files for mining purposes.

You can disable the generation of physical ROWIDs by using the NO_ROWID_IN_STMT option when you use the DBMS_LOGMNR package.

To remove redo log files, you can now use the new REMOVE_LOGFILE procedure with the DBMS_LOGMNR package.

In Oracle Database 10g, there is no need for you to set the  FAST_START_MTTR_TARGET parameter because Oracle itself will automatically tune the checkpointing process.

You can enable automatic checkpoint tuning by simply setting the FAST_START_MTTR_TARGET parameter to any non-zero value.

The V$PROCESS_MEMORY introduced in Oracle 10.2. It can be used to verify size of SQL and PL/SQL areas for a process. It is also included in STATSPACK report.

Oracle ensures the data block's integrity by computing a checksum on the data value before writing the data block to the disk. This checksum value is also written to the disk. When the block is read from the disk, the reading process calculates the checksum again and then compares against the stored value. If they differ, it means the block is corrupted.

In Oracle Database 10g Release 2, you can make the database perform the check in memory as well (not only in disk). This is done by setting the initialization parameter DB_BLOCK_CHECKSUM to FULL.

Catching corruption in the memory will prevent it at the disk level as well as its propagation to the standby database.

Note: This option is by default disabled because the parameter DB_BLOCK_CHECKSUM has a default value of FALSE. Enabling this option introduce slight performance overhead.

The V$SESSION view enhanced to include tracing information of current session.

Three new columns now show the status of tracing:

o sql_trace—Shows (TRUE/FALSE) if SQL tracing has been enabled in the    session

o sql_trace_waits—If session tracing is enabled, you can have the trace    write wait information to the trace file; very useful in diagnosing    performance issues.

o sql_trace_binds—If the session uses bind variables, you can have the    trace write the bind variable values to the trace file. This column shows    TRUE/FALSE.

Note that the view V$SESSION is populated only if the procedure session_trace_enable in the package dbms_monitor is used to enable tracing, not by alter session set sql_trace=true or setting the event 10046.

DBMS_OUTPUT maximum line length

o In Oracle 10.1 and below - 255 bytes

o In Oracle 10.2 and above - 32767 bytes

DBMS_OUTPUT maximum output buffer size

o In Oracle 10.1 and below - 1000000 bytes

o In Oracle 10.2 and above - unlimited

The V$PARAMETER_VALID_VALUES view is introduced in Oracle 10.2. It returns one row for each valid value for each parameter taking scalar value.

Oracle's Unicode character sets, AL32UTF8 and AL16UTF16, have been updated to support Unicode 4.0 in Oracle Database 10g Release 2. Unicode 4.0 has 1,226 new characters.