Oracle DB: informations about functions, their parameters, DML and DDL statements
Informations for Oracle Certified Associate - part 1

Chapter 1
Here are the notes I wrote while I was reading OCA/OCP: Introduction to Oracle9i SQL. Might be useful if you want to quickly check some details e.g. which paramters are needed by a specific function.

Categories of SQL statements:

  • DML: Data Manipulation Language (select, update, lock table, ...). Access, modify, create and delete data.
  • DDL: Data Definition Language. Define, alter, drop DB objects and their privileges.
  • Transaction Control: Group a set of DML statements into a single statement (commit, savepoint, set transation, rollback)
  • Session Control: Control properties of a user session (alter session, set role)
  • System Control; Manage DB properties (alter system)

Datatypes:

  • CHAR:
    • Max length = 2000 bytes
    • If string is shorter than field length, blanks are inserted at the right side.
    • Default size = 1.
  • VARCHAR2:
    • Max length = 4000 bytes
    • No default value (size has always to be specified).
    • No blanks are inserted if string is smaller.
    • An empty varchar2(2000) takes up the same amount of space as an empty varchar2(2)
  • NUMBER ([precision],[scale]):
    • precision from 1 to 38
    • scale from -84 to 127
    • number(5,2): from -999.99 to 999.99
    • number(4,2): 1.125 => 1.13 / -1.125 => -1.12
    • number(3,5): from -0.00999 to 0.00999
    • number: p & s are set to the maximum
    • number(6,2): 12345.12345 => error (1 too much on the left side)
    • number(6,2): 123456 => error (2 too much on the left side)
    • number(6): 1234.98 => 1235
    • number(6): 123456.1 = 123456
    • number(5,-2): 12345.345 => 12300
    • number(5,-2): 1234567 => 1234600
    • number(*,1): number(38,1)
    • number(4,5): 0.1 => error
  • DATE
    • Its time component has a resolution of 1 second.
    • Needs 7 bytes
    • "CENTURY/YYYY/MM/DD/HOUR/MIN/SEC " is contained in every date datatype.
    • Date default is DD-MON-YY
    • Time default is 00:00:00
    • Default date type is specified in the NLS_DATE_FORMAT init parm. It can be changed in the session.

Operators:
An operator is a manipulator applied to a data item in order to return a result.

  • Unary operator: 2 or {- +}5: it has got only 1 operand (<operator><operand>)
  • Binary operators: 5+2 or 2*7: it has got 2 operands
  • Arithmetic operators:
    • unary operands: +/- (e.g. -5 or +2)
    • addition: +
    • subtraction: -
    • multiplication: *
    • division: /
  • Concatenation operators: ||
    • Used only for strings. Concatenate a '' (0 length) or a null with a string results in a string.
  • Set operators
    • Union: returns a distinct of all rows from both queries => no duplicate rows.
    • Union all: returns everything from both queries (sums them up)
    • Intersect: returns only a distinct of the common rows of both queries.
    • Minus: only rows returned by the 1st query and not the second one. (as with "not in")

Operator precedence:
The ones with the same precedence are evaluated from left to right. The innermost parentheses are evaluated first.
1) unary (+/-)
2) Multiplication division (*, /)
3) addition, subtraction, concatenation (+, -, ||)

Literals:
They represent a fixed value (constants).

  • Text / chars
    Max length = 4000 bytes
  • Integer
    Without comma. Max size = 38 digits
  • Number
    With comma. Scientivic notation is ok
  • Interval
    See chapter 7.

General informations:

  • Column alias names cannot be used in the where condition.
  • Inequality operators: "!=", "<>", "^=" are all the same and can all be used.
  • In the where-clause, numbers that contain a comma and which start with 0, may be written without 0. E.g.: 0.35 becomes .35.
  • ANY/SOME operators: "where num <= any(10,30)" Result: 9,10,11,12,... . It's like many "OR".
  • ALL: the field has to satisfy all the values contained in the parentheses: "where num <= all(10, 30)" Result: 9, 10. It's like many "AND".
  • With "NULL" only "is null"/"is not null" can be used.
  • I know only AND / OR. To generate the opposite behaviour it's possible to use "NOT": "WhERE NOT(NUM=10)". Result: 9,11,12,...
  • "!= ALL(10, 30)". Result: 9,11,12,...,29,31,....
  • When I use "NOT IN" and the subquery returns any "NULL" row, the result is "NULL".

Sorting (Page 30):

  • If "DISTINCT" is used, only the columns of the select statement can be used in the "ORDER BY" clause.
  • In ascending order sort, "NULL" rows appear at the bottom of the result. To avoid it: "ORDER BY NUM NULLS FIRST"

Case expression (page 36):

  • Select case num when 1 then 'ciao'
    when 2 then 'bao'
    when 3 then 'boh'
    end
    from numbers;
  • select case when num = 1 then 'ciao'
    when num = 2 then 'bao'
    when num = 3 then 'boh'
    end numeri
    from numbers;

