Oracle Tidbits – October 2016 #oratidbit

Oracle TidBit
Oracle TidBits

Oracle *daily* TidBits” (#oratidbit) published at https://www.facebook.com/oraclenotes on weekdays in October 2016. 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 In Oracle #DB12c 12.2, if Container database Character Set is AL32UTF8 then PDBs are allowed to have different character set.
#oratidbit Oracle Health Check Collections Manager is a companion application to Oracle #ORAchk that gives you an enterprise-wide view of health check collection data. It provides a dashboard to track your collection data in one easy-to-use interface using APEX.
#oratidbit Oracle #ORAchk health check collection can be scheduled to run periodically by using the -set option. The AUTORUN_INTERVAL daemon option provides an alternative method of regularly running health checks.
#oratidbit Oracle #ORAchk and Oracle #EXAchk automatically compare the two most recent HTML reports and generate a third diff report, when run in automated daemon mode.
#oratidbit Oracle #ORAchk and Oracle #EXAchk are capable of uploading collection results from multiple instances into a single database for easier consumption of check results using Oracle Health Check Collections Manager or using custom application.
#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 In Oracle #DB12cR2 new function VALIDATE_CONVERSION can be used to identify problem data that cannot be converted to the required data type. It returns 1 if a given expression can be converted to the specified data type, else it returns 0. Example: VALIDATE_CONVERSION(‘$100,00’ AS NUMBER, ‘$999D99’, ‘NLS_NUMERIC_CHARACTERS = ”,.”’) returns 1; VALIDATE_CONVERSION(‘$29.99’ AS BINARY_FLOAT) returns 0.
#oratidbit In Oracle #DB12cR2 many data type conversion functions (TO_DATE, TO_NUMBER, etc) and CAST function includes data error handling using the “DEFAULT value ON CONVERSION ERROR” option. Example: TO_NUMBER(‘2,00’ DEFAULT 0 ON CONVERSION ERROR) returns 0 instead of error.
#oratidbit In Oracle #DB12cR2 a table segment or table partition segment can be moved to a new segment (or tablespace) with the ONLINE keyword in ALTER TABLE … MOVE while DML operations can continue to run uninterrupted on the table.
#oratidbit In Oracle #DB12cR2 most object names can be as long as 128 bytes. You no longer have to limit a table name to 30 characters, for example. Exceptions are database names (8 bytes); Names of disk groups, pluggable databases (PDBs), rollback segments, and tablespaces (30 bytes).
#oratidbit In Oracle #DB12c, the DBMS_ILM package provides an interface for implementing Information Lifecycle Management (ILM) strategies using Automatic Data Optimization (ADO) policies.
#oratidbit Oracle #DB12c Feature: In-database archiving enables to archive rows within a table by marking them as invisible. These invisible rows are in the database but are not visible to an application. The data in these rows is available for compliance purposes if needed by setting session parameter “alter session set row archival visibility = all”.
#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 Materialized View Synchronous refresh is an approach for maintaining tables and materialized views in a data warehouse where tables and materialized views are refreshed at the same time. Tables are registered with synchronous refresh by creating a staging log on them.
#oratidbit DBA_OPTSTAT_OPERATION_TASKS displays the history of tasks that are performed as part of statistics operations (recorded in DBA_OPTSTAT_OPERATIONS). Each task represents a target object to be processed in the corresponding parent operation. Task start time and end time columns help to know duration of each task.
#oratidbit The HugePages Linux OS feature allocates non-swappable memory for large page tables using memory-mapped files. Automatic Memory Management should not be used when using HugePages – both MEMORY_TARGET and MEMORY_MAX_TARGET initialization parameters are unset.
#oratidbit In #DB12c 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.
#oratidbit While creating #DB12c, 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).
#oratidbit Oracle Wallet Manager is used to manage public key security credentials on Oracle clients and servers. The wallets it creates can be read by Oracle Database, Oracle Application Server, and the Oracle Identity Management infrastructure.