Oracle 1z0-060 Upgrade to Oracle Database 12c Exam Practice Test

The triggers cloned to the interim table are disabled until the redefinition is completed. Once the redefinition is complete, all cloned objects are renamed to the original names used by they object they were cloned from.

References: http://www.oracle-base.com/articles/10g/online-table-redefinition-enhancements-10gr1.php

* SET_TABLE_PREFS Procedure

This procedure is used to set the statistics preferences of the specified table in the specified schema.

* Example:

Using Pending Statistics

Assume many modifications have been made to the employees table since the last time statistics were gathered. To ensure that the cost-based optimizer is still picking the best plan, statistics should be gathered once again; however, the user is concerned that new statistics will cause the optimizer to choose bad plans when the current ones are acceptable. The user can do the following:

EXEC DBMS_STATS.SET_TABLE_PREFS('hr', 'employees', 'PUBLISH', 'false');

By setting the employees tables publish preference to FALSE, any statistics gather from now on will not be automatically published. The newly gathered statistics will be marked as pending.

* The CREATE DATABASE ... ENABLE PLUGGABLE DATABASE SQL statement creates a new CDB. If you do not specify the ENABLE PLUGGABLE DATABASE clause, then the newly created database is a non-CDB and can never contain PDBs.

Along with the root (CDB$ROOT), Oracle Database automatically creates a seed PDB (PDB$SEED). The following graphic shows a newly created CDB:

* Creating a PDB

Rather than constructing the data dictionary tables that define an empty PDB from scratch,

and then populating its Obj$ and Dependency$ tables, the empty PDB is created when the CDB

is created. (Here, we use empty to mean containing no customer-created artifacts.) It is referred

to as the seed PDB and has the name PDB$Seed. Every CDB non-negotiably contains a

seed PDB; it is non-negotiably always open in read-only mode. This has no conceptual

significance; rather, it is just an optimization device. The create PDB operation is implemented

as a special case of the clone PDB operation.

Multipartition maintenance enables adding, dropping, truncate, merge, split operations on multiple partitions.

A: Merge Multiple Partitions:

The new “ALTER TABLE … MERGE PARTITIONS ” help merge multiple partitions or subpartitions with a single statement. When merging multiple partitions, local and global index operations and semantics for inheritance of unspecified physical attributes are the same for merging two partitions.

B: Drop Multiple Partitions:

The new “ALTER TABLE … DROP PARTITIONS ” help drop multiple partitions or subpartitions with a single statement.

Example:

view plaincopy to clipboardprint?

SQL> ALTER TABLE Tab_tst1 DROP PARTITIONS

Tab_tst1_PART5, Tab_tst1_PART6, Tab_tst1_PART7;

Table altered

SQL>

Restrictions:

* Oracle Database provides a mechanism to make table structure modifications without significantly affecting the availability of the table. The mechanism is called online table redefinition. Redefining tables online provides a substantial increase in availability compared to traditional methods of redefining tables.

* To redefine a table online:

Choose the redefinition method: by key or by rowid

* By key—Select a primary key or pseudo-primary key to use for the redefinition. Pseudo-primary keys are unique keys with all component columns having NOT NULL constraints. For this method, the versions of the tables before and after redefinition should have the same primary key columns. This is the preferred and default method of redefinition.

* By rowid—Use this method if no key is available. In this method, a hidden column named M_ROW$$ is added to the post-redefined version of the table. It is recommended that this column be dropped or marked as unused after the redefinition is complete. If COMPATIBLE is set to 10.2.0 or higher, the final phase of redefinition automatically sets this column unused. You can then use the ALTER TABLE ... DROP UNUSED COLUMNS statement to drop it.

You cannot use this method on index-organized tables.

Note:

* When you rebuild an index, you use an existing index as the data source. Creating an index in this manner enables you to change storage characteristics or move to a new tablespace. Rebuilding an index based on an existing data source removes intra-block fragmentation. Compared to dropping the index and using the CREATE INDEX statement, re-creating an existing index offers better performance.

With adaptive SQL plan management, DBAs no longer have to manually run the verification or evolve process for non-accepted plans. When automatic SQL tuning is in COMPREHENSIVE mode, it runs a verification or evolve process for all SQL statements that have non-accepted plans during the nightly maintenance window. If the non-accepted plan performs better than the existing accepted plan (or plans) in the SQL plan baseline, then the plan is automatically accepted and becomes usable by the optimizer. After the verification is complete, a persistent report is generated detailing how the non-accepted plan performs compared to the accepted plan performance. Because the evolve process is now an AUTOTASK, DBAs can also schedule their own evolve job at end time.