Chapter 2: SQLPLUS (page 55)

  • after writing a query, "/" or "RUN" repeats the command.
  • "ED" or "EDIT" or "EDI" has the same effect.
  • The buffer file is called "afiedt.buf".
  • sqlplus edit commands:
    • LIST (L): lists the contents of the buffer
    • APPEND (A): appens a line of text to the active line of the buffer (remember to insert an extra blank at the beginning of the string).
    • CHANGE (C): C/<oldstring>/<newstring>. Case non sensitive.
    • INPUT (I): inserts a new line
    • <number> (pag. 62): displays that line of the buffer.
    • DEL [linenumber]: deletes the current line or if a number is specified deletes that line.
    • "CLEAR BUFFER" (CL BUFF): deletes the whole buffer.
  • SQLPLUS script commands:
    • "SAVE <filename>": saves contents of the buffer to a file. Doesen't do anything if the file already exists.
    • "SAVE <filename> REPLACE": creates a file if it does not exist yet, or overwrites an already existing one.
    • "SAVE <filename> APPEND": appends buffer to the end of the file.
    • "EDIT <filename>": edits
    • "GET <filename>": reads contents of a file and copies it into the buffer.
    • "START <filename>" / "@<filename>": executes a script
    • "SPOOL <filename>" / "SPOOL OFF": starts or stops spooling
  • SHOW ALL: shows all the sqlplus variables.
  • SET <varname> <value>: sets sqlplus variable value. Most vars can be abbreviated.
  • SQLPLUS variables (pag. 67):
    • HEADING: displays column names when select is executed.
    • LINESIZE
    • PAGESIZE
    • NEWPAGE: number of blank lines between pages.
    • PAUSE
    • SERVEROUTPUT: displays output of DBMS_OUTPUT.PUT_LINE.
    • SQLCASE: converts sql commands upper or lowercase just prior to execution.
    • SQLTERMINATOR: command used in order to end an execute sql statements (standard is ";").
    • SUFFIX: default extention used for files (DEFAULT = .sql)
    • TERMOUT: switches off/on output from command files.
    • TIMING: displays time needed to execute commands
    • UNDERLINE: char used in order to underline column names.
  • More than 1 variable can be set at once: "set linesize 500 pause on newpage 1"
  • Additional sqlplus commands: page 71 (SQLCODE: displays return code of most recent sql).
  • Store current sql environment: "STORE SET <filename>". If you call the file "login.sql" and store it in the same directory as the sqlplus exec file, it will be executed each time you log in.
    Columns:
  • Change heading of a column: "column <original column name> heading "ciao"'.
  • Change column width: "column <original column name> format a<charnbr>".
  • Long header on two lines: "column <original column name> heading "blabla|blabla"'.
  • Format numbers: "column <original column name> format "9,999.99".
  • Right/center/left justification of column headings: "column <orig.column name> justify <left/center/right>
  • Display settings of a column: "column <orig.column name>"
  • Delete column settings: "column <orig.column name> clear".
  • Switch on/off column formatting: "column <col.name> on/off".
  • Copy properties of a column to another column: "column <orig.column name> like <other col. name> [other options]".
  • Avoid double results when multiple lines are returned: "break on <col name> no duplicates.
    Headers & footers (pag.78):
  • Display a title on each page of the output (!check linesize!): "ttitle [justification] <string2display>"
  • Display a string at the end of each page: "btitle [justification] <string2display>
  • SQL.PNO displays the page number: "ttitle [justification] "<string2display"> sql.pno
  • REPHEADER displays a string on the first page.
  • REPFOOTER displays a string on the last page.
  • "skip <nbr>": skips "nbr" of lines
  • All together: "ttitle left "titolo sinistra" skip 1 center "titolo centro""
  • temporarily switch off: "ttitle off" / "btitle off" / "repheader off" / "repfooter off"
  • "CLEAR" can be used as well in order to:
    • clear breaks
    • clear columns
    • clear buffer
    • clear computes
    • clear screen
  • FEEDBACK: counts the lines that are returned by the query.
  • VERIFY: switches off the display of the variable and on the next line, the value in it.
  • COMPUTE: perform summary operations on grouped columns (Oracle9):
    "compute <operation like sum or avg> label "bla" of <field to be summed> on <summed by which column?>".
    This is usually used with "break on".
    COMPUTE SUM OF SALARY ON DEPARTMENT_ID BREAK ON DEPARTMENT_ID;
    Script variables:
  • Variables have "&" in front
  • The "&" is defined in the "define" sqlplus parameter ("SHOW DEFINE").
  • Sqlplus prompts for a variable, only if it is not defined yet. In order to define it use: "define <varname without &> = <value>" OR "undefine <varname without &>". The value will always be saved as "char".
  • List all the defined variables: "define".
  • List only one variable: "define <varname>".
  • Append "ciao" to the contents of the variable &dept: "select * from bla where deptid = '&deptid.ciao' ".
  • Variables can substitute anything (fields, literals, "from", tablenames, ...).
  • Variables that substitute char or date literals, must be enclosed in single quotes, otherwise the user has to put them when he writes the value.
  • Use "&&" in order to save the definition, so that it gets reused. E.g.: "select * from ps_dept_tbl where deptid = &&ciao" (here "ciao" gets saved and the system won't ask again for its value if the statement is runned more then once. The variable survives for the whole session exitstance.).
  • If there is more then one variable in the statement, the system starts from the top to the bottom from left to right.
  • "undefine" deletes the variable: "undefine ciao".
  • With the "accept" command it is possible to create prompts: "accept myvar char prompt "Questo e' il prompt: " [hide]".
  • ACCEPT is the only command in sqlplus that can be used to create a non-char datatype variable. DEFINE creates only CHAR datatypes.

ISQLPLUS (Page 93):

  • The Isqlplus architecture has 3 layers:
  • DB layer: Oracle 9DB and OracleNet
  • Middle layer: Oracle HTTP server and Isqlplus server
  • Client layer: user interface running on the web
  • Multiple sql statements must end with a ";".
  • The query can be executed only by pushing on the execute button.
  • None of the buffer editing commands is accepted.
  • Other commands that are not accepted: page 96.

Chapter 3: Single-Row functions (Page 111)

  • There are 5 different types of functions:
    • single-row (this chapter)
    • aggregate (chapter 4)
    • analytical
    • object-reference
    • programmer-defined
  • Functions can be used in the "select", "where" and "order by" clauses. Single-Row functions cannot be used in "having" clauses. Only aggregate functions can be used there. Single-row functions can be used as well in "set" of update statements, "values" of insert statement, or the "where" of a delete statement.
     
  • Single-row functions are divided into the following categories:
    • null-value functions (pag. 113)
    • character functions (pag 115)
    • numeric functions (pag 129)
    • date functions (pag 138)
    • conversion functions (pag 149)
    • other functions (pag 164)
     
  • Null-value functions:
    • nvl(X1, X2): Returns X2 if X1 is null. Otherwise X1.
    • nvl2(X1, X2, x3): If X1=null returns x3, otherwise X2.
  • Character functions:
    • ascii(S1): Returns ascii code of first char of S1 (S1 may be a string).
    • chr(N1): Returns char of N1 binary code.
    • concat(S1, S2): Concatenates S1 and S2. If one of the two is null, the other is returned. If both are null, null is returned.
    • initcap(S1): Returns S1 with first char uppercase and remaining ones lowercase.
    • instr(S1, S2, [N1], [N2]): Start search in S1 at N1 (integer) and return the position of the N2 occurrence where S2 is found (in S1). Default of N1 and N2 is 1.
    • length(S1): Returns length of S1. If S1 is null, null is returned. Therefore, it never returns 0.
    • lower/upper(S1): Returns S1 all upper/lowercase.
    • lpad/rpad(S1, N1, [S2]): Default value of S2 is a blank. Function takes S1 and if the content is bigger than S2, it truncates it, otherwise it makes it as big as specified in S2 and fills in N1 in the new places (lpad to the left and rpad to the right).
    • ltrim/rtrim(S1, [S2]): Default value for S2 is a blank. The function searches s2 in s1 starting from left / right side and takes out from S1 all the S2 chars found until a different one is found.
    • replace(S1, S2, [S3]): Default value of S3 is null. Replaces in S1 all S2 by S3.
    • substr(S1, N1, [N2]): Default value of N2 is the length of S1 from N1 onwards (only what is after N1 is displayed). If N1 is negative it counts from right to left.
    • soundex(S1): phonetic representation of S1.
    • translate(S1, S2, S3): If S1, S2 or S3 is null, a null is returned. It replaces in S1 any character of S2 with the ones found in S3. E.g.: "translate('Marco', 'fSe', 'ica')" Result: "ctaiano".
    • trim ([[leading/trailing/both] s2 from] s1): If any S=null than null is returned. Default value of S2 is a blank. Returns S1 with all occurrences of S2 removed from one of the sides or both (not from the middle of the string).
  • Numeric functions (page 129):
    • abs(N1): Returns the absolute value of N1. E.g.: -1 becomes 1. 1 stays 1.
    • bitand(N1, N2): Perform on bit field types and operation.
    • ceil(N1): Rounds UP N1 to the next integer:
      12.51 => 13
      12.49 => 13
      -12.49 => -12
      -12.51 => -12
  • floor(N1) does the opposite as ceil().
  • exp(N1): Returns e raised to N1. Result has a 36 digits precision.
  • ln(N1): Returns natural logarithm of N1. Result has a 36 digits precision.
  • log(N1, N2): Returns the logarithm base N1 of N2. Result has a 36 digits precision.
  • mod(N1, N2): Returns il resto of N1/N2. If N1 is negative, the result is negative.
  • power(N1, N2): Returns N1 to the power of N2.
  • round(N1, N2): Rounds N1 to N2 digits of precision:
    12.51 => 12.5
    12.49 => 12.5
    12.45 => 12.5
    12.44 => 12.4
    -12.44 => -12.4
    -12.45 => -12.5
    -12.49 => -12.5
    -12.51 => -12.5
  • sign(N1): Returns -1 if N1 is negative, 1 if positive and 0 if it's 0.
  • sqrt(N1): Returns square root of N1
  • trunc(N1, N2): Returns N1 truncated to N2 digits of precision to the right of the decimal.
    trunc(num, 1): 123.45 => 123.4
    123.46 => 123.4
    123.44 => 123.4
    trunc(num, -1): 123.45 => 120
    1234.56 => 1230
  • Date functions (page 138):
  • The date is stored as a number: [days since 4712 B.C.][fraction of a day (e.g. 0.5 is 12 o'clock)]
  • The NLS (National Language Support) parameters of the DB allow me to nationalize by DB.
  • The command "alter session set NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS' " will set the format for the implicit DB conversion. The convertion works both ways (by selecting from a table an as well inserting)
    • add_months(D, N1): Returns D plus N1 months.
    • current_date: Returns current date for the session's time zone (session timezone)
    • current_timestamp[P]: Returns the session's timestamp for the session's timezone to P digits of precision (default=6).
      Returns date/time/timezone
  • localtimestamp: like "current_timestamp" but without timezone.
  • dbtimezone: page 142
  • extract(C from D): "select extract (year from sysdate) from dual".
    Valid values are:
    year
    month
    day
    hour
    minute
    second
    timezone_hour (field must support this)
    timezone_minute (field must support this)
    timezone_region (field must support this)
    timezone_abbr (field must support this)
  • from_tz(A, B): Returns stimestamp for timezone.
  • months_between(D1, D2): Returns nbr of months of (D2 - D1) * -1
    (1.Dec.2004, 1.Feb.2005) => -2
  • new_time(D1, TZ1, TZ2): boh
  • next_day(D1, S1): REturns the date (as specified in NLS_DATE_FORMAT) from D1 onwards (excluded D1), when the day specified in S1 (DB-language) will be. E.g.:
    If today is 21.12.2004, Tuesday:
    select next_day(sysdate, 'Dienstag' (case is not important)) from dual; => 28.12.2004
  • round(D1, [format]): Default for format is the date part of NLS_DATE_FORMAT (without HH/MI/SS). It rounds the date + time up/down.
    If it's '21-12-2004 16:42:54': select round(sysdate, 'MI') => 21-12-2004 16:43:00
  • sessiontimezone: see page 147
  • sys_extract_utc: see page 147
  • systimestamp: see page 147
    • returns a value that includes a time zone
  • sysdate
  • trunc(D1, [format]): truncates the date. "select trunc(sysdate, 'MI') => 21-12-2004 16:42:00
  • tz_offset
  • Conversion functions (page 149):
    • asciistr(S1): Returns all ascii chars of S1 and the ASCII representation of non-ascii characters of S1. E.g.: select asciistr('sch�tte') => sch\00FCtte
    • bin_to_num(B1): Converts bit to numbers.
    • cast(E1 as T1): Converts E1 to datatype T1.
      STR is a string:
      insert into stetemp select cast(sysdate as varchar2(30)) from dual;
      select upper(str) from stetemp
      No translation is performed.
    • chartorowid(S1): Converts string S1 to a rowid (Page 153).
    • rowidtochar(RID): No translation is performed.
    • compose/decompose(S1): Returns S1 as a unicode string in a fully normalized form (Page153).
    • convert(S1, DSET, [SSET]): Returns S1 converted from the source character set SSET to the destination character set DSET. Default for SSET is the DB character set.
    • hextoraw(H): Converts the hexadecimal string H to a raw datatype.
    • rawtohex(R)
    • numtodsinterval(N1, S1): Converts N1 to a datetime value specified in S1. E.g.:
      Now: 21-DEZ-2004 18:34:12
      select sysdate + numtodsinterval(25, 'minute') from dual;
      => 21-DEZ-2004 18:59:12
    • to_char(D/N1 [,format] [,nlsparm]):
      - D/N1 is a date or a number
      - format specifies the format of D/N1
      - NLSPARM specifies the language or location formatting conventions.
      Returns a string. E.g.:
      1)
      select to_char(sysdate, 'DD-Month-YYYY', 'NLS_DATE_LANGUAGE = Italian') from dual;
      21-Dicembre-2004
      (month => dicembre / Month => Dicembre / MONTH => DICEMBRE)
      2)
      SELECT TO_CHAR(-1234, 'L9G999D99')
      -SF 1.234,00
    • to_date(S1 [,format] [nlsparm]):
      - S1 is a string
      - format is the format of S1
      - NLSPARM is the language or location
      E.g.:
      select to_date('Dicembre', 'Month', 'NLS_DATE_LANGUAGE = Italian')
      01-DEZ-2004 00:00:00
    • to_dsinterval(S1 [,NLSPARM]): page 162
    • to_multi_byte(S1): page 162
    • to_number(S1 [,format] [,NLSPARM]): page 163
    • to_single_byte(S1): page 163
    • to_yminterval(S1): page 163
    • unistr(S1): page 163
    • Other functions (page 164)
      • bfilename(S1, S2): Returns type "BFILE_LOC". A kind of pointer to a file. The file doesn't have to exist, when the function is used, but it has to, when the pointer is used.
      • coalesce(LIST): Returns the first non-null value of the list. If all of them are null, null is returned. Each item of the list should be the same type.
        select coalesce(null, null, 'ciao', 'bao') from dual; => ciao
  • decode (page 166): Does not have to return a value (null is returned in this case).
  • dump(page 167)
  • empty_blob(): Takes no arguments. Used to initialize a blob variable or column.
  • empty_clob()
  • greatest(LIST): Returns greatest value of the list. If a null is contained, a null is returned.
    select greatest('A','B','C') from dual => C
  • least(LIST): Opposite of greatest. (it's not a grouping function)
  • nullif(X1, X2): Returns null if X1=X2 or if X1=null. Otherwise returns X1.
  • sys_connect_by_path(X1, C1) (page 170): X1 is a column, C1 is a char.
  • sys_context: Replaces function USERENV.
  • UID: Returns numeric user id of the current user.
  • user: Returns user name of the current user.
  • userenv(<option>) (page 176): Option may be:
    !!!In Oracle9 use SYS.CONTEXT instead of USERENV!!!
    ISDBA
    SESSIONID
    ENTRYID
    INSTANCE
    LANGUAGE
    LANG
    TERMINAL
  • vsize(X): Returns size in bytes of X.

Chapter 4: Group functions (Page 189)

  • Group functions almost never return a null, even if only nulls are evaluated.
  • Functions that can be nested in grouping functions are both group and single-rows functions.
  • Opposite of "distinct" is "all". "all" is the default.
  • Group functions cannot appear in the "where" clauses.
  • Programmer-written functions cannot be used on grouped data.
  • Functions (page 195):
    • avg([distinct/all] N1): Average of N1.
    • corr(N1, N2): boh - page 195 - Can return null!!!
    • count([distinct/all] */X1):
      select count(X1) from boh: Counts all the non-null lines.
      select count(*) from boh: Counts all the lines.
      select count(distinct *) from boh: Not possible.
  • covar_pop(N1, N2): boh (page 197)
  • covar_samp(N1, N2): boh (page 198)
  • cume_dist(?): boh (page 198)
  • dense_rank(?): boh (page 199)
  • first: See KEEP
  • group_id(): Returns id of duplicate groups (Page 201)
  • grouping(EXPR) (page 201): EXPR is an expression contained in the "group by" clause. This function can be used only in queries that have a "group by" + a "cube" or "rollup" clause. It returns a 1 if the line that is displayed is for the column specified, an aggregation.
  • keep / last: boh. (page 202
  • max/min (page 204): Nulls are not taken into consideration.
  • percent_rank: (page 205)
  • percentile_count: (page 206)
  • percentile_disc: (page 208)
  • rank: (page 209)
  • regr_avgx(Y, X): Removes first pairs that contain null in either Y or X and calculates afterwards the average of X (can return null!)
  • regr_avgy(Y, X): Same as REGR_AVGX, but calculates average of Y.
  • regr_count(Y, X): Same as REGR_AVGX, but counts the remaining lines.
  • regr_intercept(Y, X): page 212
  • regr_r2(Y,X): page 212
  • regr_slope(Y, X): page 212
  • regr_sxx(Y, X): page 212
  • regr_sxy(Y, X): page 213
  • regr_syy(Y, X): page 213
  • stddev: page 214
  • stddev_pop: page 215
  • stddev_samp: page 215
  • sum([distinct/all] N1): Can return null
  • var_pop(X): You cannot use DISTINCT in this function! page 216
  • var_samp: page 217
  • variance: page 217
  • having: As the operations on grouped data have to happen after the data has been grouped, such operations cannot be put in the "where"clause, but have to be put in a separate clause: having.
  • cube / rollback aggregates (page 221): Cube creates totals for all possible distinct combinations of the fields that are displayed. The order of expressions is significant.
    Rollup creates totals for the column over the one specified in its brackets (hierarchical strucutre). The order of expressions is NOT significant.

Chapter 5: Joins & subqueries (Page 242)

  • "Equality join" / "inner join": That means joining two tables using "=" (equality operator).
  • When more than 2 tables have to be joined, oracle joins first two of them, and joins afterwards the result to a third table and so on. More than 2 tables are never joined at the same time.
  • "Complex joins" are queries that have in the "where" clause not only the join, but as well a condition that limits the rows.
  • "select sysadm.ps_dept_tbl.deptid from ps_dept_tbl" will produce an error.
  • "select ps_dept_tbl.deptid from ps_dept_tbl is ok.
  • "select * from ps_dept_tbl d where ps_dept_tbl.deptid = '0106755' " will produce an error.
  • ansi/iso sql joins:
    • General infos:
      <tblname> natural [inner] join <tblname>
      - Here all the columns with the same name of the two tables are automatically joined.
      - As all columns with the same name (have to be of same datatype - otherwise unpredictable results are generated) are joined and therefore identical, no table specifier (like a.deptid) HAS to be used for the select clause (page 246).
      - The "select *" will display only once each column, even if it's in multiple tables.
  • Inner join:
    <tblname> natural [inner] join <tblname>
    <tblname> [inner] join <tblname> using (<column1>, <column2>, <...>)
    <tblname> [inner] join <tblname> on <condition1 --(only tbls mentioned previously can be referred to)--> [and/or/... <condition2> ...]
  • Outer join:
    <tblname> natural full/left/right [outer] join <tblname>
    <tblname> full/left/right [outer] join <tblname> on/using ...
  • Cartesian join:
    <tblname> cross join <tblname>
  • Self-join
    <tblname> [inner] join <tblname> on <condition> (not possible to use "natural" or "using")
  • nonequality join
    <tblname> [innter] join <tblname on <condition> (the condition has to include a != or < and so on)
     
  • Set operators:
    - union
    - union all
    - intersect
    - minus
  • Subqueries:
    • A max of 255 levels of subqueries can be used.
    • Types of subqueries:
      • Single row: returns 1 row
      • Multiple row: returns x rows
      • Correlated: join with parent queries � when the subquery joins one of its columns to one of the parent query.
      • Scalar: returns 1 value fo each row. Usually used after the "select" clause, but it can be used everywhere � SELECT, WHERE, ORDER BY, CASE, single-row functions.
      • Multiple column: "where (bla, bla, bla) in (select bla, bla, bla) ...."
    • Subqueries in DML statements (pages 272 + 273):
      - "with read only": subq. cannot be updated.
      - "with check option": modify only rows that comply to the subquery. In this case a constraint type "v" is added to the data dictionary (you see it in USER_CONSTAINTS.
    • !!!You cannot have an "order by" in a subquery of a where clause.

Chapter 6: Modifying data (Page 289)
Chapter summary:

  1. Oracle supports the following DML statements:
    - insert (page 291)
    - update (page 296)
    - merge (page 297)
    - delete (page 299) (see truncate DDL page 300)
    - select for update (page 303)
    - lock table (page 303)
  2. Transaction control (page 309):
    - savepoints & partial rollback (page 311)
    - consistency & transaction (page 312)
    - spec rollback segment for a transaction (page 314)
 
  • Insert:
    • I cannot insert into a view that contains any of the following:
      - aggregate function
      - distinct operator
      - set operator
      - group/order/connect by clauses
      - subquery in the select list
    • Implicit data convertions are performed where possible. E.g. integer <=> string.
    • Insert into multiple rows:
  1. Same as classical statement but starts with "INSERT ALL" instead of just "INSERT":
    insert all into <tblname> [(<destination columns>)] [values (<source columns>)]
    into <tblname> [(<destination columns>)] [values (<source columns>)]
    ...
    select <source columns>
    from blabla
    insert all into stetemp1 (setid, deptid, descr) values (setid, deptid, 'table one')
    into stetemp2 (setid, deptid, descr) values (setid, deptid, 'table two')
    select setid, deptid
    from ps_dept_tbl
  2. With when clauses:
    insert <all/first> when <condition> then <into clause>
    when <condition> then <into clause>
    ...
    else <into clause>
    select <source cols>
    from blabla
    -insert first when deptid = '0106755' then into stetemp1(setid, deptid, descr) values (setid, deptid, 'first case')
    when deptid = '0892001' then into stetemp1(setid, deptid, descr) values (setid, deptid, 'second case')
    else into stetemp1(setid, deptid)
    select distinct setid, deptid
    from ps_dept_tbl
    where deptid in ('0106755', '0892001', '0892002')-
  • Update:
    Can update multiple columns at once using (...): update stetemp1 set (setid, deptid) = (select '00106', '0106755' from dual)
  • Merge:
    Can update and/or insert rows in a table.
    merge into <tblname> using <tbl/vw/subq> [<alias>] on <condition
    when matched then update set <expression>
    when not matched then insert (<expression>)
    values <expression>
    Example 1:
    merge into stetemp4 x
    using stetemp1 y on (y.id = x.id)
    when not matched then insert values (y.id, 'insert')
    when matched then update set x.name = 'update'
    Example 2:
    merge into stetemp4 x
    using stetemp1 y on (x.id = y.id)
    when not matched then insert (x.id, x.name) values (y.id, 'insert')
    when matched then update set x.name = 'update'
  • Truncate:
    • It is a DDL statement
    • truncate table <tablename> [drop/reuse storage]
      • drop (default):
        • resets highwatermark
        • makes table&index size = to minextent nbr of extents.
        • resets NEXT to last deallocated extent
      • reuse
        • won't change table&index size
        • won't reset NEXT
    • The "delete" statement is slow because it generates undo informations.
    • When truncate is issued, not committed statements are committed.
    • Doesn't fire delete triggers
    • "Drop any table" system privilege is needed in order to drop tables that belong to another schema.
    • Parent tables cannot be truncated without first disabling foreingn keys.
    • Does not invalidate related objects, drop indexes, ...
    • Before issuing the truncate statement all foreign key constaints have to be disabled.
  • "Select ... for update":
    • Locks selected rows.
    • Rollback or commit has to be issued in order to release the rows.
  • Lock statement:
    • lock table <tblname> in <locking mode> [nowait]
      locking mode:
      • exclusive mode: no other session is able to aquire a shared or exclusive lock
      • shared mode: no other session is able to get an exclusive lock. Shared locks are allowded.
    • E.g.: "lock table ste.stetemp in exclusive mode".
    • Data changes require an exclusive mode.
    • Deadlocks (page 304): happens when two sessions hold a lock and each other is waiting for the other one.
      User 1 time User2
      update field1 row1 1 ---
      table1

      --- 2 update field1 row1
      table2

      update field1 row1
      table1 3 ---
      (waiting for user2)

      --- 4 update field1 row1
      table1
      (waiting for user1)

  • Table & row lock exist:
    • table lock: can be exclusive or shared
    • row lock: is always exclusive
    • none of the lock types refuses read access
    • Creating or altering an index will always fail if there are uncommitted rows (row exclusive lock) for the table � "WAIT" cannot be specified in DDL statements.
  • The REVOKE statement will perform an implicit commit, as TRUNCATE does.

Ways of locking rows & tables:

 
Type of lock SS RX SRX S X
SS (Row Share) Y Y Y Y -
RX (Row Exclusive) Y Y* - - -
SRX (Share Row Exclusive) Y - - Y -
S (Share / Table Share) Y - - Y -
X (Exclusive / Table Exclusive) - - - - -

* Exception: if there is an update on a line, the youngest one waits for the oldest one to commit/rollback.

Syntax for explicit lock:

Type of lock  
SS Lock table xx in   Row Share Mode
RX Lock table xx in   Row Exclusive Mode
SRX Lock table xx in Share Row Exclusive Mode
S Lock table xx in     Share Mode
X Lock table xx in     Exclusive Mode

Implicit lock is aquired with:
SS: Select <fieldnames> from <tblname> where ��. for update
RX: Becomes active with "Insert / Delete / Update" statements

All DDL statements wait for all the locks.

Transaction control (pag. 309)
It coordinates multiple concurrent access to the same data.
The consistency can be set to be on "transaction" or on "statement" level. "Transaction" is what starts with an INSERT, UPDATE, MERGE, DELETE, LOCK, SET TRANSACTION or SELECT FOR UPDATE statement and ends with a COMMIT or ROLLBACK.
Statement consistency is the default method. Each single statement accesses the data with the status it had when the single statement was executed.
Transaction consistency: all the statements of a transaction access the data with the status it had when the transaction began.

In order to read data with a status older than the actual one, the rollback segments that were generated when the change was done, are accessed. SCN (System Change Numbers) are used to implement consistency.

 
  • SET TRANSACTION: Enables the consistency on "transaction" or "statement" level.
    • "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE": transaction
    • "SET TRANSACTION READ ONLY": transaction. This will make changes to data impossible (only SELECT without update, LOCK TABLE, SET ROLE, ALTER SYSTEN and ALTER SESSION are allowded). A COMMIT or ROLLBACK can be used to end the read-only transaction.
    • "SET TRANSACTION ISOLATION LEVEL READ COMMITTED": statement
  • ROLLBACK TO [SAVEPOINT]: does a rollback to a specific savepoint. If you've got more than 1 savepoint, and you rollback to the second-last one, the last one is rolled back as well - not possible to jump here and there.
    • INSERT INTO STE_TBL VALUES ('ciao');
    • SAVEPOINT STE_SAVEP1
    • insert into STE_TBL VALUES ('marco');
    • SAVEPOINT STE_SAVEP1
    • ROLLBACK TO SAVEPOINT STE_SAVEP1
    • only "ciao" is visible.
  • SET TRANSACTION can be used as well to use during a transaction a specific rollback segment. This might be handy when most of the statements are small, but e.g. during batch processing a couple of statements do large modifications. This way special rollback segments on big discs can be used (performance & space management).
    • "SET TRANSACTION USE ROLLBACK SEGMENT ste_large;"
      • ste_large (INITIAL 100M NEXT 100M MINEXTENTS 2)
      • ste_small (INITIAL 1M NEXT 1M MINEXTENTS 5)
      • Therefore it's better that big statements use ste_large

Chapter 7: Managing tables and constraints (pag 333)
USER_TABLES contains the user's tables.
DBA_TABLES contains all the DB tables
ALL_TABLES contains the user's tables and the ones he has access to.

Oracle built-in datatypes:

  1. Character
  2. Numeric
  3. Date and Time
  4. Binary
  5. Row ID

1- Character (pag 337):
Trying to insert a string that is larger than the field, will always return an error.

  • CHAR
    • If the value is smaller than the field, trailing spaces are inserted.
    • Default size is 1 byte
    • Masimum size is 200 bytes
    • Size can be specified in CHARS or BYTES. BYTE is the default
  • NCHAR
    • Used to store unicode character set
    • Size specified alyways in CHARS.
  • VARCHAR2 / VARCHAR
    • No default value.
    • Size can be specified as CHARS or BYTES
    • Min / max are 1/4000
    • Oracle recommends VARCHAR2.
  • NVARCHAR2
    • Same as above.
    • Size specified in CHARS
  • CLOB
    • Size does not need to be specified (it's variable-length).
    • Max 4GB
  • NCLOB
    • Unicode version of CLOB.
  • LONG
    • Discouraged by Oracle.
    • Use CLOB instead of LONG.
    • Max 2GB
    • Max 1 LONG column in each table
    • LONG cannot appear in WHERE, GROUP BY, ORDER BY clauses.

2- Numeric (Pag 339)

  • NUMBER
    • Can be used to represent all the non-Oracle numeric datatypes as integer, long, float, double, decimal, .... .

3- Date and Time (Pag 340)

  • DATE
    • Stores dates from 1.1.4712 BC to 31.12.9999.
    • If a date without time is specified, the default is 00:00:00
    • If a date without date is specified, the default is the first day of the current month.
    • It stores century, year, month, date, hour, minute and seconds.
    • The output can be formatted using the NLS_DATE_FORMAT parameter or by the function TO_CHAR. See chapter 3.
  • TIMESTAMP
    • The only difference between TIMESTAMP & DATE is the ability to store seconds with a fractional precision of 9 digits. Default is 6 and can range from 0 to 9.
  • TIMESTAMP WITH TIME ZONE (pag 341)
    • TIMESTAMP WITH LOCAL TIME ZONE - time zone displacement
  •  
  • INTERVAL YEAR TO MONTH
    • Represents an interval as years and months.
    • Precision from 0 to 9. Default is 2. It is the precision for the year field.
  • INTERVAL YEAR TO SECOND:
    • Represents a period of time in days, hours, minutes, seconds.
    • [<precision>] specifies the one needed for the day field. Default is 6. min = 0, max = 9.
  • Date arithmetic:
    • Operators that can be used with date/time datatypes: + -
    • Operators that can be used with interval datatypes: + - * /
    • "[datatype] + 1": adds one day
    • "[datatype] + 0.5": adds 12 hours
    • "[datatype] + 1"
    • subtraction of two dates gives result in days (number).
    • It's not possible to add two datetime values.
    • Use function NUMTODSINTERVAL to convert resulting number

4- Binary (Pag 346)
These datatypes store values without converting them into the DB charset. Videos, exe files, etc can be stored.

  • RAW
    • Stores up to 2000 bytes
    • Is variable-length
    • No default value
    • Size has to be specified
  • BLOB
    • Stores up to 4GB
    • No size has to be specified
  • BFILE
    • Up to 4GB
    • It contains only a pointer to a file saved in the OS.
    • BFILE is the only datatype that stores data outside the DB (CLOB, BLOB, NCLOB).
  • LONG RAW:
    • Only one long raw column per table
    • Supported for backward compatibility only => use BLOB

5- Row ID (pag 347)

  • ROWID
    • Stores physical address of a row
    • 2 types:
      • Logical rowid: stores addresses of rows in index-organised tables.
      • Physical rowid: stores addresses of rows in ordinary tables (clustered tables, table partitions, subpartitions, indexes, index partitions, subpartitions).
    • Physical rowids is fastest access method to a row in a table.
  • UROWID
    • Stores logical rowid of index-organised tables or non-oracle tables.
    • Logical rowid is created using the primary key of index-organized tables.
    • Logical rowid changes only if the primary key changes.

Creating tables (pag. 348)

  • Table, column names and column aliases (labels) can be max 30 chars long.
  • Table and column names can be stored as case sensitive with "". (not recommanded)
  • Table and column names have to begin with a letter ($ _ # are not allowded)
  • Table and column nambes may contain the signs $ _ #
  • When a table is created without specifying its type, a permanent relational table is created.
  • Use "CREATE <GLOBAL TEMPORARY> ...." in order to create a table whose definition is available to all sessions in the DB (pag. 350 - did not understand this!). The clauses specify how data has to be handled:
    • "on commit preserve rows": rows are available for the whole session
    • "on commit delete rows" (default): rows are available for the whole transaction (until the commit/rollback)

Specify default values for columns (pag. 351):

  • Implicit default value for a column: NULL
  • Default values can be as well sysdate, user, userenv, uid
  • Default values cannot be level, nextval, currval, rownum, prior
  • "create table ste_bla (name varchar2(50) default 'ciao')"
  • If I add a column with a default value to a table and the table already has rows, the new column will contain for all rows the default value.

Commenting tables and columns (pag.353):

  • "comment on table ste_bla is 'il mio commento'"
  • "comment on column ste_bla.col1 is 'colonna uno'"

Creating tables from other tables (pag. 354)

  • "create table ste_bla as select * ..."
  • Does not work if source table has "long" columns
  • No constraints and column defaults are copied. Exception: "not null" constraint.

Modifying tables (pag. 356)

  • Add columns:
    • Adding just 1 column: "alter table <tblname> add [(]nome varchar2(50) [)]"
    • If multiple columns are added, parentheses have to be used: "alter table <tblname> add (nome varchar2(50)), (amount number), ..."
    • It is not possible to directly specify "not null" constraint if the table already has rows.
  • Modify columns (pag. 358):
    • "alter table <tblname> modify <colname> <new attributes>
    • "alter table <tblname> modify (<colname> <new attributes>), (<colname> <new attributes>), ... . E.g.: "create table ste_tbl1 (amount number(10,2))" => "alter table ste_tbl1 modify amount number(8, 2)"
    • Omitted parts (datatype, default value, constraints) remain unchanged.
    • A new default value affect only new rows.
    • To remove a default value, set a default of "NULL"
    • Rules for modifying column definitions: pag 359
  • Drop columns (pag. 359)
    • drop 1 column: alter table <tblname> drop column <colname> [CASCADE CONSTRAINTS]
    • drop multiple columns: alter table <tblname> drop (<colname1>, <colname2>) [CASCADE CONSTRAINTS]
  • Rename columns
    • Not possible in Oracle
  • Mark column as unused (pag. 360):
    • Mark 1 column: "alter table <tblname> set unused column <colname> [CASCADE CONSTRAINTS]". This way the column is marked for deletion.
    • Mark multiple columns: "alter table <tblname set unused (<colname1>, <colname2>) [CASCADE CONSTRAINTS]"
    • Saves resources. Columns can be dropped later.
    • After marking the column as unused, it is not visible with the command "describe <tblname>". They're in DBA_UNUSED_COL_TABS, ALL_UNUSED_COL_TABS, USER_UNUSED_COL_TABS
    • Drop a column which is marked as unused:
      • Drop all unused columns: "alter table <tblname> drop unused columns"
      • Continue a previously interrupted drop operation: "alter table <tblname> drop columns continue"
  • Drop tables:
    • "drop table <tblname> [CASCADE CONSTRAINTS]"
    • marks views, mat views, stored programs that reference the table as invalid.
  • Rename table (pag. 361):
    • "rename <old name> to <new name>" (possible only for objects owned by the user that executes the command).
    • Use "alter table <owner>.<tblname> rename to <newname>" command for objects that belong to other users.

Constraints (pag. 362)

They're created by "create table" & "alter table".
If no name is specified, oracle creates a unique name that starts with "sys_". This is not recommanded as it might become confused.
Types:

  • NOT NULL: Prevents null values 2b entered
  • CHECK: checks if a condition is satisfied
  • UNIQUE: checks that there are no duplicate values for the column(s) that are specified
  • PRIMARY KEY:
    • only 1 PK per table is allowded.
    • uniquely identifies each row of the table + prevents null values
  • FOREIGN KEY:
    • the FK of a table identifies a PK or unique key of another table => establishes a parent/child relationship
      All these constraints are applied by "create table" or "alter table". If the name is not specified, the system creates one that starts with "sys_". To specify the name of a constraint, write "constraint <cons name>".
      In order to create disabled constraints (they're enabled as default when they're created) you specify the word "disable" after the constraint definition.

The constraint definitions are saved in (pag. 368):

  • USER_CONSTRAINTS
  • ALL_CONSTRAINTS
  • USER_CONS_COLUMNS
  • ALL_CONS_COLUMNS
    At CS we have a lot of NOT NULL constraints.

NOT NULL:

  • "create table ste_tbl (name varchar2(50) [constraint steconst] not null, lastname varchar2(50) not null": the first constraint name is user-defined, but the second one will be "sys_..."
  • "alter table ste_tbl modify name null"
  • The "not null" constraint is the only one which is take over by a statement like "create table stetable as select * from ciaotable".

CHECK:
It can be defined at the column & table level.
"[CONSTRAINT <consname>] CHECK <condition>"
The condition:
Cannot use environment functions (sysdate, user, ...)
Cannot use pseudo-columns (rownum, currval, nextval, level, ....)
Should evaluate to a boolean result
Can refer to other values in the same row
Cannot use queries
Can contain more than 1 check constraint
Example of checks:

  • At table level: "create table ste_tbl (name varchar2(50), salary number, [CONSTRAINT ciao] check (salary > 0)". (Not possible to use "alter table modify...")
  • At column level: "alter table ste_tbl add [CONSTRAINT ciao] check (bonus < salary)"
  • Example of a check that is dipendent on another check:
    "alter table ste_tbl add [CONSTRAINT ciao] check
    (
    (bonus is null and salary is null)
    or
    (bonus is not null and salary is not null)
    )

UNIQUE:
Single-column syntax: "[CONSTRAINT <consname>] unique"
Table syntax: ", [CONSTRAINT <consname>] unique (<col1>, <col2>)"

  • If an index for the same columns (in the same order) already exists, oracle uses it (no duplicate values must exist).
  • If an index for the same columns doesn't exist yet, oracle creates a unique index.
  • Null values are allowded.

PRIMARY KEY (pag 366):

  • The same as the "unique" constraint, except nulls are not allowded.
  • Only 1 PK for each table.
  • Column-level syntax: "[CONSTRAINT <consname>] PRIMARY KEY"
  • Table-level syntax: "[CONSTRAINT <consname] PRIMARY KEY (<col1>, <col2>, ...)"
  • Oracle creates a unique index It cannot be dropped using "drop index".
  • Add a PK: "alter table ste_tbl add [CONSTRAINT <consname>] PRIMARY KEY (<col1>, <col2>, ...).

FOREIGN KEY (pag. 367):

  • Ensures that for the combination of columns, there is data available in the primary or unique index of the parent table.
  • The data types of the columns in the 2 tables should be the same.
  • Column-level syntax: "[CONSTRAINT <consname>] references <tblname> [(<col1>, <col2>, ...)] [on delete cascade/set null]"
  • Table-level syntax: ", [CONSTRAINT <consname>] foreign key [(<col1>, <col2>, ...)] references <tblname> [(<col1>, <col2>, ...)] [on delete cascade/set null]"
  • Null values are allowded.
  • No index is created.
  • The "on delete" clause specifies what has to be done when rows are deleted in the parent table.
    • delete them in the child table.
    • set them to null
      If the clause is omitted, no deletes are allowded for the parent table, if the child table contains such rows.
      Pag. 368

Dropping constraints (pag. 369):

  • When a unique or primary key constraint is dropped, oracle drops the associated unique index.
  • It's not possible to disable a PK or unique key if enabled foreign keys reference them. Specify "cascade" to disable them as well.
  • As for PK or unique constraints an index is created, it is possible to specify its storage parameters:
    "Alter table ste_tbl enable primary key using index tablespace ste_indexes storage (initial 2M next 2M)

Disabling constraints:

  • "ALTER TABLE ORDERS DISABLE CONSTRAINT FK_ORDERS;"

Validate constraints (pag. 370)

  • The keywords "enable" and "disable" affect only future data. Use "validate" to perform a check on historic data.
    • enable validate (default)
    • enable novalidate
    • disable validate (no dml operations are allowded. Table is read-only)
    • disable novalidate (default)

Deferring constraint checks (pag. 371)

The constraint check is done as default when the statement is executed (IMMEDIATE).
If "DEFERRABLE" is specified, the transaction can set the checking-behaviour.

  1. "alter table ste_tbl add [constraint ste_cons] primary key (nome) deferrable"
  2. This way, with the command "set constraints <consname>/<ALL> <IMMEDIATE>/<DEFERRED>", the system can do the constraint check when the statement is executed (IMMEDIATE) or when the transaction ends by a commit/rollback (DEFERRED). In the last case, if the transaction ends, the statements that produced a problem will be rolled back.
  3. E.G. deferrable initially immediate" means that the constraint can be deferred using the "set constraints" command and that the default behaviour is to do the validation when the sql is executed.
  4. A default behaviour can be specified when the constraint is created:
    "alter table ste_tbl add [constraint ste_cons] primary key (nome) deferrable INITIALLY DEFERRED"
    If these options have to be changed, the constraint has to be dropped and recreated � not possible to alter it.
  5. PK and UNIQUE constraints defined as DEFERRABLE will create/use nonunique indexes.
  6. The default if nothing is specified is "NOT DEFERRABLE, INITIALLY IMMEDIATE".

Chapter 8: Managing tables and constraints (pag 389)

  • The tables that the view is referencing are called "base tables".
  • A view can be considered as a stored query or a virtual table. It is stored in the data dictionary.
  • The maximum number of columns that a view can contain is 1000. The same as with a normal table.
  • When numeric operations are performed in the view, the resulting column will be of "floating datatype" (NUMBER without any precision or scale) (I think that there is a length limit when trying to display the number using a function...).
  • When an asterisk (*) is used in a view, Oracle in the background saves the view definition with all the single columns and not just an asterisk. Because of this, when there the columns are changed in the base tables, the view should be recreated.
  • Subqueries that define views cannot contain the "FOR UPDATE" clause and shouldn't contain "CURRVAL" or "NEXTVAL".

Creating invalid views (pag 393):

  • "create FORCE view ste_view as select blabla....". This can be done if the view contains errors, but it still has to be created - its status will be INVALID if it contains any errors. Without the force option, the view won't be created.
  • An invalid view that needs to be recompiled, will be automatically recompiled once the view is accessed.
  • A view will become invalid if any column in the base table is altered.

Creating read-only views (pag 394):

  • "create view ste_view as select * from ste_tbl WITH READ ONLY".
  • "WITH READ ONLY" does not allow any DML operations on the view.

Creating constraints on views (pag. 395):

  • E.g.: "create view ste_view (name , address, id UNIQUE DISABLE NOVALIDATE) as select name, address, id_num from ste_tbl"
  • Constraints on views must always contain the "DISABLE NOVALIDATE" clause.
  • Constraints on views are not enforced - they are declarative constraints - they need to be defined on the base tables in order to be enforced.
  • The following constraints can be defined on a view: Unique, primary key, foreign key. They are not enforced.

Modifying views (pag. 395):

  • "ALTER VIEW" can be used to compile (views are anyway automatically recompiled when they're accessed, but you can force the recompilation using this statement) an invalid view or add/drop constraints.
    • "ALTER VIEW ste_view COMPILE".
    • If columns are added to the base tables of the view and the code of the view is "select * from ste_tbl", a recompilation WILl NOT ADD THE NEW COLUMN (because Oracle expands the * to all the columns). The view will need to be recreated with "CREATE OR REPLACE VIEW ...".
    • "ALTER VIEW ste_view DROP CONSTRAINT ste_view_cons1"
    • When the view is recompiled, the dependent objects become invalid.
  • "CREATE OR REPLACE VIEW" creates the view if it does not exist yet, or recreates if it exists already.
    • If "CREATE OR REPLACE VIEW" is used instead of dropping and recreating the view, the privileges granted on the view are preserved (privileges and user). The dependent stored views and programs become invalid.
  • Statements used to modify the DEFINITION of the view:
    • Create or replace view
    • Create or replace force view

Dropping views (pag 397):

  • "DROP VIEW ste_view"
  • Views and stored programs that refer to the view become invalid.

DML operations on simple views (pag 400):

  • DML operations on a view cannot be performed if the view contains the following:
    • DISTINCT
    • GROUP BY
    • START WITH
    • CONNECT BY
    • ROWNUM
    • UNION, UNION ALL, INTERSECT, MINUS (set operators)
    • A subquery in the select clause
  • If a view is defined as "create view ste_vw as select deptid from ps_dept_tbl where deptid > '0505001'" and I execute the statement "insert into ste_vw values ('0404001')", the statement WILL WORK.
    In order to force a check (create a constraint check) for DML operations on a view, specify "WITH CHECK OPTION" clause: "create view ste_vw as select deptid from ps_dept_tbl where deptid > '0505001' WITH CHECK OPTION [CONSTRAINT ste_cons]".
    The forced view check has a "V" in the CONSTRAINT_TYPE of the USER_CONSTRAINTS dictionary view.

DML operations on join views (pag 403):

  • A join view is a view with more than 1 base table in the top-level of the from clause.
  • It is not possible to update columns that join the base tables, if the "WITH CHECK OPTION" clause is on.
  • Key-preserved tables => see pag 404.
  • Which operations can be performed on which columns (of tables and views)? Check DBA(/USER)_UPDATABLE_COLUMNS.

Inline views (pag. 406):

  • A subquery enclosed in brackets in the FROM clause is called an inline view: "select a.dt from (select sysdate dt from dual) a".
  • The subquery in the FROM clause CAN contain an "ORDER BY". (this doesn't work in the WHERE clause of the main statement)

Top-'N' analysis (pag. 409):

  • The following sql returns the five biggest OPRIDs: "select oprid from (select * from psoprdefn order by oprid desc) where rownum <= 5". Oracle recognizes this and does not sort the whole subquery.

Chapter 9: Other DB objects (pag. 421)

Sequences:

  • They exist only in the data dictionary.
  • Check page 423 for the syntax. "CREATE SEQUENCE ste_seq..."
  • The following options can be used:
    • START WITH: the first number that will be generated. Default is 1 for ascending sequences - for descending the default is -1.
    • INCREMENT BY: amount that will be incremented (x) or decremented (-x) each time. Default is 1.
    • MINVALUE: lowest number that will be generated. Default is NOMINVALUE.
    • MAXVALUE: highest number. Default is NOMAXVALUE for ascending sequences and -1 for descending sequences..
    • NO / CYCLE: says if everything should start again after reaching the limit. Default is NOCYCLE (error will be generated if limit + 1 is reached).
    • NO / CACHE: NOCACHE updates the data dictionary each time a number is generated (performance is bad). CACHE defines the block of sequence numbers held in memory (default is 20) (fast - update is done once every 20 times with a "+20" operation). The default is CACHE, which preallocates 20 values in the SGA.
  • When a sequence is created, "START WITH" must be equal or greater than MINVALUE.
  • "DROP SEQUENCE bla_seq" deletes the sequence.
  • "<seq_name>.NEXTVAL" increases the current number and selects it.
  • "<seq_name>.CURRVAL" selects the current number. If no number has been generated yet, CURRVAL will be undefined and an error will be returned (so, execute first a NEXTVAL)
    The statement "selectlast_number from all_sequences where sequence_name = 'ste_seq'" displays the greatest number in the cache.
  • A "ROLLBACK" doesn't restore the previous value of the sequence, as users shouldn't be waiting for each other.
  • (Pag. 428) Sequences cannot be altered directly. If nextval has to be re-set, use one of the following approaches:
  • Drop and recreate it (grants are lost and dependent objects are invalidated)
  • Select NEXTVAL from it until it reaches the desired value.
  • Alter the sequence by changing "INCREMENT BY" ("ALTER SEQUENCE ste_seq INCREMENT BY 12345"), select NEXTVAL, and re-set "INCREMENT BY" to the original value.

Synonyms (pag. 429):

  • It is an alias for a DB object.
  • Public synonym is available to everybody.
  • A synonym can be of a table, view, sequence, procedure, function or package. In the local database or via DB-link.
  • Synonyms do not become invalid if the objects they point to are dropped.
  • Synonyms can be created to objects that do not exist or the owner does not have privileges.
  • "CREATE [PUBLIC] SYNONYM <syn_name> FOR [schema.]<obj_name>[@db_link]"
  • "DROP [PUBLIC] SYNONYM <syn_name>". (if it's a public synonym, PUBLIC HAS to be specified)
  • If I create a private synonym called "sinonimo", somebody else can execute a statement like "select * from ste.sinonimo". It behaves as a normal object. Anyone who has access to the original object can access "ste.sinonimo".
  • This is the order of how Oracle searches for an object (pag. 432):
  1. Search for an object owned by the current user.
  2. Search for a private synonym owned by the current user.
  3. Search for a public synonym.

DB-Links (pag. 433):

  • "CREATE [SHARED] [PUBLIC] DATABASE LINK <dblink_name> [CONNECT TO <usrname> IDENTIFIED BY <pwd>] USING <'tns_name'>".
  • If "connect to" is specified, the password is stored unencrypted in the data dictionary (DD-view USER_DB_LINKS or table SYS.LINK$). It it is not specified, the user will be prompted for a password.
  • A private DB-link is not available to other users.

Create and manage indexes (pag 434):

  • Usually Oracle retrieves rows from a table by
    • ROWID
    • Full Table Scan
  • If all the columns of the sql are contained in an index, Oracle does not need to access the table, but just the index.
  • Indexes improve performance for data retrieval, but degrade performance for DML operations.
  • B-Tree indexes:
    • They're the most common used.
    • They can be unique or nonunique and simple (1 column) or concatenated (multiple columns).
    • Good performance with high-cardinality columns.
    • Good in order to retrieve a small number of rows (<10% of the table. If more, than FTS).
    • Based on binary trees which contains branch blocks and leaves blocks.
      • Branch blocks contain the key (index columns) and an address to other index block.
      • Leaf blocks contain the key and the rowid(s) of the table.
      • Leaf blocks are doubly linked list, so that a range scan can be done in both directions.
    • If a leading column in the index is not used in the sql, but later columns are, the later columns cannot be used during the index scan (io non penso pero' - mah).
  • Bitmap indexes (pag. 436):
    • They're primarily used for static data and decision-support systems (because they don't support ro-level locking).
    • They can be simple (1 column) or concatenated (multiple columns). Actually they're almost always simple (best performance).
    • Good performance with low- to medium cardinality columns.
    • Multiple bitmap indexes can be combined with AND and OR conditions.
    • The bitmaps are stored in the leaf blocks (probably id doesn't have branch blocks).
    • Each line has a bitmap which may contain TRUE, FALSE or NULL.
    • Best thing is to create a bunch of simple bitmap indexes. This way if the query changes, the indexes can still be used.

Call stored programs (pag. 439):

  • They can be written in PL/SQL or Java.
  • They can be saved as procedures, functions, packages or triggers. They may contain procedural code with loops, cursors and conditional branching.
  • Procedures and Functions:
    • They are "named programs".
    • They're stored in the database.
    • They both take 0 or more parameters.
    • Functions return always a value (Java methods that return values are called as functions).
    • Procedures return 0 or more values (through their parameter list) (void Java methods are called as procedures).
    • Procedures are called from sqlplus with an "exec" in front of it. E.g.:
      • "exec dbms_stats.gather_table_stats(<parm1>, <parm2>, ...)
    • Functions are called anywhere an expression is valid. E.g.:
      • in an assignment: "marco : = open_orders(sysdate, 30);"
      • in an expression: "if (open_orders(sysdate, 30) < 500) then..."
      • in a default value assignment: "declare marco NUMBER default open_orders(sysdate, 30)...."
      • in a sql: "select * from ste_tbl where open_orders(sysdate, 30) < 500"
      • in the parms of another program: "process_vendor(vendor_id, open_orders(vendor=>vendor_id))"
    • Passing parameters:
      • Two ways of passing parameters:
        • Positional notation
          • E.g.: "dbms_utility.analyze_schema('HR', 'ESTIMATE', NULL, 10)"
        • Named notation
          • E.g.: "dbms_utility.analyze_schema(schema=>'SCOTT', estimate_percent=>10, method=>'ESTIMATE'"). Note that I did not need to specify this time the NULL value and that the order can be different.
          • The names of the parameters are contained in the package specification.
  • Packages
    • They're containers of procedures, functions and/or data structures.
    • Packages have:
      • a package specification:
        • contains function + procedures headers, externally visible data structures.
      • a package body:
        • contains the declaration, executable and exception sections of all the procedures and functions contained in it.
  • Differences between packaged and nonpackaged programs (pag 442):
    • Package data:
      • persists for the duration of the session of the user and accross the commits in the session.
      • granting an execute privilege on a package grants it for everything contained in it (functions, procedures, ...). It's not possible to grant access to only one thing in the package.
      • they support "overloading" (multiple programs with same name - which one to be called depends on parms nbr and type)

Chapter 10: Security (pag. 455)

  • "CREATE USER ..." creates a new user.
  • Authentication can be done in three ways:
    • DB authentication (pag 457): userid and password are ok for that DB
      • It is the default type of account.
      • "CREATE USER marco IDENTIFIED BY ciaobao1"
      • The keyword "identified by" tells Oracle that this is a DB-authenticated type of account.
    • External authentication (pag 457): userid is ok for that DB. The password is checked by the OS or the network.
      • These kind of accounts are sometimes called OPS$. This because the standard prefix for such userids is OPS$ (configured in the init.ora file as parameter "os_authent_prefix"). E.g.:
        "CREATE USER ops$marco IDENTIFIED EXTERNALLY"
      • The parameter os_authent_prefix is frequently set to blank. This way, an account creation would look like this:
        "CREATE USER marco IDENTIFIED EXTERNALLY".
      • These types of accounts are used usually in cron and batch jobs, so that passwords don't need to be saved somewhere in the system.
    • Global authentication (pag 457)): userid is ok for that DB. The password is checked by the "Oracle Security Service"
      • In this case a password is checked by a "X.509" enterprise directory service. This service is most common in big organizations with single sign-on systems.
      • "CREATE USER marco IDENTIFIED GLOBALLY AS 'CN=marco, OU=KLFA11, 0=CreditSuisse, C=CH'"
  • Assign attributes to accounts (pag 458):
    • Password: can be changed with "ALTER USER marco IDENTIFIED BY ciaobao1"
    • Default tablespaces: the default is the SYSTEM tablespace.
      "CREATE USER marco IDENTIFIED BY ciaobao1 DEFAULT TABLESPACE our_tables"
      "ALTER USER marco DEFAULT TABLESPACE our_tables"
    • Temporary tablespace: the default is the SYSTEM tablespace.
      "CREATE USER marco IDENTIFIED BY ciaobao1 TEMPORARY TABLESPACE temp".
      "ALTER USER marco TEMPORARY TABLESPACE temp"
      To change it for all the users: "ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp"
    • It is not possible yet to set default tablespaces for indexes.
    • Tablespace quotas:
      • Apparently no default value?
      • The amount can be specified in bytes (e.g. 1024), in Kilobytes (1K) and in Megabytes (1M).
      • "CREATE USER marco IDENTIFIED BY ciaobao1
        DEFAULT TABLESPACE user
        QUOTA UNLIMITED ON user
        QUOTA 20M ON indexes"
      • "ALTER USER marco QUOTA 2500K ON indexes" (by default the quota is measured in bytes)
    • Profile (pag. 459) => see pag 483 for further details:
      • The default profile allows unlimited resources.
      • This option needs to be activated as well in the file "init.ora": "resource_limit true"
      • Limits the resource usage (resources may be connect time, idle time, logical reads per session, failed login attempts, password verification function, ...). It is possible to limit the number of concurrent sessions. It is NOT possible to limit the number of physical reads, as it depends on how fit the cache is.
      • "ALTER PROFILE default LIMIT idle_time 5". Sets a maximum idle time of 5 minutes for the people who have a default profile.
      • "CREATE USER marco IDENTIFIED BY ciaobao1 PROFILE superuser"
      • "ALTER USER marco PROFILE superuser
      • alter profile oe limit failed_logon_attempts 3;
      • password_grace_time is the number of days after that the password expires, that the user is reminded to change it, but is still able to log into the system.
      • Password_life_time are the number of days of duration of the password � password_grace_time is not included here.
      • Total = password_life_time + password_grace_time.
    • Roles (pag. 460) => see pag 463 for how to create roles:
      • "ALTER USER marco DEFAULT ROLE ALL EXCEPT readonlyuser"
      • Roles cannot be enabled when the user is created.
      • Not possible to know who granted dba role to somebody.
    • Passwords and accounts (pag460 461)
      • Passwords can be forced to expire:
        • ALTER USER marco IDENTIFIED BY pwdpwd
        • ALTER USER marco PASSWORD EXPIRE
        • CREATE USER marco IDENTIFIED BY welcome PASSWORD EXPIRE
      • An account can be locked per example, when that user owns tables, but nobody is supposed to login as that user.
        • ALTER USER marco ACCOUNT LOCK
        • To unlock it: ALTER USER marco ACCOUNT UNLOCK
      • It is possible to check if the terms of the licenses are being respected. The parameters "license_max_users", "license_sessions_warning" and "license_max_sessions" of the init.ora file control them.
        • license_max_users
          It is compared to the result of the query "select count(*) from dba_users". System users (SYS, SYSTEM, and so on) are counted. If the nbr of users is higher than this parameter, the next time the db is started, an alert log is written.
        • license_sessions_warning
          It is the maximum number of concurrent users. If the value is exceeded, a warning is written in the alert log file (background_dump_dest\alert.log)
        • license_max_sessions
          If this value is exceeded, two things happen:
          If the user that wants to login is a dba, he's still allowded to login, but he will get an error message.
          Otherwise, the logon fails with an error message and a warning in the alert log file.
     
  • Roles: creating and using them (pag. 463).
    • Privileges can be granted to a role. A role can be granted to another role or a user. Roles are used only to administer privileges.
    • By default a role doesn't have a password.
    • CREATE ROLE mioruolo [IDENTIFIED <BY mypassword / EXTERNALLY / GLOBALLY> ]
    • If a role is created with a password (blabla... IDENTIFIED BY ....), the role is disabled by default.
    • To enable a role: "SET ROLE rolename IDENTIFIED BY mypassword" (pag 464). Don't know how to disable a role.
  • Privileges: granting and revoking them (pag 464)
    • They allow a user to access or execute sombody else's objects or programs. As well to perform system-level operations like create and drop objects.
    • Privileges can be granted to a user by granting them to the user PUBLIC or to a role.
    • There are tree types of privileges:
      • Object privileges
        Access tables, functions, libraries, ...
      • System privileges
        Connecting to the DB, alter user session, create tables, create users, ...
        Query the data dictionary view SYSTEM_PRIVILEGE_MAP in order to see all the system privileges.
      • Role privileges
        Privilege that the user has because he has the role.
    • Table of objects and privileges that can be assigned to them (pag 465):
      alter, delete, execute, index, insert, read, reference, select, update
    • List of system privileges (pag 468):
      • cluster, database, index, procedure, profile, role, rollback segment, sequence, session, synonym, table, tablespace, user, view.
      • Special system privileges: SYSDBA and SYSOPER (pag 473)
      • Other system privileges (pag 473): analyze any, audit any, comment any, grant any privilege, grant any role.
    • Assign privileges:
      • GRANT <role / system privilege> TO <user / role / PUBLIC> [WITH ADMIN OPTION]
      • GRANT <object privilege> TO <user / role / PUBLIC> [WITH GRANT OPTION]
      • GRANT ALL TO <user / role / PUBLIC> [WITH GRANT OPTION]. The "ALL" contains the following privileges:select, insert, update, delete, alter, index and reference. "TRUNCATE" is not a table privilege and is not included in the ALL.
      • Once a privilege is granted, there is no need for a logoff+login. It is active immediately.
      • The "WITH GRANT OPTION" makes it possible for the receiver of the access, to be able to grant that access to anybody else. If, after granting the access to somebody else, the user who granted it is dropped, the receiver loses the access. This is possible because in the data dictionary both the receiver and the granter are kept. Page 474.
      • Granted access records as well from whom the access was given. If an access to the same object is granted to a user from two different users, both of them have to revoke the access � if just the first user revokes it, the user still has access (because of the second user).
      • The "REFERENCE" privilege can be granted only to a user.
      • There is no REVOKE ANY system privilege.
    • Assigning system + role privileges (WITH ADMIN OPTION):
      • In this case, the granter of the privilege (all roles) is not recorded. This means that once that the receiver got the access, he keeps it even if the granter is dropped. If anybody revokes this privilege, the privilege is gone.
    • Assigning role privileges (pag 477):
      • Any combination of system, object and role privileges can be granted to a role.
      • Read about stored sql on page 477.
    • Revoking privileges:
      • REVOKE <role / system privilege / object privilege> from <user / role / PUBLIC>
      • Object privileges (e.g. update on a table) can be revoked only by the grantors.
      • If I want to revoke only the "WITH ADMIN OPTION" or the "WITH GRANT OPTION", the whole privilege must be dropped and reassigned. However, this might cause problems if the receiver already granted to somebody else an object privilege, as the access will be lost. A chain reaction is started.
    • Data dictionary views (pag 480):
      • DBA_TAB_PRIVS contains a list of the privileges granted.
      • ALL_TAB_PRIVS_MADE contains a list of the privileges on another user's procedure granted to somebody else.
      • List of data dictionary views: pag 480 + 481 + 482

        A grant to a single of a table is assigned as follows: "GRANT UPDATE (name, age) on stetable to bruna".

Managing user groups with profiles
There are two categories of resource settings:

  • Password
    • These settings are always active.
  • Kernel
    • They're active only if they're explicitly enabled in the oracle configuration file (init.ora - resource_limit=TRUE) or dinamically (alter system set resource_limit=TRUE).

Create and alter profiles:

  • Each database starts with a profile called DEFAULT. It has all parameters set to "unlimited".
  • Syntax of "CREATE PROFILE" statement => page 483.
    • When a new profile is created, the parameters that are not explicitly limited, are inherited from the DEFAULT profile.
    • The settings of the DEFAULT profile can be changed using the ALTER statement.
    • Not specifying a parameter or specify it with the DEFAULT value, has the same effect.
    • UNLIMITED means that no limit is used for that resource.
  • Syntax of "ALTER PROFILE" statement => page 484.
    • If the DEFAULT profile is altered, all other profiles that use the parameters of the DEFAULT profile will inherit the change.
    • UNLIMITED means that no limit is used for that resource.
      Kernel resource settings: page 485
      Password resource settings: page 488

More:

  • Generate hash values in Oracle >= 10: select ora_hash('hello world') from dual