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
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