oracle数据库学习

ORACLE数据库学习资料

Top 10 New Features in Oracle 9iPresented By: Mike Freeney OCP DBAOracleManiac@http://www.77cn.com.cn

North Carolina Oracle Users Group 3/20/2002

ORACLE数据库学习资料

#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. Rollback 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

ORACLE数据库学习资料

#10. Automatic Undo Management 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 (TRUE or FALSE) Specifies whether or not to return an exception when “SET TRANSACTION USE ROLLBACK SEGMENT” is issued. Default = TRUE

ORACLE数据库学习资料

#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 tablespace 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.

ORACLE数据库学习资料

#9. Flashback Query Flashback Query allows users to see a consistent view of the database at a point in time in the 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.

ORACLE数据库学习资料

#9. Flashback 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 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;6

ORACLE数据库学习资料

#8. Resumable Space Operations Resumable Space Operations allows for transactions to suspend and resume in the event of 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]’ which will cause the session to wait—in this case for one hour (and appear to hang) while allow

ing 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.

ORACLE数据库学习资料

#8. Resumable Space Operations Sessions are non-resumable 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 trigger, ‘after suspend’, can be executed.

When the error condition is resolved, the suspended statement will automatically resume.8

ORACLE数据库学习资料

#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_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_name, sub_object_name)– SPACE_ERROR_INFO has all OUT parameters;

The DBA(USER)_RESUMABLE views will show any suspended transactions.

ORACLE数据库学习资料

#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 maintained 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<instance>.ora – Windows: $ORACLE_HOME\database\ spfile<instance>.ora

ORACLE数据库学习资料

#7. System Parameter File 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.– 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/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.11

ORACLE数据库学习资料

#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 changed with SCOPE=SPFILE. V$PARAMETER and V$PARAMETER2 are also available as well as the SQL*PLUS command ‘SHOW PARAMETER’. The SPF

ILE 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 in 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=‘*’;12

ORACLE数据库学习资料

#6. External Tables External tables are flat files stored outside of the database that Oracle treats as a table. The data is read-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.

ORACLE数据库学习资料

#6. External Tables CREATE DIRECTORY external_tables AS ‘c:\oracle\oradata\freeney9\external’; CREATE TABLE EMP_EXT (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 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_tables:‘log_emp_ext.txt’ FIELDS TERMINATED BY ‘,’ MISSING FIELD VALUES ARE NULL) LOCATION (‘emp.txt’)) REJECT LIMIT UNLIMITED; 14

ORACLE数据库学习资料

#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, etc.

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.

ORACLE数据库学习资料

#5. DBMS_METADATA DBMS_METADATA is a long overdue package that 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, name, schema) – DBMS_METADATA.GET_XML(object_type, name, schema)

ORACLE数据库学习资料

#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" NUMBER(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") REFERENCES "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" 17

ORACLE数据库学习资料

#5. DBMS_METADATA SELECT DBMS_METADATA.GET_XML(‘TABLE’, ‘EMP’, ‘SCOTT’) from dual;<?xml version="1.0"?> <ROWSET> <ROW> <TABLE_T> <VERS_MAJOR>1</VERS_MAJOR> <VERS_MINOR>0</VERS_MINOR> <OBJ_NUM>5543</OBJ_NUM> <SCHEMA_OBJ> <OBJ_NUM>5543</OBJ_NUM> <DATAOBJ_NUM>5543</DATAOBJ_NUM> <OWNER_NUM>25</OWNER_NUM> <OWNER_NAME>SCOTT</OWNER_NAME> <NAME>EMP</NAME> <NAMESPACE>1</NAMESPACE> <MINEXTS>1</MINEXTS> <MAXEXTS>2147483645</MAXEXTS> <EXTSIZE>128</EXTSIZE> <EXTPCT>0</EXTPCT> …

ORACLE数据库学习资料

#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, directories, 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

本文来源：https://www.bwwdw.com/article/72li.html