Oracle Tidbits – March 2015

Oracle *daily* TidBits” (#oratidbit) published at https://www.facebook.com/oraclenotes on weekdays in March 2015. 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…

2-Mar #oratidbit You can perform DDL operations (such as ALTER TABLE, DROP TABLE, CREATE INDEX) on a global temporary table (GTT) only when no session is bound to it. A session becomes bound to a temporary table by performing an INSERT operation on the table. A session becomes unbound to the temporary table by issuing a TRUNCATE statement or at session termination, or, for a transaction-specific temporary table, by issuing a COMMIT or ROLLBACK statement.
3-Mar #oratidbit A global temporary table cannot be partitioned, clustered, or index organized. It cannot have a foreign key and cannot contain INVISIBLE columns.
4-Mar #oratidbit An INVISIBLE column in a table can be used as a partitioning key. A virtual column can be INVISIBLE.
5-Mar #oratidbit A virtual column in a table is not stored on disk. Rather, the database derives the values in a virtual column on demand by computing a set of expressions or functions. Virtual columns can be used in queries, DML, and DDL statements. They can be indexed, and you can collect statistics on them.
6-Mar #oratidbit You cannot directly update a virtual column in a table. Thus, you cannot specify a virtual column in the SET clause of an UPDATE statement. However, you can specify a virtual column in the WHERE clause of an UPDATE statement. Likewise, you can specify a virtual column in the WHERE clause of a DELETE statement to delete rows from a table based on the derived value of the virtual column.
9-Mar #oratidbit If you are not the owner of the table, then you need the DROP ANY TABLE privilege in order to use the ALTER TABLE … DROP PARTITION or ALTER TABLE … TRUNCATE PARTITION.
10-Mar #oratidbit The ALTER [PUBLIC] DATABASE LINK statement only supports changing the fixed-user database links with the current password.
11-Mar #oratidbit The DEFAULT clause of CREATE TABLESPACE statement includes options to set COMPRESS, STORAGE and INMEMORY default values for objects created in the tablespace.
12-Mar #oratidbit The CON_NAME_TO_ID built-in function takes container name as its argument and returns the container ID.
13-Mar #oratidbit You cannot rollback a TRUNCATE TABLE statement, nor can you use a FLASHBACK TABLE statement to retrieve the contents of a table that has been truncated.
16-Mar #oratidbit #Oracle AWR Compare Period Report is generated by running $ORACLE_HOME/rdbms/admin/awrddrpt.sql on non-RAC database and by running $ORACLE_HOME/rdbms/admin/awrgdrpt.sql on RAC database.
20-Mar #oratidbit DBMS_SQL_MONITOR package in #DB12c provides information about Real-time SQL Monitoring and Real-time Database Operation Monitoring. These features provide automatic monitoring of SQL statements, PL/SQL blocks, or composite database operations that are considered expensive.
23-Mar #oratidbit #OEM12c 12.1.0.4 feature AWR Warehouse enables you to consolidate and store detailed performance data from the Automatic Workload Repository of your Oracle databases. This consolidated AWR Warehouse allows to analyze historical performance data beyond the AWR retention period of the source database.
24-Mar #oratidbit #OEM12c 12.1.0.4 feature AWR Warehouse supports source database versions from 10.2.0.4 up to the version of the database hosting the AWR repository.
25-Mar #roatidbit #OEM12c 12.1.0.4 feature AWR warehouse database must be #Oracle database 12.1.0.2 or higher or 11.2.0.4 with the appropriate patch level. This database must be a managed target in #OEM12c Cloud Control.