Category: TidBits

“Oracle *daily* TidBits” published at https://www.facebook.com/oraclenotes on all weekdays in September 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
3-Sep	In 12.1.0.2, the CREATE INDEX has a new clause COMPRESS ADVANCED LOW to enable advanced index compression. Advanced index compression improves compression ratios significantly.
4-Sep	In 12.1.0.2, the ALTER DATABASE statement has a new clause FORCE FULL DATABASE CACHING to enable the force full database caching mode. The force full database caching mode considers the entire database, including NOCACHE LOBs, as eligible for caching in the buffer cache.
2-Sep	READ object privilege available from 12.1.0.2 version of 12c database is an alternative to SELECT privilege. With SELECT privilege, you can lock the rows using the LOCK TABLE and SELECT … FOR UPDATE statements, with READ locking of rows is not possible.
5-Sep	The READ ANY TABLE system privilege is an alternative to the SELECT ANY TABLE system privilege. In addition to querying objects, the SELECT ANY TABLE privilege allows users to lock rows of a table with the SELECT … FOR UPDATE statement. The READ ANY TABLE privilege only allows users to query objects.
8-Sep	New COMMON_USER_PREFIX parameter: A common user in a multitenant database is a database user that has the same identity in the root and in every existing and future PDB. Every common user can connect to and perform operations within the root, and within any PDB in which it has privileges. The common user is created in the root container and must begin with C## in version 12.1.0.1. From 12.1.0.2 onwards, you can set the prefix characters by using the intialization parameter COMMON_USER_PREFIX.
10-Sep	The DBMS_STATS.REPORT_GATHER_*_STATS functions can be used to run statistics gathering procedures in reporting mode. In this case, the optimizer does not actually gather statistics, but reports objects that would be processed if you were to use a specified statistics gathering function. The output can be in XML, HTML or TEXT format.
12-Sep	The DBMS_STATS.REPORT_STATS_OPERATIONS function generates a report of all statistics operations that occurred between two points in time. You can narrow the scope of the report to include only automatic statistics gathering runs.
15-Sep	In Oracle Database 12c, the catupgrd.sql Upgrade Utility has been replaced with the catctl.pl Parallel Upgrade Utility. You can run upgrade scripts and processes in parallel, taking full advantage of CPU capacity and shortening the upgrade time. By default DBUA runs in parallel upgrade mode.
17-Sep	The emremove.sql script drops the Oracle Enterprise Manager-related schemas and objects. Use this script to manually remove EM DB Control during upgrade of pre-12c databsae to 12c. Running emremove.sql before starting the upgrade process minimizes upgrade downtime, as it is required to drop EM during 12c upgrade. DB Control is replaced with EM Express in 12c.
19-Sep	When upgrading Oracle database, DBUA (in 11gR2 and 12c) expects that both the source (pre-upgrade) Oracle home and the destination (newly upgraded) Oracle home are owned by the same user.
23-Sep	The AUDSYS internal database user owns the unified audit trail information in Oracle Database 12c. The audit information is queryable using the read only view UNIFIED_AUDIT_TRAIL.
25-Sep	In Oracle Database 12c, there can be up to 100 Database Writer Processes (configured using DB_WRITER_PROCESSES parameter). The names of the first 36 Database Writer Processes are DBW0-DBW9 and DBWa-DBWz. The names of the 37th through 100th Database Writer Processes are BW36-BW99.

“Oracle *daily* TidBits” published at https://www.facebook.com/oraclenotes on all weekdays in August 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…

