“Oracle *daily* TidBits” published at https://www.facebook.com/oraclenotes on all weekdays in July 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… Hope you find these helpful to learn something new or to remind you of its existence and use…
| Publish Date | TidBit |
| 1-Jul | The OPERATION column in CDB_PDB_HISTORY view shows how each pluggable database was created, and the CLONED_FROM_PDB shows the source. You must connect to the root container to view this information. |
| 2-Jul | Oracle Database 12c introduced new administrative privilege for RMAN operations. The new SYSBACKUP privilege lets you not use the SYSDBA privilege for backup and recovery operations using RMAN or SQL*Plus. The privilege includes ability to shutdown, startup, alter database along with other privileges. |
| 3-Jul | SQL*Plus in Oracle Database 12c displays the time of last successful login to database (default). This security feature can be turned off with a SQLPLUS command option -nologintime. |
| 4-Jul | In Oracle Database 12c RMAN command line interface, there is no need to prefix SQL statements with “SQL” and enclose them in quotes. |
| 7-Jul | In Oracle Database 12c, similar to adding multiple partitions in one statement, you can also drop multiple partitions using the ALTER TABLE … DROP PARTITIONS clause. |
| 8-Jul | You can turn off logging for Data Pump import in Oracle Database 12c using the new transform parameter TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y. Logging is not disabled if database is running in FORCE LOGGING mode. |
| 9-Jul | Another TRANSFORM parameter introduced in Oracle Database 12c Data Pump Import is the TABLE_COMPRESSION_CLAUSE. It lets you specify the compression clause for destination table irrespective of the source table setting. The valid values may be NOCOMPRESS, COMPRESS FOR OLTP, COMPRESS FOR QUERY LOW, COMPRESS FOR ARCHIVE… |
| 10-Jul | V$OBJECT_USAGE displays statistics about index usage gathered from the database for the indexes owned by the current user. You can use this view to monitor index usage. All indexes that have been used at least once can be monitored and displayed in this view. The V$OBJECT_USAGE view is deprecated in Oracle Database 12c Release 1 (12.1) and maintained for backward compatibility. Oracle recommends that you use the USER_OBJECT_USAGE view instead of the V$OBJECT_USAGE view. |
| 11-Jul | In Oracle Database 12c, you can truncate multiple partitions using the ALTER TABLE … TRUNCATE PARTITIONS clause. |
| 14-Jul | To manually purge the Optimizer Stats, use procedure dbms_stats.purge_stats (timestamp). The Optimizer Statistics history retention can be changed at the database using procedure dbms_stats.alter_stats_history_retention (days). The current value can be obtained using dbms_stats.get_stats_history_retention function. The oldest date of stats history availability can be checked using dbms_stats.get_stats_history_availability function. |
| 15-Jul | Use the DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE procedure to delete AWR snapshots that you no longer need. You can change the AWR snapshot retention using the procedure DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS, use the RETENTION parameter. |
| 16-Jul | ADRCI utility in 11g+ (Automatic Diagnostic Repository Command Interface) can be used to purge the trace and alert log files among others. Use command “purge -age -type TRACE” to purge trace files. The default purging is set using the “set control (SHORTP_POLICY = )” in adrci. You can view the current value (default 720) of the policy using “select SHORTP_POLICY from ADR_CONTROL”. |
| 17-Jul | Recycle bin in Oracle database was introduced in Oracle 10g. You can purge the recycle bin contents using the “PURGE DBA_RECYCLE_BIN” statement. It also gives option for selective purging using TABLE (specific table), INDEX (specific index), RECYCLEBIN (current user’s recyclebin), TABLESPACE (specific tablespace)… |
| 18-Jul | The Oracle database audit trail can be purged using procedure DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL. Type of audit trail records to purge can be specified using the AUDIT_TRAIL_TYPE parameter. If you specify parameter USE_LAST_ARCH_TIMESTAMP as FALSE, all audit records of AUDIT_TRAIL_TYPE will be removed. |
| 21-Jul | There are two parameters that control the enable of Automatic Database Diagnostic Monitoring (ADDM). The STATISTICS_LEVEL should be at TYPICAL (default) or ALL – BASIC turns ADDM off. The CONTROL_MANAGEMENT_PACK_ACCESS should be set to DIAGNOSTIC+TUNING (default) or DIAGNOSTIC – NONE turns ADDM off. |
| 22-Jul | Prior to Oracle Database 12c, having multiple tables on the left hand side of an outer join was illegal and resulted in an ORA-01417 error. The only way to execute such a query was to translate it into ANSI syntax. In Oracle Database 12c, the native syntax for a LEFT OUTER JOIN has been expanded to allow multiple tables on the left hand side. |
| 23-Jul | While creating Oracle Database 12c database, DBCA picks a free port from 5500 to 5599 for Enterprise Manager Express. If you want a specific port to be used, set the environment variable DBEXPRESS_HTTPS_PORT before starting Oracle Universal Installer (OUI) or Database Configuration Assistant (DBCA). |
| 24-Jul | The APPROX_COUNT_DISTINCT (expr) function is available starting with Oracle Database 12c 12.1.0.2. This function provides an alternative to the COUNT (DISTINCT expr) function, which returns the exact number of rows that contain distinct values of expr. APPROX_COUNT_DISTINCT processes large amounts of data significantly faster than COUNT, with negligible deviation from the exact result. APPROX_COUNT_DISTINCT ignores rows that contain a null value for expr. |
| 25-Jul | In Oracle Database 12c, for nonadministrative users to have access to EM Express, they must be granted the EM_EXPRESS_BASIC or the EM_EXPRESS_ALL role. The EM_EXPRESS_BASIC role enables users to connect to EM Express and to view the pages in read-only mode. The EM_EXPRESS_BASIC role includes the SELECT_CATALOG_ROLE role. The EM_EXPRESS_ALL role enables users to connect to EM Express and use all the functionality provided by EM Express (read/write access to all EM Express features). The EM_EXPRESS_ALL role includes the EM_EXPRESS_BASIC role. |
| 28-Jul | In Oracle Database 12c, the deinstallation tool is integrated with the database installation media. You can run the deinstallation tool using the runInstaller command with the -deinstall and -home options from the base directory of the Oracle Database, Oracle Database Client or Oracle Grid Infrastructure installation media. The deinstallation Tool (deinstall) is also available in Oracle home directories after installation as $ORACLE_HOME/deinstall/deinstall. Do not shut down the Oracle database or stop any database processes prior to running the deinstallation tool. |
| 29-Jul | The RESOURCE role in Oracle Database 12c only includes the following privileges: CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, CREATE TYPE. |
| 30-Jul | The Fast Recovery Area (FRA) is configured by using the DB_RECOVERY_FILE_DEST_SIZE and DB_RECOVERY_FILE_DEST parameters. The DB_RECOVERY_FILE_DEST_SIZE parameter must be set before the DB_RECOVERY_FILE_DEST parameter. |
| 31-Jul | The SEC_RETURN_SERVER_RELEASE_BANNER parameter controls the display of the product version information, such as the release number, in a client connection. An intruder could use the database release number to find information about security vulnerabilities that may be present in the database software. You can enable or disable the detailed product version display by setting this parameter. |