Selecting from the DUAL Table → , DUMMY, defined to be VARCHAR2(1), and contains one row with a value X. Selecting from the DUAL table is … expression from any table, but the value will be returned as many times as there are rows in the table … . Refer to \"About SQL Functions\" for many examples of selecting a constant value from DUAL. Note … DUAL in the execution
Dropping Disks from Disk Groups → When you drop a disk from a disk group, Oracle ASM rebalances the disk group by moving all of the … file extents from the dropped disk to other disks in the disk group. Oracle ASM then releases the … reallocate the disk to a different storage system Note: Dropping disks from a disk group only logically … deletes the disks from
Dropping Disks from Disk Groups → … ALTER DISKGROUP data2 ADD DISK '/devices/diskc3' FORCE; Dropping Disks from Disk Groups To drop disks … from a disk group, use the DROP DISK clause of the ALTER DISKGROUP statement. You can also drop all … dropped, the disk group is rebalanced by moving all of the file extents from the dropped disk to other … . You cannot
Dropping Templates from a Disk Group → Use the DROP TEMPLATE clause of the ALTER DISKGROUP statement to drop one or more templates from a … templates. Example 7-16 drops the previously created template unreliable from data: Example 7-16 Dropping … a template from a disk group ALTER DISKGROUP data DROP TEMPLATE unreliable;
Invoking Autonomous Functions from SQL → A function invoked from SQL statements must obey rules meant to control side effects (for details … function from query DECLARE my_emp_id NUMBER(6); my_last_name VARCHAR2(25); my_count NUMBER; BEGIN … my_emp_id:= 120; SELECT debugging.log_msg(last_name) INTO my_last_name FROM employees WHERE employee_id
Locks from In-Doubt Transactions → portions of the two-phase commit. Even if such a failure occurs, and assuming quick recovery from a
Migrating a Database from Oracle ASM to Alternative Storage → Migrating a database from Oracle ASM to an alternative storage system is essentially the reverse of
Dropping Files and Associated Aliases from a Disk Group → You can delete Oracle ASM files and their associated aliases from a disk group using the DROP FILE … file is used to delete both the file and the alias from a disk group. Example 7-7 Dropping a file and … alias from a disk group using the alias name ALTER DISKGROUP data DROP FILE '+data/payroll … file and any associated alias.
Oracle ASM Recovery from Read and Write I/O Errors → your main concern is disk failure. For example, a disk group might be configured from several small … Oracle database environment. Oracle ASM Recovery from Read and Write I/O Errors Read errors can be … . Oracle ASM tries to recover from read errors on corrupted sectors on a disk. When a read error by … offline disk from
Removing Components from the Oracle Restart Configuration → automatically removed from the Oracle Restart configuration. For example, if you use Database … Configuration Assistant (DBCA) to delete a database, DBCA removes the database from the Oracle Restart … removes the listener from the Oracle Restart configuration. See Table 4-3 for more examples. If you … remove the component from
Restoring Tables from the Recycle Bin → Use the FLASHBACK TABLE... TO BEFORE DROP statement to recover objects from the recycle bin. You … from either the DBA_ or USER_RECYCLEBIN view as shown in \"Viewing and Querying Objects in the Recycle … . SELECT object_name, original_name, createtime FROM recyclebin; OBJECT_NAME ORIGINAL_NAME CREATETIME … $yrMKlZaVMhfgNAgAIMenRA==$0\"
Purging Pending Rows from the Data Dictionary → Before RECO recovers an in-doubt transaction, the transaction appears in DBA_2PC_PENDING.STATE as COLLECTING, COMMITTED, or PREPARED. If you force an in-doubt transaction using COMMIT FORCE or ROLLBACK FORCE, then the states FORCED COMMIT or FORCED ROLLBACK may appear. Automatic recovery normally deletes entries in these states. The only exception is when recovery discovers a forced transaction that
Moving from DBMS_JOB to Oracle Scheduler → This section illustrates some examples of how you can take jobs created with the DBMS_JOB package and rewrite them using Oracle Scheduler, which you configure and control with the DBMS_SCHEDULER package.
Removing a Job from the Job Queue → The following example removes a job using DBMS_JOB, where 14144 is the number of the job being run: BEGIN DBMS_JOB.REMOVE(14144); COMMIT; END; / Using DBMS_SCHEDULER, you would issue the following statement instead: BEGIN DBMS_SCHEDULER.DROP_JOB('myjob1'); END; / See Also: Oracle Database PL/SQL Packages and Types Reference for more information about the DBMS_SCHEDULER package Chapter 29, \"Scheduling
Fetching from Results of Pipelined Table Functions → passes CURSOR expressions to both f and g. Example 12-31 Fetching from Results of Pipelined Table … * FROM TABLE(f(CURSOR(SELECT * FROM tab))); SELECT * BULK COLLECT INTO rec_tab FROM TABLE(g(r … )); -- NOTE: When g completes, it closes r. END; -- Second PL/SQL statement (faster): SELECT * FROM TABLE(g
Recovering from Permanent Media Failure Using a Control File Copy → CONTROL_FILES initialization parameter. Remove the bad control file from CONTROL_FILES setting and you can
Store Data Files Separate from Redo Log Files → Data files should not be stored on the same disk drive that stores the database redo log files. If the data files and redo log files are stored on the same disk drive and that disk drive fails, the files cannot be used in your database recovery procedures. If you multiplex your redo log files, then the likelihood of losing all of your redo log files is low, so you can store data files on the same
Recovering from Control File Corruption Using a Control File Copy → This procedure assumes that one of the control files specified in the CONTROL_FILES parameter is corrupted, that the control file directory is still accessible, and that you have a multiplexed copy of the control file. With the instance shut down, use an operating system command to overwrite the bad control file with a good copy: % cp /u03/oracle/prod/control03.ctl /u02/oracle/prod/control02.ctl Start
5.4.1 Migrating User Settings from Release 1.0 → asked to migrate your user settings from a previous release. (This occurs regardless of whether … there was a previous release on your system.) Note: Migration of user settings is supported only from … SQL Developer release 1.0 to release 1.1. It is not supported for migration from a prerelease version … migrate your user settings
5.4.2 Migrating Information from Previous Releases → Navigator and selecting Import Connections. To use any user-defined reports or the SQL history from a … located. To use any user-defined reports or the SQL history from release 1.0 with both releases 1.0 … and then Preferences ) from a prerelease version of the current release cannot currently be saved and reused; you must provide any
Removing Records from the RMAN Repository → This section explains how to remove records for files from the RMAN repository. About Uncataloging … backup record from the recovery catalog (if you use one) RMAN does not change the specified physical … with an operating system utility, then remove the record for this log from the repository by issuing a … you deleted from
Making Database Connections from the RMAN Prompt → database connection from the RMAN prompt: On the operating system command line, start the RMAN
Scenario 5: Migrating from a Dictionary-Managed to a Locally Managed Tablespace → Use the TABLESPACE_MIGRATE_TO_LOCAL procedure to migrate a dictionary-managed tablespace to a locally managed tablespace. This operation is done online, but space management operations are blocked until the migration has been completed. Therefore, you can read or modify data while the migration is in progress, but if you are loading a large amount of data that requires the allocation of additional
20.2.1 Moving from RBO to the Query Optimizer → the application when upgrading from rule-based to query optimization. By creating outlines for an … which outlines are to be created have the CREATE ANY OUTLINE privilege. For example, from SYS: GRANT
Unregistering a Target Database from the Recovery Catalog → You can use the UNREGISTER DATABASE command to unregister a database from the recovery catalog … . When a database is unregistered from the recovery catalog, all RMAN repository records in the recovery … existing backups. Do not delete all backups if your intention is only to remove the database from … the process is complete,
Restoring the Server Parameter File from a Control File Autobackup → the control file whenever an autobackup is taken. To restore the server parameter file from the … FROM AUTOBACKUP command. If the autobackup is in a nondefault format, then first use the SET … server parameter file from a control file autobackup in a nondefault location. Example 20-1 Restoring … the Server Parameter File from
Step 1: Remove the RATE Parameter from Channel Settings → in a RUN command, then remove the RATE parameter, if it is specified, from the ALLOCATE command … configured settings. Remove the RATE parameter, if it is set, from the CONFIGURE CHANNEL command.
Recovering from a Dropped Table Without Using Flashback Features → One common error is the accidental dropping of a table from your database. In general, the fastest … before the table was dropped. Export the lost data from the temporary, restored version of the
Making RMAN Database Connections from the Operating System Command Line → To connect to a target database from the operating system command line, enter the rman command
Revoking Privileges from a Virtual Private Catalog Owner → to register databases in his virtual private catalog. Now you want to revoke privileges from vpc1. To … revoke privileges from a virtual private catalog owner: Start RMAN and connect to the recovery … ; Revoke specified privileges from the virtual private catalog owner. The following command revokes … access to the metadata for
Configuring Tablespaces for Exclusion from Whole Database Backups → Sometimes you may want to omit a specified tablespace from part of the regular backup schedule … different schedule from other backups. You can run CONFIGURE EXCLUDE FOR TABLESPACE to exclude the … specified tablespace from the BACKUP DATABASE command. The exclusion condition applies to any data … cwmlite and example from
SQL*Net message from dblink → link from another server process. Wait Time: The time it took for a message to arrive from another … process) from another foreground process over a database link. See Also: Oracle Database Performance Tuning Guide for more information about this wait event
Loading LOB Data from LOBFILEs → LOB data can be lengthy enough so that it makes sense to load it from a LOBFILE instead of from a … a LOB from a LOBFILE fit in memory. SQL*Loader reads LOBFILEs in 64 KB chunks. In LOBFILEs the data … , TERMINATED BY or ENCLOSED BY ) The clause PRESERVE BLANKS is not applicable to fields read from a LOBFILE
SQL*Net message from client → The server process (foreground process) waits for a message from the client process to arrive. Wait … Time: The time it took for a message to arrive from the client since the last message was sent to … the server (foreground process) from the client. See Also: Oracle Database Performance Tuning Guide for more information about this wait
Data Retrieval Mechanisms → Data retrieval from the SAP system can be \"Completely Managed by Oracle Warehouse Builder … RFC_ABAP_INSTALL_AND_RUN, and to use FTP to import the generated data file from the SAP system. Thus the entire … process of retrieving data from the SAP system and creating a target table is managed by the Oracle
Defining the SAP Extraction Mapping → You can use the Mapping Editor to create a mapping containing SAP tables. Creating a mapping with SAP tables is similar to creating mappings with other database objects. However, there are restrictions on the operators that can be used in the mapping. You can only use table, filter, joiner, and mapping input parameter mapping operators in a mapping containing SAP tables. A typical SAP extraction mapping
3.3.1 Install the Oracle Database and Complete Pre-installation Tasks → Oracle Application Express requires an Oracle database that is release 184.108.40.206 or later. To learn more, see the Oracle Database Installation Guide for your operating environment and \"Recommended Pre-installation Tasks\".
3.4.5 Configure Oracle HTTP Server Distributed with Oracle Database 11g or Oracle Application Server… → installation. Tip: If you are upgrading from a prior release of Oracle Application Express, this step … configuring the database access descriptor (DAD) in a new installation. Tip: If you are upgrading from a … Copy the Images Directory Whether you are loading a new installation or upgrading from a previous … release, you must
3.4.10 About Managing JOB_QUEUE_PROCESSES → Application Express From SQL*Plus 220.127.116.11.1 Viewing JOB_QUEUE_PROCESSES in the Installation Log File … bottom of the page. 18.104.22.168.3 Viewing JOB_QUEUE_PROCESSES from SQL*Plus You can also view the number … of JOB_QUEUE_PROCESSES from SQL*Plus by running the following SQL statement: SELECT VALUE FROM v
3.2 Choosing an HTTP Server → In order to run, Oracle Application Express must have access to either the embedded PL/SQL gateway or Oracle HTTP Server and mod_plsql. To learn more, see \"About Choosing an HTTP Server\".