OracleNotes TidBits – May 2014

Oracle TidBit

Oracle TidBit

According to the dictionary, meaning of “tidbit” is “a small and particularly interesting item of gossip or information“… hope you find the “Oracle TidBits” published at interesting…

Here is a compilation of the tidbits published in May 2014. You will also see these tidbits, one tidbit at a time, for each page refresh on the right side of this blog as well…


TidBit Topic
Oracle Database12c introduced a mandatory process namedLREG – Listener Registration. TheLREG registers information about the database instance and dispatcher processes with the Oracle Net Listener. When an instance starts,LREG polls the listener to determine whether it is running. If the listener is running, thenLREG passes it relevant parameters. If it is not running, thenLREG periodically attempts to contact it.In releases before Oracle Database 12c, PMON performed the listener registration. Listener
With ALTER DATABASE RENAME FILE …, you can specify only the name of the file as the original file (not file number), whereas in Oracle Database12c ALTER DATABASE MOVE FILE …, you can specify the file number or file name as source file.If you identify the file by number, then file number is the value found in the FILE# column of the V$DATAFILE view (or the FILE_ID column of the DBA_DATA_FILES view). Data File
In Oracle Database 12c, you can limit the PGA memory using the PGA_AGGREGATE_LIMIT parameter. By default, PGA_AGGREGATE_LIMIT is set to the greater of 2 GB, 200% of PGA_AGGREGATE_TARGET, and 3 MB times the PROCESSES parameter. It will not exceed 120% of the physical memory size minus the total SGA size. Memory
In Oracle Databse 12c, you can make a column in table as INVISIBLE. When you make it invisible, the COL# column in COL$ dictiionary table is updated to 0, thus is not included in the “SELECT *” or “INSERT INTO VALUES”, unless specifically selected. Administration
When you make an INVISIBLE column in Oracle 12c database to VISIBLE, the COL# is assigned the highest available. Thus the column becomes the last column in the table (not storage, only display). So, if you accidentally make a column INVISIBLE and correct it by changing to VISIBLE, the column order changes. So, if the application uses “SELECT *” or “INSERT” without column names, they might break! Administration
The SPARE6 column in SYS.USER$ table keeps track the last successful login time of the user in Oracle Database 12c. This is a very good security feature, without enabling “AUDIT SESSION” to find when the user last used the database. Security
Datapump import in Oracle Database 12c has option to perform import without generating redo (thus no archive logs). Use option TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y[:TABLE|INDEX]. This sets object to NOLOGGIG before import and reverts to original setting after import. DataPump
Oracle Database 12c has option to run the Oracle instance on Unix as threads, consolidating multiple processes to one. By setting THREADED_EXECUTION=YES, allows multiple background processes to share a single OS process on Unix, similar to Windows. In default process models, SPID and STID columns of V$PROCESS will have the same values, whereas in multithreaded models, each SPID (process) will have multiple STID (threads) values. Administration
In Oracle Database 12c, when you move the online data file using ALTER DATABASE MOVE DATAFILE ‘filename’; without the TO clause, the file will be moved to DB_FILE_CREATE_DEST as Oracle Managed File – the DB_FILE_CREATE_DEST location can be ASM diskgroup or file system. Data File
The SYS.USER$ table has tracking columns. CTIME shows when user was created, PTIME shows when user password was last changed, LTIME shows when the user account was locked. In 12c, SPARE6 columns shows last login time of user. Security
V$SYSTEM_FIX_CONTROL displays information about Fix Control (enabled/disabled) at the system level. Shows bug number, sql feature, description of bug, etc in this view. Use _FIX_CONTROL initialization parameter to turn on or off specific optimizer patches and bug fixes. More information in MOS note “How to use the _FIX_CONTROL hidden parameter (Doc ID 827984.1)” Administration
Oracle Database 11gR2 support ends Jan 2015. But, there is good news. The Extended Support fee has been waived for the period of February 2015 – January 2016. Read Miscellaneous
Oracle Database 12c has a new command-line upgrade utility to upgrade database to 12c – This utility replaces the catupgrd.sql script used to upgrade to previous releases of Oracle Database. The new utility enables parallel processing during the database upgrade, resulting in better upgrade performance. Upgrade


Leave a Reply

%d bloggers like this: