======================================= | OCP Exam Notes for SQL/PLSQL Exam 1 | ======================================= Dictionary Views ================ USERS_ objects i own ALL_ object i own + granted objects DBA_ all database objects Blocks ====== Consists of fixed block header (at least 80 bytes) variable block header (at least 6 bytes) vaiable block 4 bytes = table dir 2 bytes = row dir * No rows/block INITRANS fixed block header = 57+23*INITRANS pre-allocs space for transactions accessing blocks rows default is 1 if not high enough, dynamically use avail free space to support block headers needs Tables ====== Columns Must start with A-Z only Can have mixed case & spaces via enclosed double-quotes, if used the must use from then on #,_,$ allowed Column Data char (max 255, right space padded, default 1 byte) Types varchar2 (size req, max 2000, variable size) long (one per table, 65535 max, char type) date (from 1/1/4712bc, dd-mon-yy std, julian) raw (binary, size req, 2000 max) long raw (hex, 2 gig max) rowid (internal, unqiue row id) number (p/s, precision and scale, p mand, s null def) decimal (see number) integer (same as number 38,0) smallint (see integer) float (same as number 38) float(b) (number with precision 1 to 126) real (same as number 63) Constraints can be enabled, disabled, dropped unique (auto indexed) primary key (auto indexed) foriegn key null not null (converted to a check) check (unlimited/table) default PCTFREE space to leave in each block for updates/inserts 10% default High updates, greater value. Infrequently updated, lower value. PCTUSED min level space used in a block 40% default block added to freelist if storage < pctused only applies to deletes set low if freq upd, inserts to high will increase freelist maintenance sum(pctused and pctfree) <= 100% INITRANS init active trans alloc'ed within each block ?? MAXTRANS max active trans that can update a block concurrently INIT and MAX together = # of active trans that can update a block concurrently CLUSTERS pctfree and pctused not applicable Indexes ======= General B-tree default, good for high cardinlity fields self balancing also bitmap indexes FREELIST GROUPS not applicable storage clause default ascending order sort NOSORT, doesnt sort index Unique or non-unique, p.key indexes use unique clause bitmap low cardinatlity pre 7.3.2.3.3 need specific events set for its use Columns max 16 per index >1, index only used if access at least 1st col of the composite index, eg. 1,2,3 then 1,2 or 1 Clusters ======== ? General === SQL is an interpreted language schema logical grouping of DB objects based ona user that owns the objects dual special table has 1 column, all null values null expression that represents nothing in set theory, represents an empty dataset NVL can be used to give a NULL variable or column a value select ; or / to end sql statement minimum req for a select is SELECT and FROM *, all columns tables can be given aliases that are not mandatory for use columns can be given aliases once renamed with an alias must use it for all subsequent references in the SQL alias defined 2 ways: or AS ||, concatenation operator order by default order is ascending can use ASC or DESC where clause selects out data based on a condition =,>,=>,<=,<>,~=,^=,like, soundex,between,in functions can be applied to columns there are single-row and group functions character functions:lpad,rpad,lower,upper,initcap,length,substr number functions: abs,ceil,floor,mod,round,sign,sqrt,trunc,vsize,sinh to_number list functions: (any datatype) greatest,least date functions: add_months,last_day,months_between,new_time conversion functions:to_char,to_number,to_date,to_multi_byte,to_single_byte chartorowid,rowidtochar,hextoraw,rawtohex,convert,translate group functions: avg,count,max,min,stddev,sum,varience operate on several rows at a time if a GROUP BY clause is used, you must list all non-grouped columns in the GROUP BY clause from left to right as they appear in the select HAVING clause used for group function comparisons nested subqueries, col name () to return a boolean from the subquery the ORDER BY clause can be used to order data for sub-queries select from where = (select ...) order by higher sub-query columns can be referenced in lower queries dates stored as an integer (julian date) nls_date_format, default date format for the session sql*plus uses a line buffer to store sql as it is entered buffer is called afiedt.buf only 1 buffer exists with 1 sql statement or pl/sql block list, lists contents of the sql-buffer , where n is a line in the buffer, sets the current buffer line we want c//, change a value in the current buffer line can execute buffer contents via / edit, you can edit the buffer via a text editor define_editor=, to change editor get , gets contents of a file into the buffer @ or start spool , direct SQL output to a file &, used to define a variable within a sql statement SET DEFINE , to change & to something else for the session DEFINE = , to create variables also UNDEFINE , remove variables for session ACCEPT PROMPT <'prompt string for user'>, to create variables Defined variables remain defined for the entire session unless undefined Relational DB tables,views,synonyms indexes, SQL processing mechanism triggers,integrity constraints priviliges,roles,profiles packages,procedures,functions normalisation, act of breaking down column data to place in tables where each column in the table is functionally dependent on only one primary key cardinality, referes to the uniqueness of a column or data ordinality, defines a meaning to a relationship. is it madatory or optional? and does one record in the tale correspond to one or many records in other tables?. 1:N, 0:N, 1:1, N:N tables DML === operator precendence 1) +, -, prior 2) *, / 3) =, || 4) NOT,AND,OR TRUE takes precendence with OR FALSE takes precendence with AND eg. TRUE OR NULL = TRUE (,) over-ride the above set operators union, union all, intersect,minus equal precedence comparisons =,!=,~=,<>,>,<,>=,<=,IN,NOT IN,ANY SOME, ALL, BETWEEN, EXISTS, LIKE, IS [NOT] NULL NULL's comparision to NULL = FALSE an expr to NULL = NULL, except for || which = '' displayed last in v7 ORDER-BY / where = NULL will return NULL Wildcards % and _ ESCAPE clause eg. ESCAPE 'z' where username like '%N\%P\%O%' escape \; N%P%O is the standard string it will look for Joins join is when data from one table is associated with data from another table according to a common column that appears in both tables (foreign key) equi-join (=) or inner-join cartesian (no where clause, if T1=10rows and T2-20rows then 200rows ret) outer-join self-join (join table to itself, req aliases, usually req 2 or more comparison operations) outer joins, places out table column lacking info, only in where clause cant be used with OR, IN or a sub-query min # of joins req to relate a set of tables is the #tablesinjoin - 1 Sub-queries returns 1 or more values, eg. ... IN or single in case of equality cant use order-by, group-by Functions single-row (scalar) - single value from each row queried aggregate (group) - single value for a group of rows psuedo-columns user user name of current session sysdate current date and time level rowid uniquely identifies a row rownum null literals expression containing a numeric or char value enclosed in single quotes dml insert update delete truncate is DDL as it does an implicit commit and produces no rollback views with-check-option, insertion/update/del commands must meet where clause of the view cant use the ORDER BY clause views cant be updated if has a join to 1 or more tables (complex view) uses set operator has group-by uses group function uses distinct select def ordering is ascending min req is SELECT-FROM columns can be aliased, once done must refer to the alias WITH READ ONLY clause specified for sub-queries, subq cant be updated no WHERE clause is a cartisian product HAVING, used with GROUP BY clause, cant have without used to restrict group columns NOWAIT, returns control to you if selected attempts to lock a row if not used waits until lock is released column aliases can only be directly used in ORDER-BY clause set operators union union all intersect minus SQL Functions all functions exception COUNT ignore NULL functions that dont return a null: CONCAT DECODE DUMP NVL REPLACE number functions: ABS CEIL COS COSH EXP ..etc.. character functions: CHR CONCAT INITCAP LTRIM ..etc.. also date/time, conversion, other group functions distinct all (return all values + duplicates) avg count max min stddev sum varience DATE formatting YYYY,YY year DD day fm??TH spell out "th" MM month MONTH spell out month DATE - DATE = Numeric Value of days between the both of them PL/SQL ====== pl/sql engine processes pl/sql blocks submitted by server manager pl/sql blocks, parsed into the procedural statements & sql statements parsed statements processed via the Procedural statement executor inside the engine Procedural statement executor - processes data local to the app, passes SQL statements as needed to the oracle 7 server SQL Statement Executor pl/sql blocks begin end; <> declare begin exception ??? then end; nested to any depth executed as one whole unit named blocks a stored pl/sql block is a named block procedures functions triggers variables multi-declarations not allowed, ie. mychar1, mychar2 char(1); constants change be changed unless redeclared in a subsequent block pragma PRAGMA EXECEPTION_INIT(, ); error-number defined as -904 (no leading zeros) remaps oracle errors into user-defined ones cursors cursor parameter variable dont have a size specified explicit cursor processed via OPEN, FETCH CLOSE or cursor loops OPEN parses cursor code FETCH processes cursor to get 1 row, inserted into record set CLOSE closes cursor, release memory cursor loop FOR rec_id IN cursor_id LOOP ---processing--- END LOOP; no OPEN, FETCH, CLOSE in cursor loop, done implicitly explicit cursor, named by the developer. cursor is implicit cursor, auto defined when any sql statement is issued unnamed address where SQL or PL/SQL statement is executed results in 2 fetches, 1 to get indication is row will be returned the other to get the row every sql statement exe'ed on the oracle DB is an implicit cursor, and any implicit cursor attrib can be used in conjunction with them implicit cursor attributes are %FOUND, %ISOPEN, %NOTFOUND, %ROWCOUNT exceptions 1 per block can create user defined ones, raised using RAISE command 3 basic types: pre-defined (oracle), user and pragmas user: EXCEPTION; raise ; pragma: pragma exception_init(, -); procedures IN, IN OUT parameters create [or replace] no size info req for variable types functions 1 return value only via RETURN functions used outside of its package may req its purity level defined via PRAGMA RESTRICT_REFERENCES(<..>) after definition anonoymous block un-named block loops simple loop: LOOP END LOOP; requires EXIT; or EXIT WHEN ; or WHEN EXIT; to exit while loop: stopped when condition reached, usually TRUE WHILE LOOP END LOOP; for loop: for number of iterations default asc can be reversed via FOR i IN REVERSE lower..higher-bound LOOP step interation is 1, must manually code for other cases can prematurely exit via EXIT's or EXIT WHEN's is required DML insert, delete, update SQL% can be used after any DML in pl/sql SQL%ROWCOUNT - # rows DML manipulated SQL%FOUND - DML ok SQL%NOTFOUND - DML failed SELECTS in can only return 1 value (unless via cursors or cursor loops for multiple rows) PL/SQL if not in cursor, req the INTO clause can use FOR UPDATE to lock rows selected for update or deletion COMMITS AND pl/sql is transaction based ROLLBACKS in commit, all pending transactions made permanent, ending current trans pl/sql rollback, all pending trans are removed, ending current trans savepoint, also used if req ALTER/DROP only alter compile allowed in pl/sql for packages, procedures, functions %TYPE pl/sql only used to give a variable the same type as a table column .%TYPE; %ROWTYPE pl/sql only create a composite datatype in which all the columns of a row in the table referenced are lumped together as a record. pl/sql only dec,decimal,real,double precision,int,smallint, positive numeric datatypes binary_integer, character, varchar, boolean, table/record constants CONSTANT := Procedure builder ================= file types pls, pl/sql source pld, above + interpreter codes pll, pl/sql library log, interpreter log file DB Design and Modelling ======================= 5 steps strategy & analysis design build & document transition prod normalisation 1st remove repeated attribs or groups of attribs to their own entity 2nd remove attribs dependent on only part of the UID 3rd remove attribs dependent on attribs not part of the UID (all attribs in an entity must relate directly to the UID only) P.Keys UID (unique identifier) composite primary key contatination primary key Users and Roles ================ Dictionary DBA_TAB_PRIVS tables, views, sequences Views ALL_DBA_PRIVS ALL_TAB_PRIVS USER_TAB_PRIVS USER_TAB_PRIVS_RECD tab privs granted to me USER_TAB_PRIVS_MADE privs grants to other users DBA_COL_PRIVS column level obj privs granted to users USER_COL_PRIVS_RECD column level privs granted to me ALL_COL_PRIVS_RECD granted to me or PUBLIC USER_TAB_PRIVS_RECD USER_COL_PRIVS_MADE ALl_COL_PRIVS_MADE DBA_ROLES DBA_ROLE_PRIVS ROLE_ROLE_PRIVS ROLE_SYS_PRIVS ROLE_TAB_PRIVS SESSION_ROLES what roles are current in my session SESSION_PRIVS V$PWFILE_USERS list users with SYSDBA and SYSOPER privs Users need create user priv can set default ts, temp ts and the quota on the ts's also associate a profile for the user to control system resource usage passwords stored encrypted IDENTIFIED EXTERNALLY, OS validates the users authentication default ts is SYSTEM unless specified otherwise default temp ts is also SYSTEM creating the user and giving them a default ts doesnt grant them access to the ts you can give the user persmission to create objects in tables via the QUOTA clause The RESOURCE role auto grants unlimited tablespace OS_AUTHENT_PREFIX, default of OPS$ for externally identified users Dict Views USER_USERS, info on the user itself DBA_USERS, info on all users DBA_TS_QUOTAS USER_TS_QUOTAS the column max_bytes, max_blocks in the TS_QUOTA tables = -1 = unlimited quota Alter user alter most things, not the username req alter user priv user can alter his/her own PW, not for identified externally users pw change takes effect next time user logs in changing default TS, all future objects created in new TS also used to set the DEFAULT ROLE for a user when you create a user, they get ALL, which means that all sybsequent roles given to the user will be regard as the users 'default' roles SYS and SYSTEM will inherit all roles created as their default roles unless you use DEFAULT ROLE NONE for these users. You can not set a users default role via create user command, done with alter DEFAULT ROLE ALL, (all roles granted to a user are default active) DEFAULT ROLE ALL EXCEPT A default role is one that is automatically enabled when a user creates a session Use SET ROLE to move between roles, SET ROLES ALL (doesnt work if any have a pw) only when OS_ROLES=true Special Roles Install oracle executables: OSOPER OSDBA created at the OS level when oracle is installed cant be granted needed to perform Db operations whenDB is not mounted (data dict not accessible) used by CONNECT INTERNAL The OSOPER can: startup, shutdown, alter open/mount, alter db back controlfile alter tablespace begin/end backup, archive log, recover The OSDBA can: has all osoper, but with admin option used to create the db & time based recovery both have restricted session priv SYSOPER same as OSOPER. When you create the DB: CONNECT RESOURCE DBA When you execute catalog.sql IMP_FULL_DATABASE EXP_FULL_DATABASE Drop user drop user sys priv cascade, removes all associated objs to the user DDL command, cant be rolled back Privilges system privs - with admin option over 80 types the ANY priv doesnt give access to data dictionary obj's all DDL, also ANY privs are system type privs object privs - with grant option tables, views, sequences, packages, prodecures, functions DML (i,d,u,exe, references, index) index, create index on .... references, create FK constraints alter, object admin option - can revoke from yourself - grantee can grant/revoke the sys priv or role to or from any user or other role in the db - can also add to it the with admin option - can alter or drop the role grant option - to grant an obj prov you must own the obj or have grant option - grantee can grant the obj priv to any user or role - may add the grant option as well - cant use GRANT OPTION when assigning privs to roles Revoke users with GRANT ANY ROLE can revoke any role if you have admin or grant options, can revoke priv from any user/role as req cant revoke WITH ADMIN OPTION, must revoke the whole priv and grant without option no cascading effects when revoking system privs related to DDL operations regardless if the admin option is used system privs related to DML can cascade. Grant can grant obj and system privs to users and roles may include admin or grant options can grant obj priv access to selected columns ie. grant insert(emp_no, dept_no) on ckempste; ALL option can be used to grant all privs for an option to a user system and obj privs cant be mixed in the one GRANT command Roles groups several priv's and roles into one req create role priv use grant to grant new privs to the role or revoke to remove them roles can be allocated passwords roles info is kept in the data dictionary, not user schema to alter authorisation on a role, need alter any role priv or be granted the role with the Admin Option roles can be identified externally MAX_ENABLED_ROLES controls max number of default roles possible for the user all roles/changes to, have immediate effect special type of role called PUBLIC, all users have access os_roles=true to enable OS authent roles remote_os_roles=true Storage clauses =============== pctfree pctused pctincrease initrans maxtrans Alter/Drop and other commands ============================= ddl alter,truncate,drop,comment they do implicit commits, therefore automatically end transactions alter table change col types, lengths, change constraints, storage cant alter col name or drop a col, alter col to not not if there are nulls change to non-compatible data type sequences can alter MAXVALUE is not already past it INCREMENT BY, MINVALUE, CYCLE, CACHE must drop if start with is to differ nextval, currval comment add comments to tables,view, columns USER_/ALL_/DBA_TAB_COMMENTS USER_/ALL_/DBA_COL_COMMENTS drop 130 Other ===== Data Relationships primary key, functional dependency, foreign key Integrity constraints primary key,foriegn key, not not, check, unique foreign-key,check,unique constraints not applied if NULL data cant reference pseudo columns cant include selects p.keys, no null values f.keys, ref columns in other tabs contain NULL or values ref in other tabs unique, all unique values or null check, new check constraints only applied to new data indexes auto created for p.key and unique constraints to enforce uniqueness named SYSXXXXXX if not explicitly named, along with constraints DBA_CONS_COLUMNS, DBA_IND_COLUMNS data dictionary SYS, only user allowed to update data dict tables DD views are broken into 3 categroies, ALL_,USER_,DBA_ PUBLIC user, represents the access privs every user has insert insert into [()] [values,as] () update update set are mandatory. transactions statement-level read consistency, allows inserts etc with rollback later or to return to what the data was before the dml locks or set transaction [] explicitly starts the transaction commit or rollback ends the transaction savepoint, defines a "midpoint" for the trans to allow rollbacks locks row-level or table-level updates, req a row-exclusive lock, whilst updating no other user can view or change the data select for update, reqa share row lock, can view but cant change until released views simple - data from 1 table only complex - joins or multiple tables with check option, updates can only be made on data the view returns data not inserted into simple views that use rowid,currval,nextval, rownum or select out data using these columns view query can not use the order by clause cant do inserts, update,deletes if: uses set operators, group functions uses group by, connect by, start with uses distinct if a complex view, look at USER_UPDATABLE_COLUMNS cardinality uniqueness of values of a column high uniqueness = high cardinality