Note:

* The optimizer is able to adapt plans on the fly by predetermining multiple subplans for portions of the

plan.

* Adaptive plans, introduced in Oracle Database 12c, enable the optimizer to defer the final plan decision for a statement until execution time. The optimizer instruments its chosen plan (the default plan) with statistics collectors so that it can detect at runtime, if its cardinality estimates differ greatly from the actual number of rows seen by the operations in the plan. If there is a significant difference, then the plan or a portion of it will be automatically adapted to avoid suboptimal performance on the first execution of a SQL statement.

Step 1 (2). Seed column usage

Oracle must observe a representative workload, in order to determine the appropriate column groups. Using the new procedure DBMS_STATS.SEED_COL_USAGE, you tell Oracle how long it should observe the workload.

Step 2: (3) You don't need to execute all of the queries in your work during this window. You can simply run explain plan for some of your longer running queries to ensure column group information is recorded for these queries.

Step 3. (1) Create the column groups

At this point you can get Oracle to automatically create the column groups for each of the tables based on the usage information captured during the monitoring window. You simply have to call the DBMS_STATS.CREATE_EXTENDED_STATS function for each table. This function requires just two arguments, the schema name and the table name. From then on, statistics will be maintained for each column group whenever statistics are gathered on the table.

Note:

* DBMS_STATS.REPORT_COL_USAGE reports column usage information and records all the SQL operations the database has processed for a given object.

* The Oracle SQL optimizer has always been ignorant of the implied relationships between data columns within the same table. While the optimizer has traditionally analyzed the distribution of values within a column, he does not collect value-based relationships between columns.

* Creating extended statisticsHere are the steps to create extended statistics for related table columns withdbms_stats.created_extended_stats:

1 - The first step is to create column histograms for the related columns.2 – Next, we run dbms_stats.create_extended_stats to relate the columns together.

Unlike a traditional procedure that is invoked via an execute (“exec”) statement, Oracle extended statistics are created via a select statement.

D: DB_FILE_MULTIBLOCK_READ_COUNT is one of the parameters you can use to minimize I/O during table scans. It specifies the maximum number of blocks read in one I/O operation during a sequential scan. The total number of I/Os needed to perform a full table scan depends on such factors as the size of the table, the multiblock read count, and whether parallel execution is being utilized for the operation.

1. Set user tablespaces in the source database to READ ONLY.

2. From the Oracle Database 11g Release 2 {11.2.0.3) environment, export the metadata and any data residing in administrative tablespaces from the source database using the FULL=Y and TRANSPORTABLE=ALWAYS parameters. Note that the VER$ION=12 parameter is required only when exporting from an Oracle Database llg Release 2 database:

3. Copy the tablespace data files from the source system to the destination system. Note that the log file from the export operation will list the data files required to be moved.

4. Create a COB on the destination system, including a PDB into which you will import the source database.

5. In the Oracle Database 12c environment, connect to the pre-created PDB and import the dump file. The act of importing the dump file will plug the tablespace data files into the destination PDB

Oracle White Paper - Upgrading to Oracle Database 12c -August 2013

A, Not D: In a CDB, PUBLIC is a common role. In a PDB, privileges granted locally to PUBLIC enable all local and common users to exercise these privileges in this PDB only.

C: A user can only perform common operations on a common role, for example, granting privileges commonly to the role, when the following criteria are met:

The user is a common user whose current container is root.

The user has the SET CONTAINER privilege granted commonly, which means that the privilege applies in all containers.

The user has privilege controlling the ability to perform the specified operation, and this privilege has been granted commonly

Note:

* Every privilege and role granted to Oracle-supplied users and roles is granted commonly except for system privileges granted to PUBLIC, which are granted locally.

The log files are created only in $ADR_HOME/log

in alert log only alter system is catched.Sun Mar 30 23:30:04 2014ALTER SYSTEM SET enable_ddl_logging=TRUE SCOPE=BOTH;Sun Mar 30 23:32:41 2014ALTER SYSTEM SET enable_ddl_logging=TRUE SCOPE=BOTH;Sun Mar 30 23:39:35 2014ALTER SYSTEM SET enable_ddl_logging=FALSE SCOPE=BOTH;[oracle@ovs trace]$

only in diag/rdbms/…/../log/ two files are capturing this. The xml file is capturing full details and text file having only the commands.

