oracle数据库学习

1、Top 10 New Features in Oracle 9i,Presented By:Mike Freeney OCP DBA OracleM North Carolina Oracle Users Group 3/20/2002,2,#10. Automatic Undo Management,Automatic Undo Management (AUM) is a feature that relieves the DBA of creating, sizing and monitoring the rollback segments in the database. Rollbac

2、k segments are created, deleted, monitored and sized automatically by the instance. Rollback data is managed by means of an undo tablespace. CREATE UNDO TABLESPACE “UNDO_TBS” DATAFILE /u01/oradata/freeney9/undo_tbs01.ora SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 700M,3,#10. Automatic Undo Management,

3、Key initialization parameters: UNDO_MANAGEMENT (MANUAL or AUTO) Specifies whether or not to use AUM. Default = MANUAL UNDO_TABLESPACE (valid tablespace) Specifies which undo tablespace to use. UNDO_RETENTION (in seconds default=30) Specifies how long to keep committed undo. UNDO_SUPPRESS_ERRORS (TRU

4、E or FALSE) Specifies whether or not to return an exception when “SET TRANSACTION USE ROLLBACK SEGMENT” is issued. Default = TRUE,4,#10. Automatic Undo Management,DBA_ROLLBACK_SEGS, V$TRANSACTION, V$ROLLSTAT, are V$ROLLNAME are still available. DBA_UNDO_EXTENTS shows when each extent in the undo tab

5、lespace was committed. V$UNDOSTAT shows the undo usage for the last 24 hours. Each row records a ten minute interval defined by START_TIME and END_TIME. The key field is UNDO_BLOCKS.,5,#9. Flashback Query,Flashback Query allows users to see a consistent view of the database at a point in time in the

6、 past. This view of the data is read-only. This view of the data is re-created by undo and is only available if the undo blocks are still available. PL/SQL cursors opened in flashback mode are available for DML after flashback mode is disabled. Flashback Query is also supported by EXP.,6,#9. Flashba

