Continuation of my notes to review before taking the 1Z0-047 certification.
Sequences
Rules:
- CURRVAL can’t be invoked in a session before NEXTVAL has been called in the same session
- If NEXTVAL used in statement, counter incremented even if statement doesn’t complete successfully
- CURRVAL / NEXTVAL can’t be used as column default
- CURRVAL / NEXTVAL can’t be used in subselect
- CURRVAL / NEXTVAL can’t be used with distinct
- CURRVAL / NEXTVAL can’t be used in WHERE clause
- CURRVAL / NEXTVAL can’t be used in CHECK constraint
- CURRVAL / NEXTVAL can’t be used with SET operators
Synonyms
Synonyms can be created for objects that don’t exist (or don’t yet exist)
Alter Table
- COLUMN keyword can’t be used with ADD or MODIFY
- Drop Column: If parentheses omitted, COLUMN keyword must be used. If parenthesis used, COLUMN keyword can not be used:
alter table ships drop column capacity;
OR
alter table ships drop (capacity);
- Constraints must be dropped before columns, unless “cascade constraints” clause used
- This includes FK constraints on other tables
- set unused column:
- Same effect as dropping column in that column no longer available, and can never get it back
- can create new column with same name as unused column
- unused columns still count toward column limit of 1000 per table
- when dropping unused columns, it is all or none:
alter table drop unused columns;
- use case: functionality of a drop, but returns immediately. Real drop can then be run during maintenance window
Foreign Key Options
- on delete cascade – deletes rows in child table when parent deleted
- on delete set null – updates column to null of child row when parent deleted
Create Table
The USING index clause of UNIQUE and Primary Key contsraints can be used to create indexes as well as specifying existing indexes:
create table ships
(
ship_id number primary key
using index (
create index ships_pk on ships(ship_id)
),
ship_name varchar2(50)
);
Flashback Table
Options
- To before drop
- To SCN xxx
- To timestamp xxx
- To restore point xxx
For all but “To before drop”, row movement must be enabled on the table:
alter table ships enable row movement;
SCN: Incremented with each commit and stored with each row. It can be identified with the pseudocolumn ora_rowscn
Restore Point: names scn so can be referenced later. A similar idea to savepoint.
The functions SCN_TO_TIMESTAMP and TIMESTAMP_TO_SCN can be used to get approximate values of one from the other.
Set Operators
- LOB fields can’t be used with SET operators
- Order By must only appear after last select, and applies to entire result of set operation
- Can order by position or by “reference” – column names must correspond to those of first select