[oracle@ovs log]$ pwd/oraclebase/diag/rdbms/ora12c1/ora12c1/log[oracle@ovs log]$ cat ddl/*

create table testing2(a number)

create table testing3( aa number)

[oracle@ovs log]$ cat ddl_ora12c1.logSun Mar 30 23:33:26 2014diag_adl:create table testing2(a number)diag_adl:create table testing3( aa number)

References:

* With a control file autobackup, RMAN can recover the database even if the current control file, recovery catalog, and server parameter file are inaccessible.

* RMAN uses a recovery catalog to track filenames for all database files in a Data Guard environment. A recovery catalog is a database schema used by RMAN to store metadata about one or more Oracle databases. The catalog also records where the online redo logs, standby redo logs, tempfiles, archived redo logs, backup sets, and image copies are created.

* A PDB would have its SYSTEM, SYSAUX, TEMP tablespaces. It can also contains other user created tablespaces in it.

*

* Oracle Database creates both the SYSTEM and SYSAUX tablespaces as part of every database.

* tablespace_datafile_clauses

Use these clauses to specify attributes for all data files comprising the SYSTEM and SYSAUX tablespaces in the seed PDB.

Transportable tablespaces were introduced in Oracle 8i to allow whole tablespaces to be copied between databases in the time it takes to copy the datafiles. It is supported in Oracle 11g.

Note:

Providing a UNIX Group Name

If you are installing a product on a UNIX system, the Installer will also prompt you to provide the name of the group which should own the base directory.

You must choose a UNIX group name which will have permissions to update, install, and deinstall Oracle software. Members of this group must have write permissions to the base directory chosen.

Only users who belong to this group are able to install or deinstall software on this machine.

Execute the DBMS_XDB_CONFIG.SETHTTPSPORT procedure.

References:

Both users SYS and SYSTEM automatically have the EXEMPT REDACTION POLICY system privilege. (SYSTEM has the EXP_FULL_DATABASE role, which includes the EXEMPT REDACTION POLICY system privilege.) This means that the SYS and SYSTEM users can always bypass any existing Oracle Data Redaction policies, and will always be able to view data from tables (or views) that have Data Redaction policies defined on them

* orapwd

/ You can create a database password file using the password file creation utility, ORAPWD.

The syntax of the ORAPWD command is as follows:

orapwd FILE=filename [ENTRIES=numusers] [FORCE={y|n}] [ASM={y|n}]

[DBUNIQUENAME=dbname] [FORMAT={12|legacy}] [SYSBACKUP={y|n}] [SYSDG={y|n}]

[SYSKM={y|n}] [DELETE={y|n}] [INPUT_FILE=input-fname]

force - whether to overwrite existing file (optional),

* v$PWFILE_users

/ 12c: V$PWFILE_USERS lists all users in the password file, and indicates whether the user has been granted the SYSDBA, SYSOPER, SYSASM, SYSBACKUP, SYSDG, and SYSKM privileges.

/ 10c: sts users who have been granted SYSDBA and SYSOPER privileges as derived from the password file.

ColumnDatatypeDescription

USERNAMEVARCHAR2(30)The name of the user that is contained in the password file

SYSDBAVARCHAR2(5)If TRUE, the user can connect with SYSDBA privileges

SYSOPERVARCHAR2(5)If TRUE, the user can connect with SYSOPER privileges

A: Variable size extents enable support for larger ASM datafiles, reduce SGA memory requirements for very large databases (A), and improve performance for file create and open operations.

C: You don't have to worry about the sizes; the ASM instance automatically allocates the appropriate extent size.

Note:

* The contents of ASM files are stored in a disk group as a set, or collection, of data extents that are stored on individual disks within disk groups. Each extent resides on an individual disk. Extents consist of one or more allocation units (AU). To accommodate increasingly larger files, ASM uses variable size extents.

* The size of the extent map that defines a file can be smaller by a factor of 8 and 64 depending on the file size. The initial extent size is equal to the allocation unit size and it increases by a factor of 8 and 64 at predefined thresholds. This feature is automatic for newly created and resized datafiles when the disk group compatibility attributes are set to Oracle Release 11 or higher.

* Multiprocess and Multithreaded Oracle Database Systems

Multiprocess Oracle Database (also called multiuser Oracle Database) uses several processes to run different parts of the Oracle Database code and additional Oracle processes for the users—either one process for each connected user or one or more processes shared by multiple users. Most databases are multiuser because a primary advantage of a database is managing data needed by multiple users simultaneously.

Each process in a database instance performs a specific job. By dividing the work of the database and applications into several processes, multiple users and applications can connect to an instance simultaneously while the system gives good performance.

* In previous releases, Oracle processes did not run as threads on UNIX and Linux systems. Starting in Oracle Database 12c, the multithreaded Oracle Database model enables Oracle processes to execute as operating system threads in separate address spaces.