Date	TidBit
1-Aug	X$NULL is an internal account that represents the absence of database user in a session and the actual session user is an application user supported by Oracle Real Application Security. XS$NULL has no privileges and does not own any database object. No one can authenticate as XS$NULL, nor can authentication credentials ever be assigned to XS$NULL.
4-Aug	The Unified Auditing in Oracle Database 12c is enabled by relinking the Oracle executable using command “make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME”. On Windows platforms, just rename the “%ORACLE_HOME%/bin/orauniaud12.dll.option” file to “%ORACLE_HOME%/bin/orauniaud12.dll”.
5-Aug	If you are not licensed for Diagnostic and Tuning Pack of Oracle Database, you can set the parameter CONTROL_MANAGEMENT_PACK_ACCESS to NONE. The default value is DIAGNOSTIC+TUNING, meaning both packs are licensed. Set to DIAGNOSTIC to enable diagnostic pack that includes automatic database diagnostic monitoring (ADDM) and AWR. The TUNING pack includes SQL Tuning Advisor, SQL Access Advisor, and so on.
6-Aug	When creating CDB in Oracle Database 12c, make sure you pick appropriate character set. Only databases with a compatible character set can be plugged into the CDB.
7-Aug	New in 12.1.0.2: The STANDBYS clause of the CREATE PLUGGABLE DATABASE statement specifies whether the new PDB is included in standby CDBs. You can specify ALL to include the new PDB in all of the standby CDBs. Specify NONE to exclude the new PDB from all of the standby CDBs.
8-Aug	In Multitenant database configuration, a CDB uses a single SPFILE or a single text initialization parameter file (PFILE). Values of initialization parameters set for the root container can be inherited by PDBs. The root (CDB$ROOT) must be the current container when you operate on an SPFILE.
11-Aug	New in 12.1.0.2: The NO DATA clause of the CREATE PLUGGABLE DATABASE statement specifies that a PDB’s data model definition is cloned but not the PDB’s data. The dictionary data in the source PDB is cloned, but all user-created table and index data from the source PDB is discarded. This clause is useful for quickly creating clones of a PDB with only the object definitions and no data. This clause does not apply to the SYSTEM and SYSAUX tablespaces.
12-Aug	In Multitenant database configuration, the PDB can inherit the default timezone from the root container or can set its own timezone.
13-Aug	In Multitenant database configuration, there is a default temporary tablespace for the entire CDB. You optionally can create additional temporary tablespaces for use by individual PDBs.
14-Aug	In a CDB, the UNDO_MANAGEMENT initialization parameter must be set to AUTO, and an undo tablespace is required to manage the undo data. Only a common user who has the appropriate privileges and whose current container is the root (CDB$ROOT) can create an undo tablespace.
15-Aug	To create a CDB, the CREATE DATABASE statement must include the ENABLE PLUGGABLE DATABASE clause. When the ENABLE PLUGGABLE DATABASE clause is not included in the CREATE DATABASE statement, the newly created database is a non-CDB.
18-Aug	The USER_TABLESPACES clause available in 12.1.0.2 of the CREATE PLUGGABLE DATABASE statement specifies which tablespaces are available in the new PDB. Use this clause to separate the data for multiple schemas into different PDBs. For example, when you move a non-CDB to a PDB, and the non-CDB had a number of schemas that each supported a different application, you can use this clause to separate the data belonging to each schema into a separate PDB, assuming that each schema used a separate tablespace in the non-CDB.
19-Aug	The PATH_PREFIX clause of the CREATE PLUGGABLE DATABASE statement ensures that all relative directory object paths associated with the PDB are restricted to the specified directory or its subdirectories. Use this clause when you want to ensure that a PDB’s files reside in a specific directory and its subdirectories when relative paths are used for directory objects.
20-Aug	When you drop a CDB using DROP DATABASE statement, the root, seed, and all of its all of its PDBs (including their data) are also dropped. Individual PDBs are dropped using the DROP PLUGGABLE DATABASE statement.
21-Aug	In a CDB environment, ADDM does not analyze one PDB at a time. ADDM runs with a target of either the entire instance or Oracle RAC database.
22-Aug	Most CDB_ dictionary views in a CDB include the CON_ID column. Value of 0 identifies the data pertains to entire CDB, 1 pertains to root, 2 pertains to seed and 3-254 pertains to each PDB.