Les nouvelles fonctionnalités d'Oracle 10g V1.0

Résumé du document

Support de cours d'Informatique permettant de découvrir les nouvelles fonctionnalités Oracle10G. Il explique l'installation d'Orcle 10g, comment migrer une base de données d'une version antérieure et surtout décrit tous les nouveaux modules = RMAN, AWR, ADDM...

Extraits

[...] week nights and all day on weekends. The job calls the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC internal procedure which gathers statistics for tables with either empty or stale statistics, similar to the DBMS_STATS.GATHER_DATABASE_STATS procedure using the GATHER AUTO option. The main difference is that the internal job prioritizes the work such that tables most urgently requiring statistics updates are processed first. In some cases automatically gathering statistics can cause problems. Highly volatile tables and load tables may have their statistics gathered when there is an unrepresentative number of a row present. [...]

[...] Support de cours page : Schedules Schedules optionally define the start time, end time and interval related to a job. Schedules are created using the CREATE_SCHEDULE procedure: Create the schedule. BEGIN DBMS_SCHEDULER.create_schedule ( schedule_name ' test_hourly_schedule' , start_date SYSTIMESTAMP, repeat_interval ' freq=hourly; byminute=0' , end_date NULL, comments ' Repeats hourly, on the hour, for ever.' END; / PL/SQL procedure successfully completed. Display the schedule details. SELECT owner, schedule_name FROM dba_scheduler_schedules; OWNER SCHEDULE_NAME DAILY_PURGE_SCHEDULE SYS TEST_HOURLY_SCHEDULE 2 rows selected. Notice how the interval is defined using the calendaring syntax. [...]

[...] db1102C Email address for notifications . Outgoing Mail (SMTP) server for notifications . you wish to continue? Y Apr 11:00:24 AM oracle.sysman.emcp.EMConfig perform INFO: This operation is being logged at 10.2 .0\db_1\cfgtoollog Apr 11:00:38 AM oracle.sysman.emcp.util.DBControlUtil startOMS INFO: Starting Database Control (this may take a while) . Apr 11:01:18 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration INFO: Database Control started successfully Apr 11:01:18 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration INFO: The Database Control URL is http://db1-s1:1158/em user/password@nom_serveur:no_port/nom_service Support de cours page : MIGRATION 4.1 Migration manuelle Contrôles préalables avant la migration Analyser la database en utilisant le script utlu101i.sql Ajustez la base de données d'après les indications du script Faites une sauvegarde totale de la base Agrandissez les fichiers qui ont une taille insuffisante Recréez les fichiers redo log pour respecter les tailles imposées parOracle10g (au moins 4 Mo) Ajustez les paramètres d'instance et supprimez ceux qui sont obsolètes : SHARED_POOL_SIZE (au moins 96 Mo sur systèmes 32-bit et 144 Mo sur systèmes 64-bit). [...]

[...] Essentially it is the same as the DBMS_FLASHBACK functionality or Oracle9i, but in a more convenient form. For example: CREATE TABLE flashback_query_test ( id NUMBER(10)); SELECT current_scn, TO_CHAR(SYSTIMESTAMP, ' YYYY-MM-DD HH24:MI:SS' ) FROM v$database; (or dbms_flashback.get_system_change_number function in pl/sql); CURRENT_SCN TO_CHAR(SYSTIMESTAM 2004-03-29 13:34:12 INSERT INTO flashback_query_test VALUES COMMIT; SELECT COUNT(*) FROM flashback_query_test; COUNT(*) SELECT COUNT(*) FROM flashback_query_test AS OF TIMESTAMP TO_TIMESTAMP(' 2004-03-29 13:34:12' YYYY-MM-DD HH24:MI:SS' COUNT(*) SELECT COUNT(*) FROM flashback_query_test AS OF SCN 722452; COUNT(*) select * from system.emp as of timestamp(systimestamp - interval ' 60'minute); Flashback Query Functions The TIMESTAMP_TO_SCN and SCN_TO_TIMESTAMP functions have been added to SQL and PL/SQL to simplify flashback operations: SELECT * FROM emp AS OF SCN TIMESTAMP_TO_SCN(SYSTIMESTAMP - SELECT * FROM emp AS OF TIMESTAMP SCN_TO_TIMESTAMP(993240); DECLARE l_scn NUMBER; l_timestamp TIMESTAMP; BEGIN l_scn TIMESTAMP_TO_SCN(SYSTIMESTAMP - l_timestamp SCN_TO_TIMESTAMP(l_scn); END; Support de cours page : Flashback Version Query Flashback version query allows the versions of a specific row to be tracked during a specified time period using the VERSIONS BETWEEN clause: CREATE TABLE flashback_version_query_test ( id NUMBER(10), description VARCHAR2(50)); INSERT INTO flashback_version_query_test description) VALUES ' ONE' COMMIT; SELECT current_scn, TO_CHAR(SYSTIMESTAMP, ' YYYY-MM-DD HH24:MI:SS' ) FROM v$database; CURRENT_SCN TO_CHAR(SYSTIMESTAM 2004-03-29 14:59:08 UPDATE flashback_version_query_test SET description = ' TWO'WHERE id = 1;COMMIT; UPDATE flashback_version_query_test SET description = ' THREE'WHERE id = 1;COMMIT; SELECT current_scn, TO_CHAR(SYSTIMESTAMP, ' YYYY-MM-DD HH24:MI:SS' ) FROM v$database; CURRENT_SCN TO_CHAR(SYSTIMESTAM 2004-03-29 14:59:36 SELECT versions_startscn, versions_starttime, versions_endscn, versions_endtime, versions_xid, versions_operation, description FROM flashback_version_query_test VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP(' 2004-03-29 14:59:08' YYYY-MM-DD HH24:MI:SS' ) AND TO_TIMESTAMP(' 2004-03-29 14:59:36' YYYY-MM-DD HH24:MI:SS' ) WHERE id = STARTSCN STARTTIME ENDSCN ENDTIME VERSIONS_XID V DESCRIPTION .16PM 02001C0043030000 U THREE .16PM .16PM 0600030021000000 U TWO .16PM ONE SELECT versions_startscn, versions_starttime, versions_endscn, versions_endtime, versions_xid, versions_operation, description FROM flashback_version_query_test VERSIONS BETWEEN SCN 725202 AND 725219 WHERE id = VSTARTSCN STARTTIME ENDSCN ENDTIME VERSIONS_XID V DESCRIPTION .16PM 02001C0043030000 U THREE .16PM .16PM 0600030021000000 U TWO .16PM ONE 12.3 Flashback Transaction Query Flashback transaction query can be used to get extra information about the transactions listed by flashback version queries. [...]

[...] PURGE DBA_RECYCLEBIN; The whole recycle bin. Several restrictions apply relating to the recycle bin: Only available for non-system, locally managed tablespaces. There is no fixed size for the recycle bin. The time an object remains in the recycle bin can vary. The objects in the recycle bin are restricted to query operations only (no DDL or DML). Flashback query operations must reference the recycle bin name. Tables and all dependent objects are placed into, recovered and purged from the recycle bin at the same time. [...]