i. Flashback Versions Query. – select * from table versions between scn 1000 and 1001; or between timestamp…, pseudo cols- VERSIONS_STARTTIME, VERSIONS_ENDTIME, VERSIONS_XID, VERSIONS_OPERATION, FLASHBACK_TRANSACTION_QUERY
ii. USER_RECYCLEBIN. -- show recyclebin; FLASHBACK TABLE RECYCLETEST TO BEFORE DROP.
iii. Automatic Workload Repository. -- DBMS_WORKLOAD_REPOSITORY, snapshots collected every hour by MMON, purged after 7 days. Reports retained for 1 month. Calls ADDM after every snapshot. In earlier versions we had statspack, an utility with less functions.
iv. ADDM. -- Robotic DBA monitors database performance statistics to identify bottlenecks, analyze SQL statements, gives suggestions to improve performance with other "advisors".
v. Datapump. -- expdp, impdp, DBMS_DATAPUMP, DBMS_METADATA.
vi. Improved Tablespace Management. – Default TBS, renaming TBS, SYSAUX - V$SYSAUX_OCCUPANTS.
vii. Automatic Storage Management. – INSTANCE_TYPE = ASM -- ASM instance maps file extents to the physical disk blocks. One instance serves multiple dbs.
viii. ASM. – Provides integrated cluster file system and volume mgmt capablities, simplifies administration, Manages groups of disks, called disk groups, Manages disk redundancy within a disk group, Provides near-optimal I/O balancing without any manual tuning, Enables management without specifying mount points and filenames, Supports large files.
ix. Segment Management. – ASSM enables by default, SEGMENT SPACE MANAGEMENT AUTO in create tablespace command. No need to specify and tune the pctused, freelists, and freelist groups storage parameters. PCTFREE is still required as determines how rows will be packed into blocks.
x. The Automatic Shared Memory Management. – param SGA_TARGET decides the total size of the SGA. Individual pools within the SGA are configured dynamically.
xi. RMAN. -- Incremental Backups, Incremental Merge, Compressed backup, Recovery preview, Convert tablespace byte order (Endianness) for transporting Tbs.
xii. Sql Profiles. – created by Sql tuning Advisor, allows optimizer to know data distribution and correlations. dbms_sqltune.accept_sql_profile.
xiii. Sql Tuning Advisor. – (Recommendation on tuning sql) Checks for invalid objects, statistics, query rewrites, access paths with indexes, MVs, attach Sql Profiles.
xiv. SQL Access Advisor.--(Recommendation on creating objects) recommends the proper set of materialized views, materialized view logs, and indexes for a given workload.
xv. MEMORY ADVISOR. -- enabled on setting SGA_TARGET. provides graphical analyses of total memory target settings, SGA and PGA target settings, or SGA component size settings. The SGA advisor, shared pool advisor, buffer cache advisor, PGA advisor.
xvi. MTTR ADVISOR. -- enabled on setting FAST_START_MTTR_TARGET, V$MTTR_TARGET_ADVICE.
xvii. UNDO ADVISOR. --enabled on setting UNDO_RETENTION. provides graphical analyses of total memory target settings, SGA and PGA target settings, or SGA component size settings.
xviii. dbms_scheduler. – Procedure to create Jobs, programs, schedules, EVALUATE_CALENDAR_STRING for checking date, dbms_resource_manager for scheduling.
xix. Auto Gathering of Statistics. – param STATISTIC_LEVEL activates GATHER_STATS_JOB to gather statistics.
xx. Statistics History. -- dbms_stats.restore_table_stats, DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (45) – days default.
xxi. Dynamic Sampling.—when statistics are missing, param OPTIMIZER_DYNAMIC_SAMPLING or DYNAMIC_SAMPLING optimizer hint.
xxii. SAMPLE Clause. -- allows a query to return a limited sample of data by specifying a percentage of rows or blocks to scan. Now can be in complex queries. …FROM emp SAMPLE (10) e JOIN dept d…, …FROM emp SAMPLE BLOCK (10) e JOIN dept…
xxiii. ORA_ROWSCN. -- Returns the conservative upper bound system change number (SCN) of the most recent change to the row. Enabled on setting ROWDEPENDENCIES while creating the table. (My def. SCN set by commit.)
xxiv. MERGE.—Optional Matched, Non Matched and delete.
xxv. Guaranteed Undo Retention. -- With RETENTION GUARANTEE in create undo tablespace…. ALTER TABLESPACE UNDO_TS2 RETENTION GUARANTEE; Gurantees undo unitl the sql exceeds undo_retention time.
xxvi. dbms_file_transfer.-- for copying tablespaces between databases and for moving tablespaces from one database to another., COPY_FILE ,GET_FILE , PUT_FILE
xxvii. Bigfile Tablespace.—only one file up to 8 EB in size. Cannot be dictionary managed.
xxviii. Temporary Tablespace Group. -- Allows multiple default temporary tablespaces to be specified at the database level., DBA_TABLESPACE_GROUPS
xxix. Segment Management. -- dbms_space.space_usage, shrink segment space- alter table bookings shrink space(alone, moves HWM) compact(does nt move HWM) |cascade (compact indexes too), dbms_space.verify_shrink_candidate, select * from table(dbms_space.OBJECT_GROWTH_TREND).
xxx. Auditing. -- FGA: (DBA_FGA_AUDIT_TRAIL) now supports DMLs. Standard: (DBA_AUDIT_TRAIL), audit UPDATE on SCOTT.EMP by access.
xxxi. End-to-End Tracing. – Overcomes the limitation of tracing in shared server using a client identifier. DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE ('account_update').
xxxii. Database Usage. – Views DBA_HIGH_WATER_MARK_STATISTICS (features available.), DBA_FEATURE_USAGE_STATISTICS (features used.).
xxxiii. Larger LOBs.-- The limits on LOBs have been increased in Oracle Database 10G. The new maximum limits are calculated at (4GB ? 1 byte) * (the database block size). Thus, if the database block size is 8KB, there is essentially a 32GB limitation on LOBs in that database. Note that Bfiles are limited to 4GB in size.
xxxiv. Flushing the Buffer Cache. -- Oracle Database 10G now allows you to flush the database buffer cache with the alter system command using the flush buffer_cache parameter.
xxxv. Sorted Hash Clusters. -- Sorted hash cluster ensures rows will be stored within the hash cluster in the order defined when you created the hash cluster. This can eliminate the overhead associated with sort operations that might otherwise be required since data will be returned in a guaranteed order.
xxxvi. Encryption. -- DBMS_CRYPTO (new) contains more features than DBMS_OBFUSCATION_TOOLKIT (DOTK) (old).
xxxvii. Materialized Views. -- TUNE_MVIEW in DBMS_ADVISOR.
xxxviii.VPD. – new policy types. - dbms_rls.context_sensitive, dbms_rls.static, dbms_rls.shared_context_sensitive.
xxxix. Wait Interface – new cols in V$SESSION_WAIT, AWR is V$ACTIVE_SESSION_HISTORY.
xl. Rollback Monitoring. -- V$SESSION_LONGOPS – gives the actual time required by the rollback. Earlier SELECT USED_UREC FROM V$TRANSACTION; which returns the number of undo records used by the current transaction.
xli. EM. -- Now a HTTP server (called DB Console) in the server. Previously a client installed tool.
xlii. Transport tablespaces. –with datapump and external tables. Create table trans_dump organization external (type oracle_datapump default directory dump_dir location ('trans_dump.dmp')) parallel 2 as select…
xliii. NID (also called NEW DBID) is a utility in the ORACLE_HOME/bin directory that can be used to assign a new DB ID (and optionally a new DB_NAME) to a database.
xliv. PLSQL. – Quoting - v: = q'[It's a string with embedded quotes...]’.
xlv. PL/SQL Native Compilation.-- Compiled binaries are stored in the db and backed up, Set Param PLSQL_NATIVE_LIBRARY_DIR.
xlvi. FORALL Support for Non-Consecutive Indexes. – The INDICES OF clause for sparse collections, VALUE OF clause for collections of indexes pointing to other collections.
xlvii. Nested Table Enhancements. – assigning collections, comparing, SET function removes duplicates.
xlviii. New IEEE Floating-Point Types. -- BINARY_FLOAT and BINARY_DOUBLE.
xlix. Compile-Time Warnings. -- PLSQL_WARNINGS= ALL | SEVERE | INFORMATIONAL | PERFORMANCE.
l. Implicit Conversion. Between CLOB and NCLOB.
li. Regular Expressions.
lii. UTL_COMPRESS. -- Compression and decompression of binary data, Lempel-Ziv compression algorithm.
liii. Partitioned outer join. -- tab_1 (partition by col1) right outer join tab_2. -- For each distinct value in col1, Joins with tab2 and gives output.
liv. SQLPLUS. – Detailed prompt, login.sql executed even at connect.
lv. Model clause. Select FROM sales_view MODEL RETURN UPDATED ROWS PARTITION BY (country) DIMENSION BY (prod, year) MEASURES (sale sales) RULES (sales ['Bounce', 2002] = sales['Bounce', 2001] + sales['Bounce', 2000],...
lvi. Drop database.—from RMAN, Db should be in EXCLUSIVE mode with RESTRICTED SESSION enabled.
lvii. Index-Organized Table (IOT) Partitioning Enhancements.
lviii. Eliminate Duplicated Columns in Index on an Index-Organized Table.
lix. Increased Number of Aggregates per Query. --There is no longer a limitation on the number or size of aggregations in a single SQL statement.
lx. ODM contains several data mining and data analysis algorithms for classification, prediction, regression, clustering, associations, feature selection, anomaly detection, feature extraction, and specialized analytics. It provides means for the creation, management and operational deployment of data mining models inside the database environment.
lxi. Oracle Data Mining Predictive Analytics (PA) is a package containing two programs – Predict and Explain – each requiring only that the input data be in the correct format, and making no demands on the user regarding algorithm choices or parameter settings.
lxii. ODM includes both a Java and a PL/SQL Application Programming Interface (API), allowing a programmer to embed ODM functionality.
lxiii. ODM supports a graphical user interface, Oracle Data Miner (ODMr), for use by the business analyst who has a thorough understanding of the business as well as the data available for data mining solutions.
lxiv. DBMS_LOGMNR. --LogMiner enables the analysis of the contents of archived redo logs. It can be used to provide a historical view of the database without the need for point-in-time recovery. It can also be used to undo operations, allowing repair of logical corruption.
Release 2:
lxv. Enhanced Commit. – COMMIT WRITE WAIT (Default), WRITE NOWAIT, WRITE BATCH (writes to the redo logs are buffered), WRITE IMMEDIATE (triggers LGWR).
lxvi. Catch the Error and Move On. -- dbms_errlog.CREATE_ERROR_LOG ('ACCOUNTS','ERR_ACCOUNTS')- insert into accounts select * from accounts_ny log errors into err_accounts reject limit 200.
lxvii. Protect the Code at Source. -- dbms_ddl.create_wrapped ('create or replace procedure p1 as begin null; end ;').
lxviii. Conditional Compilation. – Macros- $if $$ppval $then return 'PPVAL was TRUE'; $else return 'PPVAL was FALSE'; $end -- alter session set plsql_ccflags = 'PPVAL:TRUE';.
lxix. Unlimited DBMS Output. –jus set serveroutput on, show serveroutput;
lxx. Drop Empty Datafiles. -- alter tablespace users drop datafile '/tmp/users01.dbf'
lxxi. Faster Startup. -- At startup only 10% of the buffer cache is initialized, rest is initialized after the db is opened.
lxxii. Automatic Segment Advisor. -- select * from table (dbms_space.asa_recommendations());
lxxiii. Event-Based Scheduling. – in DBMS_SCHEDULER.
lxxiv. Direct SGA Access for Hung/Slow Systems.—Using EM in “Monitor in Memory Access Mode”, directly access SGA without creating a session.
lxxv. Activity Session History (ASH). -- history of the activities of all recent active sessions written into AWR.
lxxvi. Compare Periods Report (AWR).
lxxvii. NLS_COMP.-- LINGUISTIC, has been added to support the existing and remaining SQL & PL/SQL operators and functions.
lxxviii. Rules Manager. -- DBMS_RLMGR, Enables developers to create applications that process and respond to events of any complexity using rules and policies defined in the database.
lxxix. Regular Expression Enhancements for Perl compatibility.
lxxx. Enhancements to MODEL.-- Rules Which Use FOR Loops, ANY, IN, >, <, and LIKE, Use of Analytic Functions
lxxxi. Increased Maximum Number of Partitions Per Object. -- The maximum number of partitions is now 1024K-1.
lxxxii. Explain Plan. --SET AUTOTRACE EXPLAIN PLAN_TABLE queries are replaced by a call to the DBMS_XPLAN package.
lxxxiii. Migrate Non-ASM Databases to ASM
lxxxiv. Workspace Manager.-- lets you version-enable one or more user tables in the database. When a table is version-enabled, all rows in the table can support multiple versions of the data. Procedures DBMS_WM EnableVersioning, DisableVersioning, CreateWorkspace, GotoWorkspace, RemoveWorkspace.
lxxxv. Optimizer Statistics Management thru EM.
lxxxvi. LONG to LOB Conversion via Online Redef.
lxxxvii.Online Limit Changes. -- MAXDATAFILES, MAXLOGFILES.
lxxxviii. DBMS_ASSERT. --contains a number of functions that can be used to sanitize user input and help to guard against SQL injection in applications that don't use bind variables.
lxxxix. Online Reorg of a Single Partition. -- DBMS_REDEFINITION.
xc. Redefine a Partition Online.—thru existing DBMS_REDEFINITION.
xci. Transportable Tablespace from Backup thru RMAN easily.
xcii. Partition-Change Tracking: No Need for MV Logs.
xciii. Query Rewrite with Multiple MVs.
xciv. Oracle's Own Backup. -- Oracle Secure Backup (OSB) replaces third party tools.
xcv. Dynamic RMAN Views for Past and Current Jobs. -- V$RMAN_BACKUP_JOB_DETAILS, V$RMAN_OUTPUT.
xcvi. Flashback Database/Query through RESETLOGS.
xcvii. Restore Point in Flashback Database. -- create restore point before_monthend_200503 guarantee flashback database.
xcviii. ASM Command Line Tool. – perl tool with command similar to unix.
xcix. Block Integrity Checking in Memory. -- Not Just on Disk.
c. Manage Multiple Objects in Oracle Enterprise Manager. -- no need of tpt tools.
ci. Interruptible SQL Access Advisor. -- v$advisor_progress.
cii. Check for Tracing Enabled.--new columns in v$session.
ciii. Dynamic Channel Allocation for Oracle RAC Clusters.
civ. TDE. – encrypting more columns, external tables and datapump files.
cv. Quick Partition Split for Partitioned, Index-Organized Tables (Oracle Database 10g Release 2 simply creates the new maxvalue partition and makes the existing partition the newly created monthly partition. This results in no data movement at all and rowids don’t change)
cvi. Drop a partitioned Table in Chunks (drops partition by partition by default consuming fewer resources).
cvii. Peek into the Flash Recovery Area. -- V$FLASH_RECOVERY_AREA_USAGE.
cviii. XML. -- XQuery and XMLTable functions.
cix. Audit Trails in XML Format. -- combine this XML with a SQL query, V$XML_AUDIT_TRAIL.
cx. RAT.