DBA programming component

1. DBA programming component

2. SQL*Plus Scripting 1 • Plus points: • Same SQL language as in interactive mode • Can test programs interactively first • Includes predefined (built-in) functions • Fast development possible • Rapid prototyping • Get results and feedback quickly • No cumbersome environment • Variable inputs • Parameters, substitution, accept

3. SQL*Plus Scripting 2 • Plus points (continued) • Can have multiple script files • Each file created by simple text editor • Can have master script file • calling others in sequence • Or can nest script files more generally • scripts can call other scripts (default file type sql) • @S6 • start S6

4. SQL*Plus Scripting 3 • Problems with scripts: • interpreted each time they are run • not verified and compiled • optimisation of SQL code done each run time • poor performance • no control environment • procedural actions lacking (case, if, while, for, repeat) • no error handling • resulting in outright failures or ignoring of messages

5. SQL*Plus Scripting 4 • Problems with scripts (continued): • Lack of control by business (via DBA -- DataBase Administrator) • How do we permit scripts for usage by particular people? • Can anyone write a script to do anything they like? • If people write scripts themselves to handle business rules • how do we know they’ve implemented the rules in the same way?

6. Production Environment • Encourages: • business rules in one place • application of rules then controlled by DBA • users need permission to apply rules • permission is granted/revoked by DBA • Discourages: • duplicated, potentially inconsistent, rules • access by users to anything they like

7. SQL Procedure • An important technique • Part of PL/SQL in Oracle • Procedural Language/Structured Query Language • Part of the SQL standard • approximate portability from one system to another • Techniques are available for: • procedural control (case, if, while, …) • parameterised input/output • security

8. Oracle PL/SQL • Not available in Oracle 8i Lite • Available in Oracle 9i at Northumbria • Available in Oracle 9i Personal Edition for Windows (XP/NT/2000/98) and linux. • http://otn.oracle.com/software/products/oracle9i/index.html • c1.4Gb download -- needs Broadband -- 3 CDs • Useful guide to PL/SQL: • http://www-db.stanford.edu/~ullman/fcdb/oracle/or-plsql.html • Using Oracle PL/SQL -- Jeffrey Ullman, Stanford University

9. Procedures are First-class Database Objects • Procedures are held in database tables • under the control of the database system • in the data dictionary • select object_type, object_name • from user_objects • where object_type = 'PROCEDURE'; • user_objects is data dictionary table maintained by Oracle • object_type is attribute of table user_objects holding value ‘PROCEDURE’ (upper case) for procedures • other values for object_type include ‘TABLE’, ‘VIEW’ • object_name is user assigned name for object e.g. ‘PATIENT’

10. Procedures aid Security • Privileges on Tables: • Select • query the table with a select statement. • Insert • add new rows to the table with insert statement. • Update • update rows in the table with update statement. • Delete • delete rows from the table with delete statement. • References • create a constraint that refers to the table. • Alter • change the table definition with the alter table statement. • Index • create an index on the table with the create index statement

11. Privileges on Tables • SQL statement -- issued by DBA: • GRANT select, insert, update, delete ON patient TO cgnr2; • ‘no grants to cgnr3 for table access’ • allows user cgnr2 to issue SQL commands: • beginning with SELECT, INSERT, UPDATE, DELETE on table patient • but this user cannot issue SQL commands • beginning with REFERENCES, ALTER, INDEX on table patient • User cgnr3 does not know that table patient exists

12. Privileges on Procedures • The SQL statement • GRANT execute ON add_patient TO cgnr3; • allows user cgnr3 to execute the procedure called add_patient • So user cgnr3 can add patients • presumably the task of add_patient • but cannot do any other activity on the patient table • including SELECT • So procedures give security based on tasks • powerful task-based security system

13. Writing DBA scripts7 SQL and SQLplus tricks that work wonders! • Chr(10) inserts a newline character • Set linesize 600 • WHENEVER OSERROR EXIT <somevalue different for 0> • Use UNIONs to fetch commands from independent queries • Use dummy unprinted columns for ordering • Use the following • Set pagesize 0 (removes page breaks) • Headingsset feedback off (removes number of lines) • Selectedset recsep off (removes unwanted blank lines) • Feel free to tweek settings to get a script to work even if the defaults are ok.

14. What really happens when you start oracle? • Create an instance (recreate if present) • Physical memory is allocated to System Global Area (SGA) • Database is mounted • Files are opened (1 is mandatory but more advisable) based on control file settings V$DATAFILE shows db files • Db is opened • Bootstrap segment is loaded into SGA showing where to find DD tables

15. Data dictionary views and control files • Data dictionary views: • Use underlying tables owned by SYS • Can be queried like other views • Number in the hundreds Select * from dictionary Order by table_name;

