OracleNotes

Author: Biju

  • 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 https://www.facebook.com/oraclenotes 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 http://www.oracle.com/us/support/library/057419.pdf Miscellaneous
    Oracle Database 12c has a new command-line upgrade utility to upgrade database to 12c – catctl.pl. 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

     

  • Two Cool Utilities in DBMS_UTILITY

    Oracle Database DBMS_UTILITY package has several useful subprograms. GET_DEPENDENCY shows the objects depended on the object. Here is an example (the procedure uses DBMS_OUTPUT to show the result, hence SET SERVEROUTPUT is required in SQL*Plus):

    SQL> set serveroutput on
SQL> exec dbms_utility.get_dependency('VIEW','HR','EMP_DETAILS_VIEW');
-
DEPENDENCIES ON HR.EMP_DETAILS_VIEW
------------------------------------------------------------------
*VIEW HR.EMP_DETAILS_VIEW()

PL/SQL procedure successfully completed.

SQL> exec dbms_utility.get_dependency('TABLE','HR','EMPLOYEES');
-
DEPENDENCIES ON HR.EMPLOYEES
------------------------------------------------------------------
*TABLE HR.EMPLOYEES()
    VIEW HR.EMP_DETAILS_VIEW()
    TRIGGER HR.SECURE_EMPLOYEES()
    TRIGGER HR.UPDATE_JOB_HISTORY()

PL/SQL procedure successfully completed.

SQL>

    Oracle Database 12c has a new procedure to provide you with the SQL behind the SQL. DBMS_UTILITY.EXPAND_SQL_TEXT. Useful to expand the views and see the base tables involved, as well as to reveal the real SQL, especially when clauses like “FETCH and OFFSET” are used.

    There are two CLOB parameters to this procedure – input SQL text, and output SQL text. Try this in your Oracle Database 12c…

    SQL> set long 32000
SQL> variable x1 CLOB
SQL> exec dbms_utility.expand_sql_text('SELECT TABLE_NAME FROM DBA_TABLES',:x1);
SQL> print x1