 | 1998/10 In PL/SQL you can suspend a session using dbms_lock.sleep (seconds)
which is equivalent to the 'sleep' command in UNIX. The parameter (number data type)
can even be specified in hundredth of a second (10, 10.01, 10.5, 0.99 are all legal
values). |
| 1998/11 When you TRUNCATE a table, the storage parameter NEXT is automatically reset to the last
extent deleted. Similarly when you de-allocate extents from a table, the NEXT is
automatically reset to the size of the last extent deallocated. |
| 1998/12 In PL/SQL you can assign a
rollback segment to a transaction (without using dynamic sql) by utilizing
the Oracle supplied package dbms_transaction.use_rollback_segment ('rbs_name'). |
| 1999/01 The SQL select
to_char(to_date(123456,'J'), 'JSP') from dual; can be used to convert
numbers to words, here is the result: ONE HUNDRED TWENTY-THREE THOUSAND FOUR
HUNDRED FIFTY-SIX. |
| 1999/02 To find the difference
between date columns in Days, Hours, Minutes & Seconds use the SQL -
Select trunc(date2 - date1) days, to_char( to_date( trunc(
mod(date2-date1,1) * 86400), 'SSSSS'), 'HH24:MI:SS') time from tablename; |
| 1999/03 The file sgadefSID.dbf is
created at instance startup in $ORACLE_HOME/dbs directory and its function
is to store the address of the memory location of the SGA. When the instance
is shutdown, this file is deleted. |
| 1999/04 In oracle7, you have to
query the table SYS.FILEXT$ to find if AUTOEXTEND feature for a file is
enabled and this table is only created if the AUTOEXTEND feature is ever
turned on in any datafile in the database. The values for MAXEXTEND (max
file size) and INC (increment) are expressed in BLOCKS. In Oracle8, the
DBA_DATA_FILES view has this info. |
| 1999/05 In oracle8i, to drop a
column from a table, you no longer have to create a temp table and do insert!! Cool!!
The drop column clause in ALTER TABLE lets you free space in the database by
dropping columns you no longer need, or by marking them to be dropped at a
future time when the demand on system resources is less. You can view the
tables with columns marked unusable in DBA_UNUSED_COL_TABS. |
| 1999/06 In oracle8i, you can fire a
trigger on database STARTUP, SHUTDOWN, SERVERERROR; user LOGON, LOGOFF;
object CREATE, ALTER, DROP. The trigger defined in database level will fire
for all users and schema level will fire only for the specific user when the
event occurs. |
| 1999/07 In oracle8i, there is a new
option available to shutdown database "SHUTDOWN TRANSACTIONAL".
After submitting this statement, no client can start a new transaction on
this instance. If clients attempt to start a new transaction, they are
disconnected. After all transactions have completed, any client still
connected to the instance is disconnected. |
| 1999/08 In oracle8i, you can create
temporary tables, which are created in users temporary tablespace. Its
definition is visible to all sessions but the data is visible to, and can be
queried by, only the session that inserts the data into the table. |
| 1999/09 In oracle8i, you can
reorganize a non-partitioned table without doing export and import. The
table can be moved from one tablespace to another also, operation requires
double the amount of space. (ALTER TABLE mytable MOVE TABLESPACE
newtablespace;) |
| 1999/10 In oracle8i, TRIM function is available, which is a combination of the
existing LTRIM and RTRIM functions, allowing the user to trim leading and trailing
characters from a character string. |
| 1999/11 In Oracle 8i, you can create an index reverse keyed [CREATE INDEX i ON t (a,b,c) REVERSE;]. Since lexically adjacent keys are not stored next to each other in a reverse-key index, only fetch-by-key or full-index (table) scans can be performed. |
| 1999/12 In Oracle8, the V$INSTANCE
system view has many useful information like the hostname, startup time,
version, etc. |
| 2000/01 Oracle error codes from
10000 to 10999 are pseudo-error messges which are used for setting event
traces to debug. |
| 2000/02 A temporary segment created on a
TEMPORARY tablespace is released only at shutdown, the temporary segments are reused; whereas a temporary segment created on a PERMANENT tablespace is cleaned up by the SMON process once the completion of the statement and the space is released. |
| 2000/03 In Oracle8i, on most of the
UNIX platforms, the sgadefSID.dbf is not created when the database is
started. If you use sgadefSID.dbf in the scripts to verify if the database
is up, you may need to look for the pmon_SID process from now on. |
| 2000/04 The V$RESERVED_WORDS system
view has all the keywords that are reserved by Oracle. |
| 2000/05 In Oracle8i, you need not
recreate the control file to adjust MAXDATAFILES defined at database
creation, when increasing the DB_FILES parameter, Oracle automatically
adjusts the control file. |
| 2000/06 In Oracle8i, you can specify
up to 5 archive log destinations using the LOG_ARCHIVE_DEST_n parameter. |
| 2000/07 In release 8.1.6, block
checking and checksums are turned on for the SYSTEM tablespace,
allowing the Oracle server to detect and repair any SYSTEM
tablespace corruptions before they are written to disk. |
| 2000/08 OUTLN schema in Oracle8i
database has a default password of OUTLN. This schema has some powerful
privileges and hence the password must be changed. |
| 2000/09 Statspack collects
high-resource SQL whereas UTLESTAT does not. |
| 2000/10 You can upload the utlestat
reports and statspack reports to www.oraperf.com
for analyzing. |
| 2000/11 In Oracle8i, ALTER SYSTEM
SUSPEND statement can suspend all I/O operations of the database, enabling
hot backups using OS mirror splits. |
| 2000/12 In Oracle8i, use $ORACLE_HOME/rdbms/admin/utlrp.sql
script to recompile all invalid PL/SQL stored programs, triggers and views.
You must connect as SYS or INTERNAL. |
| 2001/01 In Oracle8i, you can
execute DDL commands inside PL/SQL block using the "EXECUTE
IMMEDIATE" statement. Prior to 8i, you need to parse and execute the
command using the DBMS_SQL package. |
| 2001/02 In Oracle8i, temporary
tables can be created using CREATE GLOBAL TEMPORARY TABLE statement, data in
such tables are visible only to the session that inserts rows. Rows are
deleted at the end of transaction or end of session (based on the ON COMMIT
clause table definition). |
| 2001/03 You can move a table from
one tablespace to another using the MOVE clause of ALTER TABLE statement.
All indexes of moved table need to be rebuilt. |
| 2001/04 In Oracle8i, non-unique
indexes can be created to enforce unique and primary key constraints. |
| 2001/05 In Oracle7, SELECT ANY TABLE
privilege gave access to dictionary views such as DBA_ and V$ views. In
Oracle8i, you need to set O7_DICTIONARY_ACCESSIBILITY=TRUE to achieve this behavior.
The new predefined roles SELECT_CATALOG_ROLE and EXECUTE_CATALOG_ROLE can be
used in 8i. |
| 2001/06 Composite partitioning in
Oracle8i uses range partitioning for partitions and hash partitioning for
sub-partitions. Only sub-partitions store data. |
| 2001/07 Oracle8i 8.1.6 release on
UNIX platforms do not have the "sqlload" executable, instead comes
with "sqlldr" executable. If your scripts are using "sqlload",
you need to make a soft link using "ln -s sqlldr sqlload" |
| 2001/08 With Oracle8i 8.1.7, a new
tool named iSQL*Plus is available, which is a browser based SQL*Plus tool.
No need to install client software on every PC to use SQL*Plus!! |
| 2001/09 Oracle9i supports ANSI SQL99
standard for specifying join between tables. You can use NATURAL JOIN, CROSS
JOIN, JOIN ON, JOIN USING, LEFT OUTER JOIN, RIGHT OUTER JOIN and FULL JOIN
syntaxes. |
| 2001/10 The TIMESTAMP data type in
Oracle9i can store Date/Time values with up to 9 digits precision for
seconds. TIMESTAMP WITH [LOCAL] TIME ZONE data type can also store time zone
information. |
| 2001/11 In Oracle9i, you can define
primary key, unique key and foreign key constraints on views. These constraints
are declarative hence the only valid state is DISABLE NOVALIDATE. |
| 2001/12 In Oracle9i, ALTER INDEX
<index name> MONITORING USAGE statement can monitor if an index is
used. |
| 2002/01 In Oracle9i, Server manager
tool is not available. You need to use "sqlplus /nolog" instead of
"svrmgrl" and "connect / as sysdba" instead of
"connect internal". |
| 2002/02 In Oracle9i, CASE expression
can be used instead of using complex DECODE functions. |
| 2002/03 Prior to Oracle9i, database
buffer cache size was determined by DB_BLOCK_BUFFERS times DB_BLOCK_SIZE. In
Oracle9i, the DB_CACHE_SIZE parameter specifies the size. |