16. Important DD components Data dictionary views: • Cannot be updated • Are documented on otn.oracle.com • Have a prefix that defines the scope: • USER: Views focused on a user’s own objects • ALL: Views about objects a user either owns or can query • DBA: Views for DBAs only, showing information about all objects in the database

17. Dynamic DD views Dynamic performance views are similar to data dictionary views except: • The contents of the views change with database activity • They are used mostly for tuning and monitoring • Dynamic performance views are prefixed with: • V$: Standard dynamic performance view • GV$: View spanning multiple instances

18. Useful views Some useful views include: • V$SYSSTAT: statistical details about all sessions running on the database • V$SQL: Details on individual SQL statements running on the database • V$SESSTAT: Information by session to help identify memory usage

19. Physical Storage Management • Oracle databases have a number of files assigned – a pool of free space available known as table spaces • ONLY table spaces are referenced in SQL commands not file • Objects likely to be retrieved from disk at the same time should be on different disks (parallelisation of I/O) – tables held separately from indexes

20. Cont .. • Special cases • SYSTEM – always created hold dictionary tables. HALLOWED GROUND!!!!!! • also need TEMP and ROLLBACK or UNDO

21. Segments, Extents and Blocks • Segment is a physical implementation of object – table or index etc • Made up from extents – contiguous blocks. • Extents are expressed as a number of blocks – elementary storage unit based on OS block size • Good DBAs like segments to be composed of small numbers of extents. Looks at DBA_SEGMENTS • STORAGE clause determines tables characteristics NEXT parameter determines size of next extent DBA_EXTENTS table PCTINCREASE parameter

22. Frequent errors • No more space in table space • Not enough contiguous space in table space (DBA_FREE_SPACE shows enough but not in a big chuck) try defragging • Max number of extents has been reached by segment

23. User management – the theory • Users means oracle accounts • Users are created and identified by a password • Assigned a default table space and temp space and may be given quotas • Need roles to do anything – connect & resource basic ones • May need profiles

24. User management – the practice • Only 4 types of user • DBA • Application owner • Advanced user • Rank-and-file users We will be doing it today!

25. System privileges • System privileges allow a user to manage some part of the database system • Object privileges allow a user to work with an object. • SYSDBA and SYSOPER are system privileges that allow a user to start up and shut down the database, as well as other high-level tasks • The CREATE SESSION system privilege is needed to log onto the database

26. Typical privileges • Typical object privileges for a table include SELECT, INSERT, UPDATE, and DELETE • The GRANT and REVOKE commands are used for both system and object privileges • Use WITH ADMIN OPTION when granting system privileges to allow the user to grant that privilege to others

27. Cont… • A grant made to PUBLIC gives all users the privilege • Revoked system privileges do not cascade to other users • Use WITH GRANT OPTION when granting object privileges to allow the user to grant that privilege to others • Revoked object privileges cascade to other users

28. Cont… • Object privileges can be granted on columns • The owner of a table can grant object privileges on that table • The grantor grants the privilege and the grantee receives the privilege • Querying an object without privileges to query causes an error stating that the object does not exist

29. Auditing • Statement auditing is the monitoring of activity on a particular type of statement, such as SELECT • Privilege auditing audits any command that is authorized by the privilege, such as CREATE TABLE • Object auditing generates audit trail records as soon as the object is used, such as with SELECT or DELETE statements

30. Cont .. • The SYS.AUD$ table holds auditing records unless the AUDIT_TRAIL initialization parameter is set to “OS” • AUDIT_SYS_OPERATIONS is an initialization parameter that, when set to “TRUE,” causes Oracle9i to audit all activity by SYS or users with SYSDBA • privileges BY ACCESS or BY SESSION tell Oracle9i whether to write a record for each occurrence of an audited event or a summary record for the session

31. Cont .. • The following clauses limit the writing of audit trail records: • WHENEVER SUCCESSFUL and WHENEVER NOT SUCCESSFUL • AUDIT_TRAIL is a static parameter, so you must restart the database after changing it • A group of data dictionary views shows audit trail records for each type of auditing • Use the NOAUDIT command to stop specific auditing activities

32. The mythical rollback segment • Temporarily saves previous values when some update go on • 2 purposes • Enables cancellation of actions (ROLLBACK statement) • Concurrent sessions read the data when they access changed tables prior to a commit. • Same as other segments

33. Getting the client and server on talking terms • Client connection to server needs a listener to broker the connection (not the named listener but the tnslsnr) • Only bothered with the instance name (net8 refers to services) • Instance name is not enough, needs to know SID and Oracle home where oracle is installed as you may have multiple versions of oracle on one machine (SID_LIST section of the listener.ora file) • Can use several listeners, useful if incompatible versions (7.1 & *.x) • Listeners created on installation but not always activated