Finding current SCN of a database

There are two ways to get the current SCN (System Change Number) for an oracle 10g and 11g database

DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER and V$database

Method 1:  Using DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER function

SQL>   SELECT TO_CHAR(dbms_flashback.get_system_change_number) FROM dual;

GET_SYSTEM_CHANGE_NUMBER

----------------------------------------

   11626841778005

Method 2 :    Using current_scn column from v$database;

SQL>   SELECT TO_CHAR(CURRENT_SCN) FROM V$DATABASE;

CURRENT_SCN

--------------------

11626841778008

Maximum ROW LENGTH

DATA_LENGTH reports the maximum length that a column can have (e.g.  VARCHAR columns), it does not show the actual size of the data but we can put unlimited number of rows in a table

To get a row size you can analyze tables and get the average row size from user_tables column: AVG_ROW_LEN

There is no specific LIMIT on how long the row can be in an Oracle table however there is a limit on number of columns.

Oracle RDBMS release	MAX. no of columns/tables
7 8 9 10	254 1000 1000 1000

Differences between STATSPACK and AWR

1.  AWR is the next evolution of statspack utility.

2.  AWR is the 10g NEW feature but statspack can still be used in 10g.

3.  AWR repository holds all the statistics available in statspack as well as some additional statistics which are not (10g new features).

4.  Statspack does not STORE the ASH statistics which are available in AWR dba_hist_active_sess_history VIEW.

5.  Important difference between both is STATSPACK doesn’t store history for new metric statistics introduced in Oracle 10g.The key AWR views dba_hist_sysmetric_history  and  dba_hist_sysmetric_summary.

6.  AWR contains views such as dba_hist_service_stat,  dba_hist_service_wait_class and dba_hist_service_name.

7.  Latest version of statspack included with 10g contains a specific tables which track history of statistics that reflect the performance of Oracle streams feature. These tables are stats$streams_capture, stats$treams_apply_sum, stats_buffered_subscribers, stats$rule_set.

8.  The AWR does not contain specific tables that reflect oracle streams activity. Therefore if DBA relies on Oracle streams it would be useful to monitor its performance using Statspack utiity.

9.  AWR snapshots are scheduled every 60 minutes by default.

             10.   Statspack snapshot purges must be scheduled manually but AWR snapshots are purged automatically by MMON every night.

More about AWR click here
Steps to generate AWR report click here