7、ck Query,EXEC DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(524545); EXEC DBMS_FLASHBACK.ENABLE_AT_TIME( TO_DATE(03-20-2002 14:00:00,MM-DD-YYYY HH24:MI:SS); Oracle uses a new table, SMON_SCN_TIME to translate timestamps to SCNs. Documentation states that it only tracks the last five days and is only

8、 in five minute increments. SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL; This is a excellent new feature for capturing the current SCN. EXEC DBMS_FLASHBACK.DISABLE;,7,#8. Resumable Space Operations,Resumable Space Operations allows for transactions to suspend and resume in the event of

9、repairable errors related to space limits and out-of-space conditions. Before 9i, operations that failed with MAXEXTENTS, UNABLE TO ALLOCATE EXTENT, etc. were rolled back automatically and an exception was returned. With 9i, you can alter a session with ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600 wh

10、ich will cause the session to waitin this case for one hour (and appear to hang) while allowing the DBA to fix the problem on the back end. Without the timeout clause, the default is two hours. IMP and SQLLDR also support resumable operations.,8,#8. Resumable Space Operations,Sessions are non-resuma

11、ble by default and must be set to resumable on a session by session basis. Users must be granted the resumable system privilege before they can alter their session. GRANT RESUMABLE TO MIKE; There are two ways to alert the DBA of a suspend event: An error is written to the alert log. A system event t

12、rigger, after suspend, can be executed. When the error condition is resolved, the suspended statement will automatically resume.,9,#8. Resumable Space Operations,The DBMS_RESUMABLE package allows for the control of this feature. EXEC DBMS_RESUMABLE.SET_TIMEOUT(seconds); For current session EXEC DBMS

13、_RESUMABLE.GET_TIMEOUT(); For current session EXEC DBMS_RESUMABLE.SET_SESSION_TIMEOUT(session_id, seconds); EXEC DBMS_RESUMABLE.GET_SESSION_TIMEOUT(session_id); EXEC DMBS_RESUMABLE.ABORT(session_id); EXEC DBMS_RESUMABLE.SPACE_ERROR_INFO(error_type, object_type, object_owner, table_space_name, object

14、_name, sub_object_name) SPACE_ERROR_INFO has all OUT parameters; The DBA(USER)_RESUMABLE views will show any suspended transactions.,10,#7. System Parameter File,The System Parameter File (SPFILE) has been introduced as a persistent server side binary file to store initialization parameters. It is m

15、aintained by the Oracle binaries. This file allows for ALTER SYSTEM and other dynamic statements to be remembered across database startups. Oracle will look for an spfile before it looks for a pfile. Unix: $ORACLE_HOME/dbs/spfile.ora Windows: $ORACLE_HOMEdatabase spfile.ora,11,#7. System Parameter F

16、ile,The SPFILE can be created from a PFILE: CREATE SPFILE =SPFILE NAME FROM PFILE =PFILE NAME; The SPFILE can be translated back to a PFILE: CREATE PFILE =PFILE NAME FROM SPFILE =SPFILE NAME; If the instance has been started with a pfile, then the SCOPE keyword can be used with ALTER SYSTEM commands

17、. ALTER SYSTEM SET TIMED_STATISTICS=TRUE SCOPE=MEMORY COMMENT=03/20/02 SCOPE=MEMORY will affect only the current instance. ALTER SYSTEM SET CURSOR_SHARING=SIMILAR SCOPE=SPFILE COMMENT=03/20/02 SCOPE=SPFILE will affect only future start-ups. ALTER SYSTEM SET OPEN_CURSORS=500 SCOPE=BOTH COMMENT=03/20/

18、02 SCOPE=BOTH will affect the current instance and will persist to the spfile. This is the default behavior if the database was started with an spfile.,12,#7. System Parameter File,A new view, V$SPPARAMETER will allow for a real time look at what is in the spfile. This will include what has been cha

19、nged with SCOPE=SPFILE. V$PARAMETER and V$PARAMETER2 are also available as well as the SQL*PLUS command SHOW PARAMETER. The SPFILE supports multiple instances with Real Application Clusters (RAC). This allows for one SPFILE regardless of how many instances are open. The phrase SID=* should be used i

20、n this environment. The statement ALTER SYSTEM RESET will remove the entry from the spfile and revert to the default. ALTER SYSTEM RESET COMPATIBLE SCOPE=SPFILE SID=*;,13,#6. External Tables,External tables are flat files stored outside of the database that Oracle treats as a table. The data is read

21、-only and no indexes can be created. Object rights are controlled through SELECT TABLE and READ DIRECTORY privileges. UTL_FILE_DIR must be set appropriately.,14,#6. External Tables,CREATE DIRECTORY external_tables AS c:oracleoradatafreeney9external; CREATE TABLE EMP_EXT (EMPNO NUMBER(4,0), ENAME VAR

22、CHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4,0), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2,0) ORGANIZATION EXTERNAL (TYPE oracle_loader DEFAULT DIRECTORY external_tables ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE BADFILE external_tables:bad_emp_ext.txt LOGFILE external_table

23、s:log_emp_ext.txt FIELDS TERMINATED BY , MISSING FIELD VALUES ARE NULL) LOCATION (emp.txt) REJECT LIMIT UNLIMITED;,15,#6. External Tables,Once the table metadata has been created (as in the previous slide) , then this table can be queried just like any other table. This includes functions, joins, et

24、c. Two new views help in the administration of these external tables: DBA_EXTERNAL_TABLES lists the attributes of each external table in the database. DBA_EXTERNAL_LOCATIONS lists the specific flat files and their associated directories.,16,#5. DBMS_METADATA,DBMS_METADATA is a long overdue package t

