“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 |