LOCK TABLE → Purpose Use the LOCK TABLE statement to lock one or more tables, table partitions, or table … access to a table or view by other users for the duration of your operation. Some forms of locks can be … placed on the same table at the same time. Other locks allow only one lock for a table.
TRUNCATE TABLE → Purpose Caution: You cannot roll back a TRUNCATE TABLE statement, nor can you use a FLASHBACK TABLE … statement to retrieve the contents of a table that has been truncated. Use the TRUNCATE TABLE … statement to remove all rows from a table. By default, Oracle Database also performs the following tasks
CREATE TABLE → Purpose Us e the CREATE TABLE statement to create one of the following types of tables: A … relational table, which is the basic structure to hold user data. An object table, which is a table that uses … an object type for a column definition. An object table is explicitly defined to hold object
DROP TABLE → Purpose Use the DROP TABLE statement to move a table or object table to the recycle bin or to … remove the table and all its data from the database entirely. Caution: Unless you specify the PURGE … clause, the DROP TABLE statement does not result in space being released back to the tablespace for use
ALTER TABLE → Purpose Use the ALTER TABLE statement to alter the definition of a nonpartitioned table, a … partitioned table, a table partition, or a table subpartition. For object tables or relational tables with … object columns, use ALTER TABLE to convert the table to the latest
FLASHBACK TABLE → Purpose Use the FLASHBACK TABLE statement to restore an earlier state of a table in the event of … human or application error. The time in the past to which the table can be flashed back is dependent … on the amount of undo data in the system. Also, Oracle Database cannot restore a table to an earlier … state
Selecting from the DUAL Table → DUAL is a table automatically created by Oracle Database along with the data dictionary. DUAL is in … , 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
Researching and Reversing Erroneous Table Changes → the option of using Oracle Flashback Table, described in \"Recovering Tables Using Oracle Flashback … Table\". Note: You must be using automatic undo management to use Oracle Flashback features. See
Recovering Tables Using Oracle Flashback Table → Recovering Tables Using Oracle Flash back Table Oracle Flashback Table enables you to restore a … table to its state as of a previous point in time. It provides a fast, online solution for recovering a … table that has been accidentally modified or deleted by a user or application. In many cases … , Oracle Flashback
12 SQL Statements: ALTER TABLE to ALTER TABLESPACE → This chapter contains the following SQL statements: ALTER TABLE ALTER TABLESPACE
Table 7-6 AND Truth Table → : SELECT * FROM employees WHERE hire_date 2500 ORDER BY employee_id; Table 7-7 shows the results of applying OR to two expressions.
Table 7-5 NOT Truth Table → -- TRUE FALSE UNKNOWN NOT FALSE TRUE UNKNOWN Table 7-6 shows the results of combining the AND condition to two expressions.
Table 7-7 OR Truth Table → OR TRUE FALSE UNKNOWN TRUE TRUE TRUE TRUE FALSE TRUE FALSE UNKNOWN UNKNOWN TRUE UNKNOWN UNKNOWN For example, the following query returns employees who have a 40% commission rate or a salary greater than $20,000: SELECT employee_id FROM employees WHERE commission_pct =.4 OR salary > 20000 ORDER BY employee_id;
Online Table Redefinition Examples → descriptions of all DBMS_REDEFINITION subprograms. Example Description Example 1 Redefines a table by adding … single table partition, moving it to a different tablespace. Example 1 This example illustrates online … redefinition of the previously created table hr.admin_emp, which at this point only contains columns … : empno,
Parallelizing Table Creation → When you specify the AS SELECT clause to create a table and populate it with data from another … table, you can utilize parallel execution. The CREATE TABLE...AS SELECT statement contains two parts: a … in the CREATE TABLE...AS SELECT statement An ALTER SESSION FORCE PARALLEL DDL statement is … one of the tables
Adding Table Columns → To add a column to an existing table, use the ALTER TABLE...ADD statement. The following statement … alters the hr.admin_emp table to add a new column named bonus: ALTER TABLE hr.admin_emp ADD (bonus … NUMBER (7,2)); If a new column is added to a table, the column is initially NULL unless you specify
Error Logging Table Format → The error logging table consists of two parts: A mandatory set of columns that describe the error … . For example, one column contains the Oracle error number. Table 18-1 lists these error description … names match the column names from the table being inserted into (the \"DML table\"). The number of … columns
Creating a CHAINED_ROWS Table → To create the table to accept data returned by an ANALYZE...LIST CHAINED ROWS statement, execute … table named CHAINED_ROWS in the schema of the user submitting the script. Note: Your choice of script to … execute for creating the CHAINED_ROWS table is dependent upon the compatibility level of your … database and the type of table
The Job Table → The job table is a container for all the jobs, with one table per database. The job table stores
Example: Creating a Table → When you issue the following statement, you create a table named admin_emp in the hr schema and … store it in the admin_tbs tablespace: CREATE TABLE hr.admin_emp ( empno NUMBER(5) PRIMARY KEY, ename … (department_id)) TABLESPACE admin_tbs STORAGE ( INITIAL 50K); COMMENT ON TABLE hr.admin_emp IS 'Enhanced … employee table';