25、hat allows for object DDL to be retrieved from the database. DBMS_METADATA has functions/procedures for programmatic use: DBMS_METADATA.OPEN () DBMS_METADATA.FETCH_DDL () DBMS_METADATA.FETCH_XML () DBMS_METADATA.CLOSE () DBMS_METADATA has functions for casual use: DBMS_METADATA.GET_DDL(object_type,

26、name, schema) DBMS_METADATA.GET_XML(object_type, name, schema),17,#5. DBMS_METADATA,SELECT DBMS_METADATA.GET_DDL(TABLE, EMP, SCOTT) from dual; CREATE TABLE SCOTT.EMP ( EMPNO NUMBER(4,0), ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4,0), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NU

27、MBER(2,0), CONSTRAINT PK_EMP PRIMARY KEY (EMPNO) USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE USERS ENABLE, CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO) RE

28、FERENCES SCOTT.DEPT (DEPTNO) ENABLE NOVALIDATE ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE USERS,18,#5. DBMS_METADATA,SELECT DBMS_METADATA.GET

29、_XML(TABLE, EMP, SCOTT) from dual; 1 0 5543 5543 5543 25 SCOTT EMP 1 1 2147483645 128 0 ,19,#5. DBMS_METADATA,This package will work for tables, indexes, views, packages, functions, procedures, triggers, synonyms, and types. I could not get it to work for clusters, package bodies, type bodies, direc

30、tories, sequences, or database links. Multiple objects can be selected with one statement by using: SELECT DBMS_METADATA.GET_DDL(TABLE, TABLE_NAME, OWNER) | ; from dba_tables where owner not in (SYS,SYSTEM, OUTLN); DBMS_METADATA.GET_XXX returns a clob, so make sure to execute: Set long XXXXX,20,#4.

31、TimeStamp Data Type,We now have a new datatype family called TIMESTAMP that allows us to store dates and time all the way down to 9 decimals of the second. The members of this family are: TIMESTAMP(4) Range from 0 to 9. 6 is the default. TIMESTAMP (6) WITH TIME ZONE Stores the absolute globalized ti

32、me zone. The time zone can be an offset from UTC, such as -5:0, or a region name, such as US/Pacific. TIMESTAMP (2) WITH LOCAL TIME ZONE On select, the database converts the timestamp to the time zone of the client. INTERVAL YEAR (1) TO MONTH Stores the year and month difference between two dates. I

33、t is represented as 1-6. INTERVAL DAY (1) TO SECOND (6) Stores the Day, Hour, Minute, and Seconds between two timestamps. It is represented as 1 2:3:4.555555.,21,#4. TimeStamp Data Type,The space usage for these new datatypes are: TIMESTAMP 7 to 11 bytes depending on precision. TIMESTAMP WITH TIME Z

34、ONE 13 bytes regardless of precision. TIMESTAMP WITH LOCAL TIME ZONE 7 to 11 bytes. INTERVAL YEAR TO MONTH 5 bytes regardless of year precision. INTERVAL DAY TO SECOND 11 bytes regardless of precision. TIMESTAMP WITH LOCAL TIME ZONE does not actually store the time zone with the data, but defaults i

35、nitially to the operating system time zone. It can be seen through the function DBTIMEZONE and can be altered with: CREATE DATABASE ORCL . SET TIME ZONE =-05:00.; ALTER DATABASE SET TIME_ZONE = CST ; You can also see the client with SESSIONTIMEZONE and change it with: ALTER SESSION SET TIME_ZONE = L

36、OCAL; ALTER SESSION SET TIME_ZONE =CST,22,#4. TimeStamp Data Type,This functionality has introduces several new parameters: NLS_TIME_TZ_FORMAT NLS_TIMESTAMP_FORMAT Default=DD-MON-RR HH.MI.SSXFFFFFF AM NLS_TIMESTAMP_TZ_FORMAT Default=DD-MON-RR HH.MI.SSXFFFFFF AM TZR X = Decimal Separator. FF = Fracti

