Notes for 1Z0-047 Part III

By | January 6, 2011

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

Leave a Reply

Your email address will not be published. Required fields are marked *

Turn on pictures to see the captcha *