Oracle Tidbits – January 2017 #oratidbit

Oracle *daily* TidBits” (#oratidbit) published on Facebook, Twitter and Google+ during weekdays in January 2017. 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.

#oratidbit #DB12cR2 In 12.2, Local temporary tablespace can be created using LOCAL TEMPORARY TABLESPACE clause of CREATE TABLESPACE. In RAC environment, local temporary tablespaces store a separate, nonshared temp files for each database instance, which can improve I/O performance. A local temporary tablespace must be a BIGFILE tablespace.
#oratidbit A non-partitioned table in Oracle #DB12cR2 can be converted to a partitioned table by using the MODIFY clause of ALTER TABLE. MODIFY PARTITION clause specifies the partition properties.
#oratidbit Starting with #Exadata release 12.1.2.2.0, Oracle Exadata database nodes, Oracle Exadata Virtual Server nodes (dom0), and Oracle Exadata Virtual Machines (domU) can be updated, rolled back, and backed up using patchmgr. Using patchmgr enables you to run a single command to update multiple nodes; you do not need to run dbnodeupdate.sh separately on each node. patchmgr can update the nodes in a rolling or non-rolling fashion.
#oratidbit #db12cR2 The OPTIMIZER_ADAPTIVE_FEATURES initialization parameter is desupported in Oracle Database 12c Release 2 (12.2.0.1).
#oratidbit #db12cR2 The ON STATEMENT refresh mode refreshes materialized views every time a DML operation is performed on any base table, without the need to commit the transaction. This mode does not require you to maintain materialized view logs on the base tables.
#oratidbit In #db12c the SIMILAR value for the CURSOR_SHARING initialization parameter is deprecated. Use FORCE instead.
#oratidbit #db12cR2 New initialization parameter DATA_GUARD_SYNC_LATENCY, enables you to define the maximum amount of time (in seconds) that the primary database may wait before disconnecting subsequent standby destinations after at least one synchronous standby has acknowledged receipt of the redo.
#oratidbit #db12cR2 Using initialization parameter ENABLED_PDBS_ON_STANDBY specify a subset of pluggable databases (PDBs) for replication on a physical standby of a multitenant container database (CDB). In 12.1, you had to replicate either all PDBs or none.
#oratidbit #db12cR2 Password file changes done on the primary database are now automatically propagated to standby databases. The only exception to this is far sync instances. Updated password files must still be manually copied to far sync instances because far sync instances receive redo, but do not apply it.
#oratidbit #db12cR2 DBMS_JOB package is deprecated, and may be desupported in a future release. Move to DBMS_SCHEDULER.
#oratidbit #db12cR2 By default, permissions to read and write to the System Global Area (SGA) are limited to the Oracle software installation owner only. In previous releases, both the Oracle installation owner account and members of the OSDBA group had access to shared memory.
#oratidbit #db12cR2 Initialization parameter ALLOW_GROUP_ACCESS_TO_SGA determines if the Oracle Database installation owner account is the only user that can read and write to the database System Global Area (SGA), or if members of the OSDBA group can read the SGA. In Oracle Database 12c Release 2 (12.2), the default value for this parameter is FALSE.
#oratidbit #db12cR2 Parallel Upgrade Utility using command-line batch script catupgrd.sql is no longer distributed with 12.2. Run the parallel upgrade utility by using command dbupgrade on Linux/Unix and dbupgrade.com for Windows.
#oratidbit Oracle #DB12c Feature: In-database archiving is enabled by using the attribute ROW ARCHIVAL in the CREATE TABLE or ALTER TABLE statements. Invisible column ORA_ARCHIVE_STATE is added to the table with default value 0 (not archived).
#oratidbit #db12cR2 The SYSRAC administrative privilege is used by the Clusterware agent in its connection to the database on behalf of Oracle RAC utilities, such as SRVCTL. The SYSRAC privilege provides only the minimal privileges necessary for performing day-to-day Oracle RAC operations.
#oratidbit #db12cR2 SQL*Plus supports command history; SET HISTORY enables users to run, edit, or delete previously used SQL*Plus, SQL, or PL/SQL commands from the history list in the current session.
#oratidbit #db12cR2 The SET MARKUP SQL*Plus command supports the display of data in CSV format. Produce output in HTML or CSV format.
#oratidbit #db12cR2 The SET FEEDBACK ONLY SQL*Plus command returns just the number of rows returned by the query without displaying the query result.
#oratidbit #db12cR2 CDB root and individual PDBs store, view, and manage AWR data. You can take an AWR snapshot at a CDB-level (root), as well as at a PDB-level (individual PDBs). In 12cR1, AWR snapshots are at CDB level (root) and included information from CDB and all PDBs.
#oratidbit #db12cR2 When you take AWR snapshot at CDB level, snapshot data is stored in SYSAUX tablespace of CDB. The PDB-level snapshot data is stored in the SYSAUX tablespace of a PDB.