Category: TidBits

Posted on

Oracle TidBits – July 2014

“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.
Posted on

Oracle Tidbits – June 2014

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

TidBit Topic
In Oracle Database 12c, the Pre-Upgrade Information Tool script is preupgrd.sql (meaningfully named). Earlier release pre-upgrade tool script is named utluNNNi.sql, NNN is version. The new 12c tool automatically generates fixup scripts to address common issues. One less thing to worry for DBA. Upgrade
The UTL_INADDR package (10g+) provides PL/SQL procedures to support internet addressing. “select utl_inaddr.get_host_address from dual” gives the IP address of the local machine, and “select utl_inaddr.get_host_address(”) from dual” gives the IP of the (similar to nslookup). Network
ASM in Oracle Database 11g supports renaming of diskgroup using renamedg command. In Oracle Database 12c, you can rename a disk within a diskgroup using ALTER DISKGROUP RENAME DISK. It is ideal to run the ALTER DISKGROUP diskgroupname RENAME DISKS ALL statement after the renamedg to change the names of the disks in the renamed disk group. ASM
Oracle Database 12c has new CASCADE clause for TRUNCATE statement. This truncates all child tables that reference the truncated table if foreign key is defined with ON DELETE CASCADE option and the FK constraint is in ENABLED status. Children, grand children are truncated. SQL
In Oracle Database 12c, ALTER DATABASE MOVE DATAFILE clause can be used to rename or move a data file. For this, the datafile must be ONLINE. If the data file is OFFLINE, you have to use the ALTER DATABASE RENAME FILE clause. Data File
In Oracle Database 12c, in addition to LGWR, you could have Log Writer Worker processes. From “Reference” document, appendix F Background Process: On multiprocessor systems, LGWR creates worker processes to improve the performance of writing to the redo log. LGWR workers are not used when there is a SYNC standby destination. Possible processes include LG00-LG99. Administration
In Oracle Database 12c, SQL SELECT statement has a new row limiting clause, using the FETCH and OFFSET key words. In releases prior, row limiting and row filtering could be used interchangeably, but in 12c filtering is strictly using WHERE clause and row limiting is  by using FETCH clause. SQL
Oracle Database 12c ASM can save the password file on ASM for cluster nodes, thus no need to synchronize password file across nodes. All nodes share the same password file. Oracle Database also first searches ASM for password file by default. ASM
In Oracle multitenant database, the seed pluggable database (PDB$SEED) is always in READ ONLY mode. [ok agreed… you may mess with  “_oracle_script” and change its status!] Multitenant
The easy connect string to connect to pluggable database is username[/password]@hostname:[port]/pdbservice. So what is different, nothing. Basically you have to define a service and always use the service. Multitenant
A common user in a multitenant database with the SET CONTAINER privilege can use the “ALTER SESSION SET CONTAINER=” statement to swtich between databases including the root container. When you connect using this method, the AFTER LOGON trigger does not fire. The transactions that are not committed or rolled back in the original container are still in pending state. Thus the SWITCH CONTAINER is different from using CONNECT. When you use CONNECT, the current transaction is committed and new connection is established with the container (PDB). Multitenant
The ISPDB_MODIFIABLE column with TRUE value in V$PARAMETER identifies the parameters that can have a different value for PDB. To change the value of parameter for a PDB, connect to the PDB and use the “ALTER SESSION SET parameter=value [scope=spfile]” statement. If you include “scope=spfile”, then the PDB must be closed and opened to see the new value. Multitenant
Certain initialization parameters in a multitenant database could have different values for individual pluggable database. Such parameter values are not saved in the initialization file, they are stored in  PDB_SPFILE$ and are queriable using V$SYSTEM_PARAMETER. The CON_ID column identifies the puluggable database. Multitenant
In Oracle Database 12c, in addition to DBA_, ALL_ & USER_ dictionary views, there are the CDB_ views showing objects in the multitentant container database across all pluggable databases. All CDB_ views include a container id (CON_ID) column to identify the pluggable database (or root). Multitenant
DBA_HIST_INTERCONNECT_PINGS shows measured latency of interconnect messages (round-trip) from instance to instance in RAC. The PING process assesses the latencies associated with communications for each pair of instances. Every few seconds, the process in one instance (INSTANCE_NUMBER value) sends two messages to each instance (TARGET_INSTANCE value). One message has a size of 500 bytes and the other has a size of 8 KB. The message is received by the PING process on the target instance and is immediately acknowledged. The time for the round-trip is measured and collected. RAC
Oracle provides scripts to copy AWR data to another database (a central repository). The scripts are named awrextr (extract from source) and awrload (load to repository) under $ORACLE_HOME/rdbms/admin. To generate the reports from central repository use awrrpti.sql (standard report), awrddrpi.sql (period comparison report), awrgrpti.sql (report for RAC) and awrgdrpi.sql (RAC period comparison report)- these accept the database id and instance id as input. Administration
In multitenant database architecture which components are local to the pluggable database? Application tablespaces, local temporary tablespaces, local users, local metadata, local PDB resource manager plans. Multitenant
In multitenant container database architecture, control files, redo log files, undo tablespace and temporary tablespace are common to all pluggable databases – these components are tied to the container or CDB. But each pluggable database could have its own temporary tablespace for local users. Multitenant
In Oracle Database 12c, DBMS_UTILITY.EXPAND_SQL_TEXT can be used to regenerate the SQL using only tables. This procedure recursively replaces any view references in the input SQL query with the corresponding view subquery. SQL
The UTL_INADDR package (10g+) provides PL/SQL procedures to support internet addressing. “select utl_inaddr.get_host_name from dual” gives the host name of the local machine, and “select utl_inaddr.get_host_name(”) from dual” gives the name of the machine where the belongs (similar to nslookup). Network
In Oracle Database 12c, the ALTER TABLE … ADD PARTITION can be used to add multiple partitions. Multiple Range partitions listed in ascending order of their upper bound values to the high end (after the last existing partition) of a Range-partitioned table can be added (the MAXVALUE partition should not exist). Similarly, you can add multiple list partitions to a table using new sets of partition values if the DEFAULT partition does not exist. Administration