DML and DDL Transactions → The following are the DML and DDL operations supported in a distributed transaction: CREATE TABLE … DML and DDL statements in parallel, and INSERT direct load statements serially, but note the … cannot perform remote operations after issuing parallel DML/DDL or direct load INSERT. If the
8 Common SQL DDL Clauses → This chapter describes some SQL data definition clauses that appear in multiple SQL statements. This chapter contains these sections: allocate_extent_clause constraint deallocate_unused_clause file_specification logging_clause parallel_clause physical_attributes_clause size_clause storage_clause
Specifying the DDL Lock Timeout → Data Definition Language (DDL) statements require exclusive locks on internal structures. If these … locks are unavailable when a DDL statement runs, the DDL statement fails, though it might have … succeeded if it had been executed subseconds later. To enable DDL statements to wait for locks, specify a … DDL
Data Definition Language (DDL) Statements → Data definition language (DDL) statements let you to perform these tasks: Create, alter, and drop … transaction before and after every DDL statement. Many DDL statements may cause Oracle Database to … reauthorizes schema objects and the circumstances under which a DDL statement would cause this, see Oracle … Database
Manually Recompiling Invalid Objects with DDL → recompile package body Pkg1, you would execute the following DDL statement: ALTER PACKAGE pkg1 COMPILE
Parallel DDL → This section includes the following topics on parallelism for DDL statements: DDL Statements That … Can Be Parallelized CREATE TABLE... AS SELECT in Parallel Recoverability and Parallel DDL Space … Management for Parallel DDL DDL Statements That Can Be Parallelized You can parallelize DDL
DDL Locks → A data dictionary lock (DDL) protects the definition of a schema object while that object is acted … upon or referred to by an ongoing DDL operation. Recall that a DDL statement implicitly commits its … -statement transaction, Oracle Database automatically acquires DDL locks for all schema objects … referenced
Data Definition Language (DDL) → Up to three character set conversions may be required for data definition language (DDL) during an
DDL → Data definition language. Includes statements like CREATE/ALTER TABLE/INDEX, which define or change data structure.
DDL Statements → DDL statements manage schema objects in the database. These statements create new tables, drop old … FROM jamie DDL statements also allow you to work with objects in the Oracle database, as in Example 1
Embedded DDL → data definition language (DDL) statements. This restriction cannot be enforced when precompiling, so … the use of extended column types, such as NCHAR, within embedded DDL statements results in an
Tracking DDL Statements in the LogMiner Dictionary → language (DDL) statements. DDL tracking enables LogMiner to successfully track structural changes made … Example 5: Tracking DDL Statements in the Internal Dictionary for a complete example. With this … option set, LogMiner applies any DDL statements seen in the redo log files to its internal dictionary … . Note:
Reapplying DDL Statements Returned to V$LOGMNR_CONTENTS → Be aware that some DDL statements issued by a user cause Oracle to internally execute one or more … other DDL statements. If you want to reapply SQL DDL from the SQL_REDO or SQL_UNDO columns of the V … Dictionary in the Redo Log Files for an example. To differentiate between DDL statements that were issued by
Managing a DDL Handler → This section contains instructions for creating, specifying, and removing the DDL handler for an … apply process. Note: All applied DDL LCRs commit automatically. Therefore, if a DDL handler calls the … EXECUTE member procedure of a DDL LCR, then a commit is performed automatically. See Also: \"Apply … information
Executing DDL LCRs → The EXECUTE member procedure for DDL LCRs is a subprogram of the LCR$_DDL_RECORD type. When the … EXECUTE member procedure is run on a DDL LCR, the LCR is executed, and any apply process handlers that … would be run for the LCR are not run. The EXECUTE member procedure for DDL LCRs can be invoked only … in an apply handler for
DDL → refers to data definition language, which includes statements defining or changing data structure.
Considerations for Applying DDL Changes → The following sections discuss considerations for applying DDL changes to tables: System-Generated … Names CREATE TABLE AS SELECT Statements System-Generated Names If you plan to capture DDL changes at … a source database and apply these DDL changes at a destination database, then avoid using system … -generated names. If a DDL
Displaying Information About DML and DDL Handlers → This section contains queries that display information about apply process DML handlers and DDL … handlers. See Also: Oracle Streams Concepts and Administration for more information about DML and DDL … APPLY_DATABASE_LINK column is NULL. See Also: \"Managing a DML Handler\" Displaying the DDL Handler … for Each Apply Process To
Enterprise Manager name Record Skip DDL → Scope Database Enterprise Manager name Record Skip DDL
C.11.1 DDL Statements that Use DBLINKS → SQL Apply may not correctly apply DDL statements such as the following, that reference a database … executing such a DDL statement, you should use the DBMS_LOGSTDBY.INSTANTIATE_TABLE procedure for the
Enterprise Manager name Record Applied DDL → Scope Database Enterprise Manager name Record Applied DDL
Executing SQL DDL and DML Statements → ->terminateStatelessConnectionPool (scPool); Executing S QL DDL and DML Statements SQL is the industry-wide language
Follow the Best Practices for Replicating DDL Changes → When replicating data definition language (DDL) changes, do not allow system-generated names for … replicate DDL in your Oracle Streams environment, then any table structure changes must be performed … manually at each database in the environment. See Also: \"Considerations for Applying DDL Changes\"
10.5.3 Setting up a Skip Handler for a DDL Statement → You can create a procedure to intercept certain DDL statements and replace the original DDL … transparently handle DDL transactions with file specifications. The following procedure can handle … handle tablespace DDL transactions: CREATE OR REPLACE PROCEDURE SYS.HANDLE_TBS_DDL ( OLD_STMT IN
Types of DDL Changes Ignored by an Apply Process → The following types of DDL changes are not supported by an apply process. These types of DDL … DDL LCR that specifies an operation that cannot be applied, then the apply process ignores the DDL … LCR and records the following message in the apply process trace file, followed by the DDL text that
C.11 DDL Statements Supported by a Logical Standby Database → C.11 DDL Statements Supported by a Logical Standby Databas e Table C-1 lists the supported values … DBMS_LOGSTDBY package and Section 10.5.3, \"Setting up a Skip Handler for a DDL Statement\" Table C-1 Values … ALTER INDEX CREATE INDEX DROP INDEX NON_SCHEMA_DDL All DDL that does not pertain to a particular schema … SEGMENT SCHEMA_DDL
C.4 Support For Row-level Security and Fine-Grained Auditing → As of Oracle Database 11 g, Logical Standby can automatically replicate the security environment provided through the DBMS_RLS and DBMS_FGA PL/SQL packages. This support simplifies management of security considerations when a server fails over to the standby since the security environment will transparently be maintained. It also ensures that access control policies applied to the primary data can
C.4.2 Fine-Grained Auditing → Fine-grained auditing provides a way to audit select statements. The DBMS_FGA package enables all select statements that access a table to be captured, together with what data was accessed. An FGA policy may be applied to a particular column or even to only those select statements that return rows for which a specified predicate returns TRUE. When a DBMS_FGA procedure is executed on the primary, additional
C.8.3 Handling XML and XDB PL/SQL Packages in Logical Standby → In Oracle Database 11 g release 1 (11.1), Logical Standby supports XML when it is stored in CLOB format. However, there are several PL/SQL packages used in conjunction with XML that are not fully supported. The PL/SQL packages and procedures that are supported by Logical Standby only modify in-memory structures; they do not modify data stored in the database. These packages do not generate redo and
C.10 Skipped SQL Statements on a Logical Standby Database → C.10 S kipped SQL Statements on a Logical Standby Database By default, the following SQ L statements are automatically skipped by SQL Apply: ALTER DATABASE ALTER MATERIALIZED VIEW ALTER MATERIALIZED VIEW LOG ALTER SESSION ALTER SYSTEM CREATE CONTROL FILE CREATE DATABASE CREATE DATABASE LINK CREATE PFILE FROM SPFILE CREATE MATERIALIZED VIEW CREATE MATERIALIZED VIEW LOG CREATE SCHEMA AUTHORIZATION CREATE
C.1 Datatype Considerations → The following sections list the supported and unsupported database objects: Supported Datatypes in a Logical Standby Database Unsupported Datatypes in a Logical Standby Database
C.7 Unsupported Table Storage Types → Logical standby databases do not support the following table storage types: Tables stored with segment compression enabled Tables containing LOB columns stored as SecureFiles Tables with virtual columns
C.1.2 Unsupported Datatypes in a Logical Standby Database → C.1.2 Unsupported Datatypes in a Logical Standby Databas e Logical standby databases do not support the following datatypes: BFILE Collections (including VARRAYS and nested tables) Multimedia data types (including Spatial, Image, and Oracle Text ) ROWID, UROWID User-defined types LOBs stored as SecureFiles XMLType stored as Object Relational Binary XML
C.6 Supported Table Storage Types → Logical standby databases support the following table storage types: Cluster tables (including index clusters and heap clusters) Index-organized tables (partitioned and nonpartitioned, including overflow segments) Heap-organized tables (partitioned and nonpartitioned)
C.11.2 Replication of AUD$ and FGA_LOG$ on Logical Standbys → Auditing and fine-grained auditing are supported on logical standbys. Changes made to the AUD$ and FGA_AUD$ tables at the primary database are replicated at the logical standby. Both the AUD$ table and the FGA_AUD$ table have a DBID column. If the DBID value is that of the primary database, then the row was replicated to the logical standby based on activities at the primary. If the DBID value is
C Data Type and DDL Support on a Logical Standby Database → Database DDL Statements Supported by a Logical Standby Database Replication of AUD$ and FGA_LOG$ on Logical Standbys
C.8.1 Supported PL/SQL Supplied Packages → C.8.1 Supported PL/SQL Supplied Package s Oracle PL/SQL supplied packages that do not modify system metadata or user data leave no footprint in the archived redo log files, and hence are safe to use on the primary database. Examples of such packages are DBMS_OUTPUT, DBMS_RANDOM, DBMS_PIPE, DBMS_DESCRIBE, DBMS_OBFUSCATION_TOOLKIT, DBMS_TRACE, DBMS_METADATA, DBMS_CRYPTO. Oracle PL/SQL supplied packages
C.4.1 Row-level Security → supporting packages. You must ensure that these objects are placed in maintained schemas and that no DDL
C.8.2 Unsupported PL/SQL Supplied Packages → Oracle PL/SQL supplied packages that modify system metadata typically are not supported by SQL Apply, and therefore their effects are not visible on the logical standby database. Examples of such packages are DBMS_JAVA, DBMS_REGISTRY, DBMS_ALERT, DBMS_SPACE_ADMIN, DBMS_REFRESH, DBMS_REDEFINITION, and DBMS_AQ. Specific support for DBMS_JOB has been provided. Job execution is suspended on a logical
C.2 Support for Transparent Data Encryption (TDE) → Data Guard SQL Apply can be used to provide data protection for a primary database with Transparent Data Encryption (TDE) enabled. Consider the following when using a logical standby database to provide data protection for applications with advanced security requirements: Tables with Transparent Data Encryption using server held keys are supported in the context of a logical standby database when
C.4.3 Skipping and Enabling PL/SQL Replication → PL/SQL can be configured with skip and skip_error rules exactly as DDL statements except that … DBA take a compensating action. Unlike DDL, skip procedures on PL/SQL do not support returning a replacement statement.
C.3 Support for Tablespace Encryption → Data Guard SQL Apply can be used to provide data protection for a primary database that has tablespace encryption enabled. In such a case, restrictions 1, 2, and 3 listed in Section C.2, \"Support for Transparent Data Encryption (TDE)\" will apply. Note: In some cases, when SQL Apply mines and applies redo records for changes made to tables in encrypted tablespaces, records of user data in unencrypted
C.8 PL/SQL Supplied Packages Considerations → This section discusses the following considerations regarding PL/SQL supplied packages: Supported PL/SQL Supplied Packages Unsupported PL/SQL Supplied Packages Handling XML and XDB PL/SQL Packages in Logical Standby See Also: Oracle Database PL/SQL Packages and Types Reference for more information about Oracle PL/SQL supplied packages
C.9 Unsupported Tables → It is important to identify unsupported database objects on the primary database before you create a logical standby database because changes made to unsupported data types and tables on the primary database will be automatically skipped by SQL Apply on the logical standby database. Moreover, no error message will be returned. There are three types of objects on a database, from the perspective of
C.1.1 Supported Datatypes in a Logical Standby Database → C.1.1 Supported Datatypes in a Logical Standby Databas e Logical standby databases support the following datatypes: BINARY_DOUBLE BINARY_FLOAT BLOB CHAR CLOB and NCLOB DATE INTERVAL YEAR TO MONTH INTERVAL DAY TO SECOND LONG LONG RAW NCHAR NUMBER NVARCHAR2 RAW TIMESTAMP TIMESTAMP WITH LOCAL TIMEZONE TIMESTAMP WITH TIMEZONE VARCHAR2 and VARCHAR XMLType stored as CLOB Note: SQL Apply support for the
C.5 Oracle Label Security → Logical standby databases do not support Oracle Label Security. If Oracle Label Security is installed on the primary database, SQL Apply fails on the logical standby database with an internal error during startup.
Generating DDL → With Oracle Application Express, you can generate Data Definition Language (DDL) statements from … generate Data Definition Language (DDL) statements from the Oracle data dictionary. These scripts … specific schema, specific object types, or specific objects. To generate a DDL statement: On the … Workspace home page, click the
DDL Statements → SQL Server requires some DDL statements to be executed in their own transaction, and only one DDL … statement can be executed in a given transaction. If you use these DDL statements in a SQL Server … , or, if you execute the DDL statements through the gateway using the pass-through feature, an error … gateway
DDL handler → An apply handler that processes DDL LCRs.
Embedded DDL → language (DDL) statements. This restriction cannot be enforced when precompiling, so the use of … extended column types, such as NCHAR, within embedded DDL statements results in an execution error rather than a precompile error.
Disabling Parallel SQL Execution → You disable parallel SQL execution with an ALTER SESSION DISABLE PARALLEL DML|DDL|QUERY statement … . All subsequent DML ( INSERT, UPDATE, DELETE ), DDL ( CREATE, ALTER ), or query ( SELECT ) operations … or indexes involved. The following statement disables parallel DDL operations: ALTER SESSION DISABLE PARALLEL DDL;
Make DML and DDL Changes to Tables in the hr Schema → Database\", you can make DML and DDL changes to the tables in the hr schema at the dbs1.example.com … changes to the hr.jobs and hr.locations tables at dbs1.example.com. You can also make a DDL change to … hr.assignments table. You can also query the hr.locations table at dbs3.example.com to see that the DML and DDL … changes
Make DML and DDL Changes to Tables in the hrmult Schema → You can make DML and DDL changes to the tables in the hrmult schema at any of the databases in the … applied correctly. You can also make a DDL change to the hrmult.jobs table at mult3.example.com and
Enabling Parallel SQL Execution → You enable parallel SQL execution with an ALTER SESSION ENABLE PARALLEL DML|DDL|QUERY statement … . Subsequently, when a PARALLEL clause or parallel hint is associated with a statement, those DML, DDL … , or query statements will execute in parallel. By default, parallel execution is enabled for DDL and
DDL logical change record (DDL LCR) → See Also: DDL logical change record (DDL LCR) DDL logical change record (DDL LCR) A logical change … record (LCR) that describes a data definition language (DDL) change.
DDL Statement Processing → The following topics discuss how DDL, Transaction Control, and other SQL statements can differ from … the process just described in Description of SQL Statement Processing: DDL Statement Processing … Transaction Control Processing Other Processing Types DDL Statement Processing The execution of DDL … statements
Committed DDL Statements → For both OCN and QRCN, the following DDL statements, when committed, generate notifications: ALTER … following: Query IDs of the queries whose results have changed Name of the modified table Type of DDL … operation Some DDL operations that invalidate registered queries can cause those queries to be … , COL3 NUMBER) The following
4.49 Generate Oracle DDL → DDL It specifies the converted model for which to generate Oracle DDL (data definition language … operation is in progress, a box displays object types and the number of objects of each type for which DDL … two files have been created: a.sql file containing DDL statements (such as CREATE TABLE and CREATE … containing
DDL for LOBs → LOB definition can involve the CREATE TYPE and the CREATE TABLE statements. Example 6-1 specifies a CLOB within a datatype named lob_type. Example 6-1 How to Create a CLOB Attribute of a Type CREATE OR REPLACE TYPE lob_type AS OBJECT ( id INTEGER, data CLOB ); Example 6-2 creates an object table, lob_table, in which each row is an instance of lob_type data: Example 6-2 How to Create a LOB Object Table
Forcing Parallel SQL Execution → You can force parallel execution of all subsequent DML, DDL, or query statements for which … parallelization is possible with the ALTER SESSION FORCE PARALLEL DML|DDL|QUERY statement. Additionally you … of subsequent statements and sets the overriding degree of parallelism to 5: ALTER SESSION FORCE PARALLEL DDL PARALLEL 5;