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
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.