37、onal Part of Second. TZR=Time Zone Region Name. (See V$TIMEZONE_NAMES for options616!) TZH=Time Zone Hour. We also have several new functions: CURRENT_DATE Returns SYSDATE converted to the session time zone. SYSTIMESTAMP Returns the current database timestamp with time zone. CURRENT_TIMESTAMP Return

38、s SYSTIMESTAMP converted to the session time zone. LOCALTIMESTAMP Returns SYSTIMESTAMP converted to the session time zone without the time zone portion of the value .,23,#4. TimeStamp Data Type,We also have several new conversion functions: TO_TIMESTAMP From String to Timestamp. TO_TIMESTAMP_TZ From

39、 String to Timestamp with Time Zone. TO_DSINTERVAL From String to Interval Day to Second. TO_YMINTERVAL From String to Interval Year to Month TO_CHAR Extended to accept the new format characters. EXTRACT Returns the requested value (as a number) from a datetime or interval datatype. Options are Year

40、, Month, Day, Hour, Minute, Second, Timezone_Hour, Timezone_Minute, Timezone_Region, or Timezone_ABBR. SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL; SELECT EXTRACT(MINUTE FROM TO_DSINTERVAL(1 2:3:4.555) FROM DUAL;,24,#3. Multiple Block Size Support,Oracle now supports multiple block sizes in the same

41、 database. The BLOCKSIZE keyword has been added to the CREATE TABLESPACE command. Valid values are: 2K 4K 8K 16K 32K (operating system dependent). CREATE TABLESPACE MY_TBS . BLOCKSIZE 16K; CREATE TABLESPACE MY_TBS . BLOCKSIZE 8192; The field BLOCK_SIZE has been added to the DBA_TABLESPACES view.,25,

42、#3. Multiple Block Size Support,The DB_BLOCK_SIZE parameter is still needed in the init.ora during database creation. This value is the default blocksize for new tablespaces as well as the required size for the system tablespace, undo tablespace and all temporary tablespaces. As with previous releas

43、es, DB_BLOCK_SIZE cannot be changed without recreating the database. All partitions of a partitioned object must be in tablespaces with the same block size. Indexes can be in a tablespace with a different block size than its corresponding table.,26,#3. Multiple Block Size Support,Several new paramet

44、ers support this feature: DB_CACHE_SIZE Replaces DB_BLOCK_BUFFERS. DB_2K_CACHE_SIZE Buffer Pool for objects with 2K blocks. DB_4K_CACHE_SIZE Buffer Pool for objects with 4K blocks. DB_8K_CACHE_SIZE Buffer Pool for objects with 8K blocks. DB_16K_CACHE_SIZE Buffer Pool for objects with 16K blocks. DB_

45、32K_CACHE_SIZE Buffer Pool for objects with 32K blocks. DB_nK_CACHE_SIZE is not allowed where nK is the default block size. DB_nK_CACHE_SIZE must be defined for each block size before a tablespace can be created or plugged in (exp/imp) with that block size.,27,#2. Dynamic Memory Allocation,Oracle no

46、w allows the DBA to alter the SGA dynamically. Oracle 9i introduced some new parameter: SGA_MAX_SIZE This is the maximum size that the SGA can grow to. Based on the operating system, this memory usage may be dynamic or it may be allocated on startup. If a value is not give for SGA_MAX_SIZE then it d

47、efaults to the sum of all the SGA components. This parameter is not dynamic. DB_CACHE_SIZEReplaces DB_BLOCK_BUFFERS and is specified in bytes. DB_KEEP_CACHE_SIZEReplaces BUFFER_POOL_KEEP. DB_RECYCLE_POOL_SIZEReplaces BUFFER_POOL_RECYCLE. V$BUFFER_POOL has been expanded to show statistics for each of

48、 the pools. The key fields are BLOCK_SIZE (K) and CURRENT_SIZE (M). V$BUFFER_POOL_STATISTICS has been expanded to include BLOCK_SIZE.,28,#2. Dynamic Memory Allocation,Each component (buffer caches, shared pool) are allowed to grow and shrink by units called “granules”. The granule size is determined

49、 by the value for SGA_MAX_SIZE. Granule size = 4M if SGA_MAX_SIZE 128M. The minimum size of the SGA is now 12Mone granule for the fixed SGA, one granule for the buffer cache and one granule for the shared pool. While the instance is running the DBA can execute: ALTER SYSTEM SET SHARED_POOL_SIZE=64M

50、SCOPE=BOTH; ALTER SYSTEM SET DB_8K_CACHE_SIZE=32M SCOPE=MEMORY; These commands can shrink or grow the pools by the granule size. The result of the command cannot grow the SGA beyond SGA_MAX_SIZE.,29,#2. Dynamic Memory Allocation,Oracle 9i also allows us much greater control over the PGA. The PGA is

51、split logically into two parts: Untunable MemorySql working area for memory constructs such as cursors, bind variables, etc. Tunable MemoryEverything else such as sort areas, merge areas, etc. Two new parameters have been introduced to control the tunable memory: WORKAREA_SIZE_POLICY (AUTO or MANUAL

52、). This parameter indicates whether you want to use the old method of PGA management with sort_area_size, hash_area_size, etc. or the new method of system managed PGA. The default value is MANUAL unless PGA_AGGREGATE_TARGET is set. PGA_AGGREGATE_TARGET This parameter indicates the target maximum amo

53、unt of memory that can be allocated to the SGA. It includes the tunable and untunable memory for all sessions. There is no default, and this parameter must be set before WORKAREA_SIZE_POLICY can be set to AUTO.,30,#2. Dynamic Memory Allocation,In order to monitor PGA usage, three new columns have be

54、en added to V$SYSSTAT, V$SESSTAT, and V$MYSTAT: workarea executions - optimal This represents the number of work areas that were optimally sized. No data was written to disk. workarea executions - onepass This represents the number of work areas that required writes to disk, but only one pass was ne

55、eded. workarea executions - multipass This represents the number of work areas that required writes to disk, and multiple passes were needed to complete the transaction. These should be avoided and are a sign of a poorly tuned system. Three new columns have been added to V$PROCESS. PGA_USED_MEM Tota

56、l PGA currently used by this process. PGA_ALLOC_MEM Total PGA currently allocated by this process. PGA_MAX_MEM Maximum PGA ever allocated by this process. SELECT SUM(PGA_USED_MEM), SUM(PGA_ALLOC_MEM), SUM(PGA_MAX_MEM) FROM V$PROCESS;,31,#1. SQL Enhancements,Oracle 9i is fully SQL:1999 join compliant

57、. CROSS JOIN (Cartesian Product). SELECT E.ENAME, D.DNAME FROM EMP E CROSS JOIN DEPT D; NATURAL JOIN (Equijoin on All Identically Named Columns). SELECT E.ENAME, D.DNAME FROM EMP E NATURAL JOIN DEPT D; USING clause (Similar to a Natural Join, but allows for the designation of which column(s) to use

58、in the equijoin). SELECT E.ENAME, D.DNAME FROM EMP E JOIN DEPT D USING (DEPTNO);,32,#1. SQL Enhancements,ON clause (Used to define columns to join on) SELECT E.ENAME, D.DNAME FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO); LEFT OUTER JOIN (All records from first table with matching rows from second

59、) SELECT E.ENAME, D.DNAME FROM EMP E LEFT OUTER JOIN DEPT D ON (E.DEPTNO = D.DEPTNO); RIGHT OUTER JOIN (All records from second table with matching rows from first) SELECT E.ENAME, D.DNAME FROM EMP E RIGHT OUTER JOIN DEPT D ON (E.DEPTNO = D.DEPTNO);,33,#1. SQL Enhancements,FULL OUTER JOIN (All records from both tablesIdentical to a union of left outer join and right outer join) SELECT E.ENAME, D.DNAME FROM EMP E FULL OUTER JOIN DEPT D ON (E.DEPTNO = D.DEPTNO); Keywo