Object names must be no more than 30 charactors. The charactors can be letters, digits, underscore, dollar, hash(#). ( To use double enclose will break the rule, but can’t over 30 charactors limition either. )
Within a schema, tables, views and synonyms cannot have the same names. ( But some other objects types like index, have their own namespace. )
All examinees will be expected to know about these data types: VARCHAR2, CHAR, NUMBER, DATE, TIMESTAMP, INTERVAL, RAW, LONG, LONG RAW, CLOB, BLOB, BFILE, and ROWID. Detailed knowledge will also be needed for VARCHAR2, NUMBER, and DATE.
VARCHAR2: Variable-length character data, from 1 byte to 4KB. The data is stored in the database character set.
NUMBER: Numeric data, for which you can specify precision and scale. The precision can range from 1 to 38, the scale can range from -84 to 127.
DATE: This is eight length zero, if the column is empty, or 7 bytes. All DATE data includes century, year, month, day, hour, minute, and second. The valid range is from January 1,4712BC to December 31,9999 AD.
A constraint violation will enforce an automatic rollback of the entire statement that hit the problem, not just the single action within the statement, and not the entire transaction.
It is possible to insert many rows with NULLs in a column with a unique constaint. This is not possible for a column with a primary key constraint.
Unique and primary key constraints need an index. If one does not exist, one will be created automatically.
A foreign key constarint is defined on the child table, but a unique or primary key constraint must already exist on the parent table.
By default, constraints are enabled and validated, and they are not deferrable.
The “public” in “public synonym” means that it is not a schema object and cannot therefore be prefixed with a schema name. It does not mean that everyone has permissions against it.
The CURRVAL of a sequence is the last value issued to the current session, not necessarily the last value issued. You cannot select the CURRVAL until after selecting the NEXTVAL.
An INSERT command can insert one row, with column values specified in the command, or a set of rows created by a SELECT statement.
Any SELECT statement, specified as a subquery, can be used as the source of rows passed to an INSERT. This enables insertion of many rows. Alternatively, using the VALUES clause will insert one row. The values can be literals or prompted for an substitution variables.
One UPDATE statment can change rows in only one table, but it can change any number of rows in that table.
The subqueries used to SET column values must be scalar subqueries. The subqueries used to select the rows must also be scalar, unless they use the IN predicate.
Always remember that server processes read blocks from datafiles into the databases buffer cache, DBWn writes blocks from the database buffer cache to the datafiles.
Undo is not the opposite of redo! Redo protects all block changes, no matter whether it is a change to a block of a table segment, an index segment, or an undo segment. As far as redo is concerned, an undo segment is just another segment, and any change to it must be made durable.
What does DBWn do when you issue a COMMIT command? Answer: absolutely nothing.
The redo log stream includes all changes: those applied to data segments and to undo segments, for both committed and uncommitted transactions.
Any DDL command, or a GRANT or REVODE, will commit the current trasaction.
PL/SQL always executes within the database, no matter where it is stored. Java can run either within the database or on the user machine.
It is impossible to run a trigger by any means other than its triggering event.
No transaction can ever span multiple undo segments, but one undo segment can support multiple transactions.
Active undo can never be overwritten; expired undo can be overwritten. Unexpired undo can be overwritten, but only if there is a shortage of undo space.
If a DML statement runs out of undo space, it will be rolled back. The rest of the transaction that had already succeeded remains intact and uncommitted.
Unless specified at creationg time in the datafile clause, the datafiles of an undo tablespace will not be set to autoextend. But if your database is created with DBCA, it will enable automatic extension for the undo tablespace’s datafile with maximum size unlimited. Automatic extension can be enabled or disabled at any time, as it canbe for any datafile.
Conditional clauses compare two terms using comparison operators. Knowing the data types of the terms is important so that they can be enclosed in single quotes, if necessary.
Boolean operators OR and AND allow multiple WHERE clause conditions to be specified while the NOT operator negates a conditional operator and may be used several times within the same condition. The equality, inequality, BETWEEN, IN, and LIKE comparison operators test two terms within a single condition. Only one comparison operator is used per conditional clause.
Precedence Level: () /,* +,-
=,<,>,<=,>= [NOT]LIKE,IS[NOT]NULL,[NOT]IN [NOT]BETWEEN !=,<> NOT AND OR
A common mistake is to assume that the return data type of single-row functions is the same as the category the function belongs to. This is only true of the numeric functions. Character and date functions can return values of any data type. For example the INSTR character function and the MONTHS_BETWEEN date function both return a number. It is also common to erroneously assume that the difference between two dates is a date, when in fact it is a number.
The TO_NUMBER function converts character items into numbers. If you convert a number using a shorter format mask, an error is returned. If you convert a number using a longer format mask, the original number is returned. Be careful not to confuse TO_NUMBER conversions with TO_CHAR. For example, TO_NUMBER(123.56,’999.9’) returns an error, while TO_CHAR(123.56,’999.9’) returns 123.6.
The parameters of the general function NVL2 can be confusing if you are already familiar with NVL. NVL(original,ifnull) returns original if it is not null, or else ifnull is returned. The NVL2(original,ifnotnull,ifnull) function returns ifnotnull if original is not null, or else ifnull is returned. The confusion may arise because the second parameter in the NVL function is ifnull, while the second parameter in the NVL2 function is ifnotnull. Be mindfull of the meaning of the parameter positionj in functions.
There are two fundamental rules to remember when studying group functions. First, they always operate on a single group of rows at a time. The group may be one of many groups a dataset has been segmented into, or it may be an entire table. The group function executes once per group. Second, rows with nulls occuring in group columns or expressions are ignored by all group functions, except the COUNT(*) form of the COUNT function.
Any item in the SELECT list that is not a group function must be a grouping attribute of the GROUP BY clause.
A dataset is divided into groups using the GROUP BY clause. The grouping attribute is the common key shared by members of each group. The grouping attribute is usually a single column but may be multiple columns or an expression that cannot be based on group functions. Note that only grouping attributes and group functions are permitted in the select clause when using GROUP BY.