“Oracle *daily* TidBits” (#oratidbit) published on Facebook, Twitter and Google+ during weekdays in February 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 Setting STATISTICS_LEVEL parameter to BASIC disables AWR. If STATISTICS_LEVEL is set to BASIC, you can still manually capture AWR statistics using the DBMS_WORKLOAD_REPOSITORY package. |
| #oratidbit The $ORACLE_HOME/rdbms/admin/awrextr.sql script extracts AWR data for a range of snapshots from the database into a Data Pump export file (to load into another database). |
| #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. |
| #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 DBMS_SPM PL/SQL package is used to perform SQL plan management tasks – creating, dropping, and loading SQL plan baselines. DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE can be used to show one or more execution plans for the SQL statement. |
| #oratidbit Run the asmcmd command with -p option to include the current directory in the ASMCMD prompt. |
| #oratidbit Active Session History (ASH) sampling is available (#db12c) on Oracle ASM instances. This activity is exposed in the dynamic view V$ACTIVE_SESSION_HISTORY. |
| #oratidbit #db12cR2 Case-insensitive data(base) support available through collation. Can be declared on a schema, table or column level. |
| #oratidbit #db12cR2 The AL32UTF8 character set is the default for the database character set in Oracle Database installs and in the Database Configuration Assistant (DBCA). In earlier versions, the default database character set was based on the operating system locale setting. |
| #oratidbit #db12cR1 OPTIMIZER_ADAPTIVE_FEATURES functions are replaced by two new parameters in #db12cR2 OPTIMIZER_ADAPTIVE_PLANS (default TRUE), and OPTIMIZER_ADAPTIVE_STATISTICS (default FALSE). |
| #oratidbit before installing #DB12cR2 Grid Infrastructure or Database, simplify OS configuration by installing oracle-database-server-12cR2-preinstall RPM. |
| #oratidbit Starting with Oracle Database 12c Release 1 (12.1.0.2), the roothas.sh script replaces the roothas.pl script in the Oracle Grid Infrastructure home. |
| #oratidbit To enable or disable a particular database feature for an Oracle home, shut down the database and use the “chopt” tool. |
| #oratidbit #db12cR2 New function ORA_MAX_NAME_LEN_SUPPORTED can be used to check the maximum length of identifiers allowed in the database. In #DB12cR2, if COMPATIBLE parameter is set to 12.1.0 or lower, the limit is 30 bytes. |
| #oratidbit The pl/sql package DBMS_DB_VERSION provides the Oracle version numbers and other information useful for simple conditional compilation based on version and release. |
| #oratidbit “In a future PL/SQL release, to accommodate emerging SQL standards, VARCHAR might become a separate data type, no longer synonymous with VARCHAR2.” — A note from PL/SQL Language Reference Document. |
| #oratidbit PL/SQL data types PLS_INTEGER, BINARY_FLOAT and BINARY_DOUBLE use hardware arithmetic and are faster than NUMBER data type (uses library arithmetic). |
| #oratidbit PL/SQL package DBMS_PARALLEL_EXECUTE can be used to implement DML parallelism in PL/SQL manually; you can execute a SQL or PL/SQL statement in parallel by taking the data to be processed and breaking it up into multiple smaller chunks. |
| #oratidbit #db12cR2 #sqlplus New SET LOBPREFETCH to define the amount of LOB data (in bytes) to prefetch from database at one time. Default is 0, meaning prefetching is off. Maximum value is 2GB. |
| #oratidbit #db12cR2 #sqlplus New SET ROWPREFETCH to define the number of rows to prefetch from database at one time. Default is 1. The amount of data contained in the prefetched rows should not exceed 2GB. |
Hi there, I have a question about a question on your book: OCA Oracle Database 12c Administrator Certified Associate Study Guide. On the self assessment for the OCA exam, question #37. It basically ass what qualify to be returned by this ‘%S\_J\_C’ ESCAPE ‘\’. A) BTS_J_C and D) S_J_C. The answer on the book says only D while stating that % can be substituted for zero or many characters. So i don’t understand how A is not right. Also, the question asks to choose two correct answers.
Thanks for your time
Both A and D are correct answers. Thanks for pointing out the omission in the answer key.