What does the SELECT ... FOR UPDATE statement do?

From the documentation:

The FOR UPDATE clause lets you lock the selected rows so that other users cannot lock or update the rows until you end your transaction. You can specify this clause only in a top-level SELECT statement, not in subqueries.

Some examples here

Used in PL/SQL:

The SELECT statement with the FOR UPDATE clause (SELECT FOR UPDATE statement) selects the rows of the result set and locks them. SELECT FOR UPDATE lets you base an update on the existing values in the rows, because it ensures that no other user can change those values before you update them.

What are the two options XDK and XML in DBA_REGISTRY anyway?

What are the two options XDK and XML in DBA_REGISTRY anyway?

You see them amongst other components in the DBA_REGISTRY view:

select comp_id,comp_name, version,status from dba_registry;

COMP_ID       COMP_NAME          VERSION        STATUS
-------------------- ---------------------------------------- ------------------------------ --------------------
XDB       Oracle XML Database        12.1.0.2.0       VALID
XML       Oracle XDK          12.1.0.2.0       VALID

So what are they, and how are they connected?

The XDK documentation states:

"Oracle XML Developer's Kit (XDK) is a versatile set of components that enables you to build and deploy C, C++, and Java software programs that process Extensible Markup Language (XML)."

In other words, XDK is a toolbox for developers of XML-driven applications.

The XML DB documentation states:
"Oracle XML DB is a set of Oracle Database technologies related to high-performance handling of XML data...Oracle XML DB and the XMLType abstract data type make Oracle Database XML-aware. Storing XML data as an XMLType column or table lets the database perform XML-specific operations on the content."

In other words, XML DB is the framework for allowing XML to be stored and retrieved in the database. It has been a part of the Oracle RDBMS since version 9.2.

The XDK

"supports Oracle XML DB, which is a set of technologies used for storing and processing XML in Oracle Database."

With XDK and XML DB you can

"build applications that run in Oracle Database. You can also use XDK independently of Oracle XML DB.
XDK is fully supported by Oracle and comes with a commercial redistribution license. The standard installation of Oracle Database includes XDK."

Cardinality, Selectivity, Projection and Selection - definitions

From Oracle documentation:

Cardinality

The ratio of distinct values to the number of table rows. A column with only two distinct values in a million-row table would have low cardinality

Selectivity

In a query, the measure of how many rows from a row set pass a predicate test, for example, WHERE last_name = 'Smith'. A selectivity of 0.0 means no rows, whereas a value of 1.0 means all rows. A predicate becomes more selective as the value approaches 0.0 and less selective (or more unselective) as the value approaches 1.0.

Projection

The ability of a SELECT statement to choose a *subset* of the columns in a given table.
In other words, this corresponds to the column listing in the SELECT statement.

Selection

The ability of a SELECT statement to choose a subset of rows in a given table.
In other words, selection corresponds to the WHERE clause of the statement.

What is the difference between direct-path INSERT and conventional INSERT?

• During conventional INSERT operations, the database reuses free space in the table, interleaving newly inserted data with existing data. During such operations, the database also maintains referential integrity constraints.

• During direct-path INSERT operations, the database appends the inserted data after existing data in the table. Data is written directly into datafiles, bypassing the buffer cache. Free space in the existing data is not reused, and referential integrity constraints are ignored. These procedures combined can enhance performance.


Source: Oracle Documentation

What is standard (or traditional) auditing?

From the Oracle Documentation:

"In standard auditing, you enable auditing of SQL statements, privileges, schema objects, and network or multitier activities. You can audit a specific schema table if you want."

You need to set AUDIT_TRAILto "DB" or "DB,EXTENDED", in order to start using standard auditing.
After this is done, you may execute the AUDIT statement to start auditing of different actions according to your need.
You can choose to write the audit-trail to the database itself, or to operating system files.

Oracle Database audits all data manipulation language (DML) operations, such as INSERT, UPDATE, MERGE, and DELETE on the SYS.AUD$ and SYS.FGA_LOG$ tables by non-SYS users. (It performs this audit even if you have not set audit options for the AUD$ and FGA_LOGS$ tables.)

Typically, non-SYS users do not have access to these tables, except if they have been explicitly granted access. If a non-SYS user tampers with the data in the SYS.FGA_LOG$ and SYS.AUD$ tables, then Oracle Database writes an audit record for each action.

To view the standard audit trail, query the views DBA_AUDIT_TRAIL and DBA_COMMON_AUDIT_TRAIL.

Source: Oracle 11g Documentation


Note that in later releases, Oracle is often refering to standard auditing as "Traditional Auditing", since Oracle 12c introduced the concept of Unfied Auditing.

What is mandatory auditing?

Oracle Database always audits certain database-related operations and writes them to the operating system audit files.

It includes the actions of any user who is logged in with the SYSDBA or SYSOPER privilege. This is called mandatory auditing.

Even if you have enabled the database audit trail (that is, setting the AUDIT_TRAIL parameter to DB), Oracle Database still writes mandatory records to operating system files.

Mandatory auditing includes the following operations:

* Database startup
* SYSDBA and SYSOPER logins
* Database shutdown

Source: Oracle Documentation

What is Row Movement and how is it used?

When you add the clause "enable row movement" to a create table statement, you are giving Oracle permission to change the ROWIDs.

Features such as flashback table depends on row movement being enabled on the table.

To enable row movement on an existing table:

ALTER TABLE SCOTT.EMP ENABLE ROW MOVEMEMENT;

Using enable row movement can corrupt any Oracle features that rely on ROWID, such as nested tables.
Check for such objects before you enable row movement:

SELECT COUNT(*)
FROM dba_nested_tables
WHERE owner = 'SCOTT';


Sources: Oracle Documentation and Don Burleson