The SET DEFINE OFF command in Oracle prevents Oracle from doing any variable substitution.
Use the SET DEFINE OFF command in Oracle to prevent Oracle from interpreting the ampersand for variable substitution.
The SET DEFINE ON command turns variable substitution back on.
The Oracle DUAL table is a table created by Oracle and is used for selects statements that do not have a table name to go along with them.
For example, the DUAL table can be used to select pseudo columns, such as getting the currval or nextval from an Oracle sequence.
SELECT myseq.nextval FROM DUAL;
The DUAL table has one column, named “DUMMY,” which is a VARCHAR2 datatype of length 1. The value of the record is “X.”
All Oracle users have access to the DUAL table, although it is owned by “SYS.”
The DUAL table should not be updated, altered, or deleted.
The following syntax demonstrates how to create an Oracle tablespace:
CREATE TABLESPACE myTablespace
DATAFILE ‘/oracle/ts_mytablespace.dbf’
SIZE 100m
AUTOEXTEND on
NEXT 10m
MAXSIZE 500m;
The size of this tablespace is defined as 100 megabytes. By having the AUTOEXTEND attribute set to “on,” the statement is specifying that if the tablespace size exceeds 100 megabytes, more space will be allocated for the tablespace. The amount of space by which to extend the tablespace is specified by the “NEXT” attribute, which in this example is 10 megabytes. The tablespace will continue to grow until it reaches the maximum allowable size, which is determined by the “MAXSIZE” attribute – 500 megabytes.
Note: In order to create an Oracle tablespace, you must have the CREATE TABLESPACE privilege.
A tablespace is the Oracle way of referring to a database file. A tablespace is a file set up to contain tables and is the primary logical storage structure for Oracle databases. Logical storage means the tablespace itself cannot be seen in the file system of the database server. The tablespace relates database objects to particular data files. Data files are physically located in the file system of the server.
An Oracle tablespace keeps information about various aspects of tables, including:
Various types of tablespaces can exist in Oracle. A quite limited description of a few of these are:
Permanent tablespaces, such as the SYSTEM tablespace
Temporary tablespaces
Undo tablespaces
When a large amount of alphanumeric data needs to be stored in one record, the CLOB data type should be used.
CLOB stands for “Character Large Object.” The CLOB data type allows for the storage of up to 4GB of data. It’s basically the same as the VARCHAR2 data type, with the exception that it allows for the storage of much longer strings of data.
Note that the CLOB data type stores only alphanumeric data. If it is necessary to store other types of data, such as sound or video, the BLOB data type should be used.
To list all stored procedures in MySQL, simply execute the following command:
SHOW PROCEDURE STATUS;
A database diagram can be created in Toad for Oracle by using the “ER Diagram” tool. This can be access by clicking “Database” on the menu bar, then “Report”, then “ER Diagram.”
A blank area will then appear in the center of the screen. Tables listed in the “Object Palette” can be dragged onto the area. Following that, use the various functions on the menu bar to further refine the diagram.
A unique constraint is used to ensure that a singular value occur only once in a column or that a distinctive combination of values occur only once across a series of columns.
A unique constraint in Oracle can be disabled using the following syntax:
ALTER TABLE my_table DISABLE CONSTRAINT my_constraint;
A unique constraint is used to ensure that a singular value occur only once in a column or that a distinctive combination of values occur only once across a series of columns.
A unique constraint in Oracle can be dropped using the following syntax:
ALTER TABLE my_table DROP CONSTRAINT my_constraint;
A unique constraint is used to ensure that a singular value occur only once in a column or that a distinctive combination of values occur only once across a series of columns.
Primary keys are themselves unique constraints automatically and can’t be created as unique constraints. Oracle does not permit you to create both a primary key and unique constraint with the same columns.
CREATE TABLE my_table
(my_column datatype default null/not null,
my_column datatype default null/not null,
CONSTRAINT my_constraint UNIQUE (column1, column2, …column_n)
);
Null values are allowed in a unique constraint, provided that the combination of values is unique.
