This section details information about Oracle specific SQL and database information. I have placed here stuff which doesn't fit elsewhere so there is not much structure to the information here - bit of a lucky dip. This page will be added to as I get the time to type stuff up.
Copying a table
You may want to copy a table of data whilst you maintain the original, for instance if you want to remove a column from a table you will need to delete the table and recreate it.
Copying a table is very easy in Oracle (other RDBMS's have varying syntax but the result is the same).
CREATE TABLE BOOK_COPY AS
SELECT * FROM JD11.BOOK
This statement copies the data and structure from the BOOK table and creates a new table (BOOK_COPY). The only structural elements that are not copied are the constraints, only NULL / NOT NULL constraints are copied from the original table. You may use any valid
SELECT statement to create the table (so you can select a subset of records using a WHERE clause). You may also select defined columns rather than them all (as in the example) by specifying them instead of the "*" special character.
SELECT statement to create the table (so you can select a subset of records using a WHERE clause). You may also select defined columns rather than them all (as in the example) by specifying them instead of the "*" special character.
Oracle Data Dictionary
The data dictionary is the set of tables that Oracle uses to manage the database, these tables hold metadata (data about data).
The data dictionary can tell you about database objects, permissions,
rights, indexes, synonyms, sequences, constraints, users and audit information.
rights, indexes, synonyms, sequences, constraints, users and audit information.
The data dictionary is created by the CREATE DATABASE statement and is available from then on. Database users access data dictionary data by using standard SELECT statements, Oracle itself has update rights to the tables (which are owned by user SYS).
Many of the tables hold internal information which is difficult to interpret but Oracle provides various views of the data which interpret the information for you.
Views are named with a prefix which indicates the class of user that can view them, these views are :
How do you find out which views are available to you? All you need do is issue the command below :
SELECT * FROM DICTIONARY
This command lists all available views from a special table named DICTIONARY together with a brief explanation of each one. Then you just pick the views you think might be of interest and
SELECT * from them, happy hunting. Christophe Emery also suggests :
SELECT * FROM ALL_CATALOG ORDER BY TABLE_NAME
Some views that might be immediately useful to all users are given below, the synonym for the view is on the left (you may use the synonym for the view instead of the view name - CAT instead of
USER_CATALOG).
USER_CATALOG).
Oracle Locking
To ensure data integrity Oracle uses locks. Locks are used to prevent destructive interaction between processes accessing the same resource. Oracle uses two locking types, they are :
DML locks - used to protect data. Can be either table or row level.
Dictionary locks - used to protect the database structure.
Locks may be explicit or implicit.
Explicit locks are instigated by a process or user.
Implicit locks are undertaken by Oracle.
Oracle will lock any resource when it detects a valid attempt to update that resource.
Dictionary locks are always implicit.
DML locks may be implicit or explicit.
There are five locking modes, they are :
Locks are held until either a transaction is committed / rolled back.
It is possible to lock resources manually, you may want to do this if you require a consistent view across multiple joined tables or you don't want values changed during a long transaction.
There are two ways to lock a resource prior to an update transaction which are covered below.
Locking using SELECT for UPDATE
SELECT * FROM JD11.BOOK WHERE SECTION_ID = 10 FOR UPDATE OF SECTION_ID NOWAIT
With this SELECT we lock all the rows in the result set for later update. The FOR UPDATE tells Oracle to lock each row as it processes it - note that this statement does not actually update any data.
The OF keyword prefixes the column identification area which specifies which columns are going to be updated by us at a later date. The NOWAIT keyword specifies that we don't want the statement to wait until and current locks on the table are removed, this means that if the statement detects that the table or any row in the result set is locked it will return an error code and not
wait for the lock to be removed. We update the table data as normal, the lock is removed when we commit or roll back.
wait for the lock to be removed. We update the table data as normal, the lock is removed when we commit or roll back.
Locking using LOCK
We can lock an entire table if required - but this should be used very sparingly if at all.
LOCK TABLE JD11.BOOK IN EXCLUSIVE MODE NOWAIT
The above command will lock the BOOK table (provided it is not already locked). After the table name comes the lock definition area - in this case we are locking the table against everything other the queries.
ROWID
A ROWID is created by Oracle for each new row in every table, it is a pseudo column that has a value for every row in a table. The ROWID gives us the physical address of a row and is the fastest way to access any row.
The ROWID contains 3 bits of information, they are :
1. The block within the database file.
2. Row # within the block.
3. Database file ID.
An example could be :
000088C9.0191.0002
The ROWID has three important uses, it is :
1. The fastest path to any row.
2. A way of speeding the COMMIT process in application code.
3. Unique identifiers of any given row.
Since PL/SQL is an extension of SQL the same general syntax rules apply to both, they are :
1. Statements may be split across lines but keywords may not.
2. Lexical units (identifiers, operator names, literals) are separated by one or more spaces or other delimiters that will not be confused with the lexical unit.
3. Reserved words cannot be used as identifiers unless enclosed with double quotes.
Reserved words are :-
ACCESS, ADD, ALL, ALTER, AND, ANY, AS, ASC, AUDIT,
BETWEEN, BY,
CHAR, CHECK, CLUSTER, COLUMN, COMMENT, COMPRESS,
CONNECT, CREATE, CURRENT,
DATE, DECIMAL, DEFAULT, DELETE, DESC, DISTINCT, DROP,
ELSE, EXCLUSIVE, EXISTS,
FILE, FLOAT,
GRANT, GROUP,
HAVING,
IDENTIFIED, IMMEDIATE, IN, INCREMENT, INDEX, INITIAL,
INSERT, INTEGER, INTERSECT, INTO, IS,
LEVEL, LIKE, LOCK, LONG,
MAXEXTENTS, MINUS, MODE, MODIFY,
NOAUDIT, NOCOMPRESS, NOT, NOWAIT, NULL, NUMBER,
OF, OFFLINE, ON, ONLINE, OPTION, OR, ORDER,
PCTFREE, PRIOR, PRIVILEGES, PUBLIC,
RAW, RENAME, RESOURCE, REVOKE, ROW, ROWID,
ROWLABEL, ROWNUM, ROWS,
SELECT, SESSION, SET, SHARE, SIZE, SMALLINT, START,
SUCCESSFUL, SYNONYM, SYSDATE,
TABLE, THEN, TO, TRIGGER,
UID, UNION, UNIQUE, UPDATE, USER,
VALIDATE, VALUES, VARCHAR, VARCHAR2,VIEW,
WHENEVER, WHERE and WITH.
4. Identifiers can contain up to 30 characters and must start with an alphabetic character.
5. Character and date literals must be enclosed within single quotes.
6. Numeric literals can be represented by simple values (E.g. 0.32, -34, 01991, Etc.) or scientific notation (2E5 meaning 2x10 to the power of 5 = 200,000).
7.Comments may be enclosed between /* and */ symbols and may be multi line. Single line comments may be prefixed with a – symbol.
SQL Delimiters
Delimiters are symbols or compound symbols which have a special meaning within SQL and PL/SQL statements.
Operator Precedence
Evaluation Order (Highest precedence top down) | <><><><><><><><><> |
**, NOT | <><><><><><><><><> Exponentiation, local negation | <><><><><><><><><>
+, - | <><><><><><><><><> Identify sign | <><><><><><><><><>
*, / | <><><><><><><><><> Multiplication, division | <><><><><><><><><>
+, -, || | <><><><><><><><><> Addition, subtraction, concatenation | <><><><><><><><><>
=, !=, <<, >>, <=, >=, IS NULL, LIKE, BETWEEN, IN | <><><><><><><><><> Comparison | <><><><><><><><><>
AND | <><><><><><><><><> Conjunction | <><><><><><><><><>
OR | <><><><><><><><><> Inclusion | <><><><><><><><><>
Monitoring and Killing User Sessions
To find out the list of users currently online within an Oracle instance use this command:
SELECT SID, SERIAL#, USERNAME FROM V$SESSION;
The SELECT statement will return a list of online users. Note that up to the first seven processes returned (SID 1-7) are the Oracle main processes, DB Writer, archive, system and process monitors etc.
Under no circumstances should you kill an Oracle main process.
To terminate the user JD11's session we can now issue this command :
ALTER SYSTEM KILL SESSION '24,25';
Where 24 = SID and 25 = serial number.
No comments:
Post a Comment