Know Oracle Date And Time Function

Oracle9i provides extended date and time support across different time zones with the help of new datetime data types and functions. To understand the working of these data types and functions, it is necessary to be familiar with the concept of time zones.

This topic group introduces you to the concepts of time such as Coordinated Universal Time, time zones, and daylight saving time.

Objectives

After completing this topic group, you should be able to:

Calculate the date and time for any time zone region using time zone offsets.

Time Zones

The hours of the day are measured by the turning of the earth. The time of day at any particular moment depends on where you are.
100000

The earth is divided into twenty four time zones, one for each hour of the day. The time along the prime meridian in Greenwich, England is known as Coordinated Universal Time, or UTC (formerly known as Greenwich Mean Time, or GMT ). UTC is the time standard against which all other time zones are referenced.

Note: The following topics discuss prime meridian and UTC in more detail.

Coordinated Universal Time

100001

Since time began, the time flow on earth has been ruled by the apparent position of the sun in the sky.

In the past, when methods of transportation made even short travels last for several days, no one, except astronomers, understood that solar time at any given moment is different from place to place.

Around the 1800s with the development of faster modes of transportation and a need for accurate time references for sea navigation, Greenwich mean time (GMT), which later became known as Coordinated Universal Time (UTC), was introduced.

The earth surface is divided into 24 adjacent, equal, and equatorially perpendicular zones, called time zones. Each time zone is delimited by 2 meridians. UTC is the time standard against which all other time zones in the world are referenced.

UTC is measured with astronomical techniques at the Greenwich astronomical observatory in England.

Daylight Saving Time

“Just as sunflowers turn their heads to catch every sunbeam, there is a simple way to get more from the sun.”

Purpose of Daylight Saving Time

100004

The main purpose of daylight saving time (called Summer Time in many places around the world) is to make better use of daylight. By switching clocks an hour forward in summer, we can save a lot of energy and enjoy sunny summer evenings. Today approximately 70 countries use daylight saving time.

When Is Daylight Saving Time Observed Around the World?

Country Begin Daylight Saving Time Back to Standard time
US; Mexico; Canada 2:00 a.m. on the first Sunday of April 2:00 a.m. on the last Sunday of October
European Union 1:00 a.m. on the last Sunday in March 2:00 a.m. on the last Sunday of October

Equatorial and tropical countries from the lower latitudes do not observe daylight saving time. Because the daylight hours are similar during every season, there is no advantage to moving clocks forward during the summer.

How Is This Information Relevant To Time Zones?

The world is divided into 24 time zones and UTC is the time standard against which all other time zones in the world are referenced. When daylight saving time comes into effect in certain countries, the time zone offset for that country is adjusted to accomodate the change in time.

For example: The standard time zone offset for Geneva, Switzerland is UTC +01:00 hour. But when daylight saving time comes into effect the time zone offset changes to UTC +02:00 hours. The time zone offset changes to UTC +01:00 hour again, on the last Sunday in October, when the daylight saving time comes to an end.

Summary

The key learning points in this topic group included:

Coordinated Universal Time:
UTC is the time standard against which all other time zones in the world are referenced.

UTC Conversion:
To convert UTC to local time, you add or subtract hours from it. For regions
west of the zero meridian to the international date line (which includes all of North
America), hours are subtracted from UTC to convert to local time.

Daylight Saving Time:
Daylight saving time is used to make better use of daylight hours by switching clocks an hour forward in summer.

All this information is necessary to understand how the Oracle9i server provides support for time zones in its multi geography applications.

The next topic group “Database Time Zone Versus Session Time Zone” discusses the difference between Database Time Zone and Session Time Zone.

Database Time Zone Versus Session Time Zone

100015

Database Time Zone
Database time zone refers to the time zone in which the database is located.

Session Time Zone
Session time zone refers to the user’s time zone, from where he or she has logged on to the database.

Global Corporation is a finance company with offices around the world. The company head office is located in Barcelona (time zone : +01 hours). The company database is located in New York (time zone : -05 hours). Miguel from Sydney (time zone : +10 hours) has established a connection to the database.

DBTIMEZONE

The DBTIMEZONEfunction returns the value of the database time zone. The default database time zone is the same as the operating system’s time zone.

The return type is a time zone offset (a character type in the format ‘[+ | -]TZH:TZM‘ ) or a time zone region name, depending on how the user specified the database time zone value in the most recent CREATE DATABASE or
ALTER DATABASE
statement.

100016

You can set the database’s default time zone by specifying the SET TIME_ZONE clause of the CREATE DATABASE statement. If omitted, the default database time zone is the operating system time zone.


SESSIONTIMEZONE

The SESSIONTIMEZONEfunction returns the value of the session’s time zone.

The return type is a time zone offset (a character type in the format ‘[+|-]TZH:TZM’) or a time zone region name, depending on how the user specified the session time zone value in the most recent ALTER SESSION statement.

Altering the Session Time Zone

How can I change the session time zone?

The session time zone for a session can be changed with an ALTER SESSIONcommand.

Syntax

ALTER SESSION
SET TIME ZONE = ‘[+ |-] hh:mm’;

 

The key learning points in this topic group included:

Database Time Zone:
Database time zone refers to the time zone in which the database is located. You can use the DBTIMEZONE function to query the value of the database time zone.

Session Time Zone:
Session time zone refers to the time zone from which the user has logged on to the database. You can use the SESSIONTIMEZONE function to query the value of the session time zone.

TIMESTAMP

The TIMESTAMP data type is an extension of the DATEdata type.

It stores the year, month, and day of the DATE data type; the hour, minute, and second values; as well as the fractional second value.

Format

TIMESTAMP [(fractional_seconds_precision)]

The fractional_seconds_precision is used to specify the number of digits in the fractional part of the SECOND datetime field and can be a number in the range 0 to 9. The default is 6.

Grand Prix Qualifying Run

The line-up position for the Formula 1 Grand Prix is determined by the results of the qualifying run. Because the difference between the finishing times of the various drivers is very close, the finishing time of each driver is measured in fractional seconds. To store this kind of information, you can use the new TIMESTAMP data type.

TIMESTAMP WITH TIME ZONE

TIMESTAMP WITH TIME ZONE is a variant of the TIMESTAMP data type, that includes a time zone displacementin its value.

Format

TIMESTAMP[(fractional_seconds_precision)] WITH TIME ZONE

Earthquake Monitoring Station

Earthquake monitoring stations around the world record the details of tremors detected in their respective regions. The date and time of the occurrence of these tremors are stored, along with the time zone displacement, using the new TIMESTAMP WITH TIME ZONE data type. This helps people who analyze the information from locations around the world obtain an accurate perspective of the time when the event occurred.

TIMESTAMP WITH LOCAL TIME ZONE

TIMESTAMP WITH LOCAL TIME ZONE is another variant of the TIMESTAMPdata type. This data type also includes a time zone displacement.

Format

TIMESTAMP[(fractional_seconds_precision)] WITH LOCAL TIME ZONE

The TIMESTAMP WITH LOCAL TIME ZONE datatype differs from TIMESTAMP WITH TIME ZONE in that when you insert a value into a database column, the time zone displacement is used to convert the value to the database time zone.

Example

When a New York client inserts TIMESTAMP’1998-1-23 6:00:00-5:00′ into a TIMESTAMP WITH LOCAL TIME ZONE column in the San Francisco database. The inserted data is stored in San Francisco as binary value 1998-1-23 3:00:00.

The time-zone displacement is not stored in the database column.When you retrieve the value, Oracle returns it in your local session time zone.

When the New York client selects that inserted data from the San Francisco database, the value displayed in New York is `1998-1-23 6:00:00′. A San Francisco client, selecting the same data, gets the value ‘1998-1-23 3:00:00’.

New Year Celebration Broadcast

A television company is planning a live broadcast of New Year celebrations across the globe. To schedule a broadcast of the various events from across the globe, they use an application that stores the broadcast time using the TIMESTAMP WITH LOCAL TIME ZONE data type. Reporters located in different time zones can easily query to find out when to start and end their broadcasts, the output of which will be in their respective time zones.

TIMESTAMP:
With the new TIMESTAMP data type you can store the year, month, and day of the DATE data type; hour, minute, and second values; as well as the fractional second value.

TIMESTAMP WITH TIME ZONE:
The TIMESTAMP WITH TIME ZONE data type is a variant of the TIMESTAMP data type, that includes a time zone displacement in its value.

TIMESTAMP WITH LOCAL TIME ZONE:
The data stored in a column of type TIMESTAMP WITH LOCAL TIME ZONE is converted and normalized to the database time zone. Whenever a user queries the column data, Oracle returns the data in the user’s local session time zone.

TZ_OFFSET

Richard, a marketing executive, travels frequently to cities across the globe. He carries his laptop while travelling and updates the database located at the head office in San Francisco with information about his activities at the end of each day.

Since Richard is using a laptop for his work, he needs to update the session time zone every time he visits a new city.

Richard uses the TZ_OFFSET function to find the time zone offset for that city.

Syntax

SELECT TZ_OFFSET(‘Canada/Pacific’) FROM DUAL;

Note: For a listing of valid time zone name values, you can query the V$TIMEZONE_NAMES dynamic performance view.

ALTER SESSION Command

After Richard finds the time zone offset for the city he is visiting, he alters his session time zone using the ALTER SESSION command.

ALTER SESSION
SET TIME_ZONE = ‘-08:00’;

Richard then uses any of the following functions to view the current date and time in the session time zone.

CURRENT_DATE
CURRENT_TIMESTAMP
LOCAL_TIMESTAMP

Note: The following pages contain a detailed explanation of the functions listed above.

CURRENT_DATE

The CURRENT_DATE function returns the current date in the session’s time zone.The return value is a date in the Gregorian calendar. (The ALTER SESSION command can be used to set the date format to ‘DD-MON-YYYY HH24:MI:SS’.)

The CURRENT_DATE function is sensitive to the session time zone.

When Richard alters his session time zone to the time zone of the city that he is visiting, the output of the CURRENT_DATE function changes.

Example

Before the Session Time Zone is Altered

After the Session Time Zone is Altered

Observe in the output that the value of CURRENT_DATE changes when the TIME_ZONE parameter value is changed to -08:00.

Note: The SYSDATE remains the same irrespective of the change in the TIME_ZONE.
SYSDATE is not sensitive to the session’s time zone.

CURRENT_TIMESTAMP

The CURRENT_TIMESTAMP function returns the current date and time in the session time zone, as a value of the TIMESTAMP WITH TIME ZONE data type.

The time zone displacement reflects the local time zone of the SQL session.

Format

CURRENT_TIMESTAMP (precision)

Where precision is an optional argument that specifies the fractional second precision of the time value returned.

LOCALTIMESTAMP

 

The LOCALTIMESTAMP function returns the current date and time in the session time zone in a value of TIMESTAMP data type.

The difference between this function and the CURRENT_TIMESTAMP function is that LOCALTIMESTAMP returns a TIMESTAMP value, whereas CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE value.

Format

LOCALTIMESTAMP (TIMESTAMP_precision)

Where TIMESTAMP_precision is an optional argument that specifies the fractional second precision of the TIMESTAMP value returned.

EXTRACT

So far you have learned how Richard can alter his session date and view the current date and time in the session time zone.

Now observe how Richard can query a specified datetime field from a datetime or interval value expression using the EXTRACT function.

Format

SELECT EXTRACT ([YEAR] [MONTH] [DAY] [HOUR] [MINUTE] [SECOND]  [TIMEZONE_HOUR] [TIMEZONE_MINUTE] [TIMEZONE_REGION] [TIMEZONE_ABBR]
FROM [datetime_value_expression] [interval_value_expression]);

Using the EXTRACT function, Richard can extract any of the components mentioned in the preceding syntax.

Example

Richard can query the time zone displacement for the current session as follows:

SELECT EXTRACT(TIMEZONE_HOUR FROM CURRENT_TIMESTAMP) "Hour",                         
EXTRACT(TIMEZONE_MINUTE FROM CURRENT_TIMESTAMP) "Minute" FROM DUAL;

Datetime Functions: Conversion

Now examine some additional functions that help convert a CHAR value to a TIMESTAMP value, a TIMESTAMP value to a TIMESTAMP WITH TIME ZONEvalue, and so on.

The functions are:

TO_TIMESTAMP
TO_TIMESTAMP_TZ
FROM_TZ

TO_TIMESTAMP
The TO_TIMESTAMP function converts a string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a value of TIMESTAMPdata type.

Format

TO_TIMESTAMP(char,[fmt],[‘nlsparam’])

The optional fmt specifies the format of char. If you omit fmt, the string must be in the default format of the TIMESTAMP data type.

The optional nlsparam specifies the language in which month and day names and abbreviations are returned. If you omit nlsparams, this function uses the default date language for your session.

Example

SELECT TO_TIMESTAMP(‘2000-12-01 11:00:00’,
‘YYYY-MM-DD HH:MI:SS’)
FROM DUAL;

TO_TIMESTAMP_TZ

The TO_TIMESTAMP_TZ function converts a string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a value of TIMESTAMP WITH TIME ZONEdata type.

Format

TO_TIMESTAMP_TZ
(char,[fmt],[‘nlsparam’])

The optional fmt specifies the format of char. If you omit fmt, the string must be in the default format of the TIMESTAMP data type.

The optional nlsparam specifies the language in which month and day names and abbreviations are returned. If you omit nlsparams, this function uses the default date language for your session.

Example

SELECT TO_TIMESTAMP_TZ(‘2000-12-01 11:00:00 -08:00’,
‘YYYY-MM-DD HH:MI:SS TZH:TZM’)
FROM DUAL;

Note: The TO_TIMESTAMP_TZ function does not convert character strings to TIMESTAMP WITH LOCAL TIME ZONE.

FROM_TZ

The FROM_TZ function converts a timestamp value to a TIMESTAMP WITH TIME ZONEvalue.

Format

FROM_TZ(timestamp_value, time_zone_value)

Time_zone_value can be a character string in the format ‘TZH:TZM’ format or a character expression that returns a string in TZR (time zone region) format with optional TZD (time zone displacement) format.

Example Using the Format TZH:TZM

SELECT from_tz(TIMESTAMP ‘2000-12-01 11:00:00’,
‘-8:00’) “FROM_TZ”
FROM DUAL;

Example Using TZR

SELECT FROM_TZ(TIMESTAMP ‘2000-12-01 11:00:00’, ‘AUSTRALIA/NORTH’) “FROM_TZ”
FROM DUAL;

INTERVAL Data Type

The INTERVALdata type is used to represent the precise difference between two datetime values.

The two INTERVAL data types introduced in Oracle9i are:

INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND

Usage of the INTERVAL Datatype

The INTERVAL data type can be used to set a reminder for a time in the future or check whether a certain period of time has elapsed since a particular date.
For example: You can use it to record the time between the start and end of a race.

INTERVAL YEAR TO MONTH

You can use the INTERVAL YEAR TO MONTHdata type to store and manipulate intervals of years and months.

Format

INTERVAL YEAR[(precision)] TO MONTH

Where precision specifies the number of digits in the years field.

You cannot use a symbolic constant or variable to specify the precision; you must use an integer literal in the range 0-4. The default value is 2.

Automated Generation of Expiration Date

The packaging department of Home Food Products Ltd has decided to automate the generation of the expiration date details of its products.

INTERVAL DAY TO SECOND

INTERVAL DAY TO SECONDstores a period of time in terms of days, hours, minutes, and seconds.

Format

INTERVAL DAY[(day_precision)]
TO SECOND[(fractional_seconds_precision)]

Where day_precision is the number of digits in the DAY datetime field. Accepted values are 0 to 9. The default is 2.

Fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values are 0 to 9. The default is 6.

Automated Generation of the Arrival Time

The Railway Enquiry department wants to automate the generation of the arrival time for all of its trains.

You have just learned about the new INTERVAL data types introduced with the Oracle9iserver.

INTERVAL YEAR TO MONTH:

The data type INTERVAL YEAR TO MONTH is used to store and manipulate intervals of years and months.

TO_YMINTERVAL function:

The TO_YMINTERVAL function converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to an INTERVAL YEAR TO MONTH type, where CHAR is the character string to be converted.

INTERVAL DAY TO SECOND:

The INTERVAL DAY TO SECOND data type stores a period of time in terms of days, hours, minutes, and seconds.

TO_DSINTERVAL function:

The TO_DSINTERVAL function converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to an INTERVAL DAY TO SECOND data type.

 

Rollback Segment Utilization:Extent, Wrap and Shrink

This practice will demonstrate the  concept of extent, wrap and shrink in rollback segment utilization. You will:

  • Use the create rollback segment and alter rollback segment syntax.
  • Examine the V$ROLLSTAT view.
  • Determine what would be required to force an extent, a wrap and a shrink.

ASSUMPTIONS

  • The directory and filenames referenced in the commands in this practice reference the UNIX operating system.  However, simply changing the directory and filename references to match the operating system you are using will allow all the commands to work properly on your operating system.
  • The database version must be Oracle8i release 2, or higher.
  • The database blocksize is 2048 bytes.
  • The output produced in these instructions is from a UNIX operating system.  There may be some variance in your output data.

INSTRUCTIONS:

1.    Create a rollback segment of initial 10k, next 10k and minextents of 2.

Ensure there is only one user rollback segment online so that all the transactions have to use this newly created rollback segment.

SQL> create rollback segment RBS4

storage (initial 10K next 10K minextents 2);

Rollback segment created.

 

SQL> alter rollback segment RBS4 online;

Rollback segment altered.

 

Note:  Put all the other user RBSs offline

 

 

 

2.    Create two user sessions that use the rollback segment RBS4.   In Session 1 create TAB111 and insert a value.  Do not commit.  In Session 2, issue create table TAB112 as select * from sys.obj$ where 1=2;

Examine the statistics in V$ROLLSTAT and select the number of shrinks, wraps and extends.  Check how many extents and blocks belong to this rollback segment. Determine what would be required to force an extent, a wrap and a shrink.

 Session 1

 

SQL> create table TAB111 ( a number);

Table created.

 

SQL> insert into TAB111 values (1);

1 row created.

 

Note: This session does not commit.  This means that the first extent cannot be reused.

 

Session 2

 

SQL> create table TAB112

as select *

   from sys.obj$

   where 1 = 2;

Table created.

 

 

SQL> select hwmsize, shrinks, wraps, extends

from v$rollstat

where usn = 5;

HWMSIZE    SHRINKS      WRAPS    EXTENDS

———- ———- ———- ———-

129024          0          0          0

 

SQL> insert into TAB112 select * from sys.obj$;

3121 rows created.

 

SQL> select hwmsize, shrinks, wraps, extends

from v$rollstat

where usn = 5;

 

HWMSIZE    SHRINKS      WRAPS    EXTENDS

———- ———- ———- ———-

260096          0          3          2

 

 

Note: Session 2 has run a long running transaction.  Initially, the current extent is extent 0 (which is where the other transaction started running).  Every new transaction gets allocated blocks in the current extent as long as they are available.  When extent 0 is full, the transaction moves on to extent 1 (making it now the current extent).  The number of wraps increases by one when moving from one extent to the next.

Again, new blocks are allocated from this extent until none is available. Then, we try to wrap back into extent 0 (remember, initially there are only two extents).  However, this is not allowed as session 1 has an active transaction in extent 0.  Every time the head of the extent list catches up with the tail, a new extent must be added. Extends is now increased and since we are moving to the newly allocated extent, wraps is also increased (now it would have the value 2).

This process is repeated one more time, and we end up with the solution displayed: wraps=3,

extends = 2.

3.        Commit both active transactions and re-examine v$rollstat.   Force RBS4 to shrink and re-examine v$rollstat to see the changes.

Session 1

 

SQL> commit;

Commit complete.

 

Session 2

 

SQL> commit;

Commit complete.

 

SQL> select hwmsize, shrinks, wraps, extends

from v$rollstat

where usn = 5;

 

HWMSIZE    SHRINKS      WRAPS    EXTENDS

———- ———- ———- ———-

260096          0          3          2

 

SQL> alter rollback segment rbs4 shrink;

Rollback segment altered.

 

SQL> select hwmsize, shrinks, wraps, extends

from v$rollstat

where usn = 5;

 

HWMSIZE    SHRINKS      WRAPS    EXTENDS

———- ———- ———- ———-

260096          1          3          2

 

SQL> select optsize, extents

from v$rollstat

where usn=5;

OPTSIZE    EXTENTS

———- ———-

2

 

Note: When optimal is not set, the shrink command reduces the size of the rollback segment to 2.

4.        To demonstrate clearly how the number of wraps increases every time a different extent becomes the current one, repeat the same exercise above but create the rollback segment with three extents to start with.

SQL> alter rollback segment RBS4 offline;Rollback segment altered.

 

SQL> drop rollback segment RBS4;

Rollback segment dropped.

 

SQL> create rollback segment RBS4

storage (initial 10K next 10K minextents 3);

Rollback segment created.

 

SQL> alter rollback segment RBS4 online;

Rollback segment altered.

 

Note:  Put all the other user RBSs offline

 

 

5.        Create two user sessions and examine the statistics in V$ROLLSTAT.

Session 1 

SQL> insert into TAB111 values (1);

1 row created.

 

Note: This session does not commit.  This means that the first extent cannot be reused.

 

Session 2

 

SQL> select hwmsize, shrinks, wraps, extends

from v$rollstat

where usn = 5;

HWMSIZE    SHRINKS      WRAPS    EXTENDS

———- ———- ———- ———-

129024          0          0          0

 

SQL> insert into TAB112 select * from sys.obj$;

3121 rows created.

 

SQL> select hwmsize, shrinks, wraps, extends

from v$rollstat

where usn = 5;

 

HWMSIZE    SHRINKS      WRAPS    EXTENDS

———- ———- ———- ———-

260096          0          3          1

 

 

Note: We need a total of four extents to perform both transactions.  If the rollback segment has 2 extents to start with, there will be a need for an additional 2 (extends = 2).  If minextents is 3, then only one additional extent is necessary (extends = 1).

 

However, the wraps occur when we move from extent 0 to extent 1, from 1 to 2 and from 2 to 3 (wraps = 3).

6.        Re-execute the transaction for session 2, and examine V$ROLLSTAT.

Session 2 

SQL> insert into TAB112 select * from sys.obj$;

3121 rows created.

 

SQL> select hwmsize, shrinks, wraps, extends

from v$rollstat

where usn = 5;

 

HWMSIZE    SHRINKS      WRAPS    EXTENDS

———- ———- ———- ———-

456704          0          6          4

 

 

 

Note: Another run of the transaction forces the allocation of three more extents and the number of wraps continues to increase accordingly even though extent 0 has never been reused because the transaction in session 1 is preventing this.

7.        Commit both transactions and re-execute the insert into TAB112.

Session 1 

SQL> commit;

Commit complete.

 

Session 2

 

SQL> commit;

Commit complete.

 

SQL> insert into TAB112 select * from sys.obj$;

3121 rows created.

 

SQL> select hwmsize, shrinks, wraps, extends

from v$rollstat

where usn = 5;

 

HWMSIZE    SHRINKS      WRAPS    EXTENDS

———- ———- ———- ———-

456704          0         10          4

 

Note: Both transactions have committed now, so there is no need to allocate new extents but as we continue to move from one extent to the next, the number of wraps increases.

 

 

8.        Force RBS4 to shrink and re-examine V$ROLLSTAT.

 

SQL> alter rollback segment rbs4 shrink;

Rollback segment altered.

 

SQL> select hwmsize, shrinks, wraps, extends

from v$rollstat

where usn = 5;

 

HWMSIZE    SHRINKS      WRAPS    EXTENDS

———- ———- ———- ———-

456704          1         10          4

 

SQL> select optsize, extents

from v$rollstat

where usn=5;

OPTSIZE    EXTENTS

———- ———-

2

 

Note: When optimal is not set, the shrink reduces the size of the rollback segment to 2 not to minextents which in this case was set to 3.

9.    The following exercises illustrate what happens when optimal is set.  With optimal set, we first check whether we need to perform a shrink before crossing the extent boundary.

Create a rollback segment with minextents of 2 and optimal of 20k.  Ensure all other rollback segments are offline.

SQL> alter rollback segment RBS4 offline;

Rollback segment altered.

 

SQL> drop rollback segment RBS4;

Rollback segment dropped.

 

SQL> create rollback segment RBS4

storage (initial 10K next 10K minextents 2 optimal 20k);

Rollback segment created.

 

SQL> alter rollback segment RBS4 online;

Rollback segment altered.

 

Note:  Put all the other user RBSs offline

 

 

 

10.     Create two user sessions and start a transaction in Session 1 by inserting a value.  Do not commit this session.

In Session 2, examine V$ROLLSTAT for extents and wraps.  Issue insert into TAB112 as select * from sys.obj$;   Re-examine V$ROLLSTAT and note the changes.

 

 

Session 1 

SQL> insert into TAB111 values (1);

1 row created.

 

Session 2

 

SQL> select hwmsize, shrinks, wraps, extends

from v$rollstat

where usn = 5;

HWMSIZE    SHRINKS      WRAPS    EXTENDS

———- ———- ———- ———-

129024          0          0          0

 

SQL> insert into TAB112 select * from sys.obj$;

3121 rows created.

 

SQL> select hwmsize, shrinks, wraps, extends

from v$rollstat

where usn = 5;

 

HWMSIZE    SHRINKS      WRAPS    EXTENDS

———- ———- ———- ———-

260096          0          3          2

 

 

Session 2

 

SQL> insert into TAB112 select * from sys.obj$;

3121 rows created.

 

SQL> select hwmsize, shrinks, wraps, extends

from v$rollstat

where usn = 5;

 

HWMSIZE    SHRINKS      WRAPS    EXTENDS

———- ———- ———- ———-

456704          0          6          5

 

 

 

Note: Another run of the transaction forces the allocation of three more extents and the number of wraps continues to increase accordingly even though extent 0 has never been reused because the transaction in session 1 is preventing this.

  1. Commit both sessions. In Session 2, re-execute the insert from sys.obj$ and examine the shrinks, wraps and extends from V$ROLLSTAT.

Determine the optimal size from V$ROLLSTAT and explain the results.

 Session 1

 

SQL> commit;

Commit complete.

 

Session 2

 

SQL> commit;

Commit complete.

 

SQL> insert into TAB112 select * from sys.obj$;

3121 rows created.

 

SQL> select hwmsize, shrinks, wraps, extends

from v$rollstat

where usn = 5;

 

HWMSIZE    SHRINKS      WRAPS    EXTENDS

———- ———- ———- ———-

456704          1         10          8

 

SQL> select optsize, extents

from v$rollstat

where usn=5;

 

OPTSIZE    EXTENTS

———- ———-

20480          5

 

Note: At the time of the shrink there were 7 extents in the rollback segment, the two we started with plus 5 extends.  Optimal was set to 20kb = 2 extents. The current extent (number 7) cannot be deallocated and neither can the initial extent. The shrink brings the rollback segment size down to optimal.  As the transaction runs, it required 3 more extents, hence extends is now 8 and the number of extents is back to 5.

Know about Oracle Network Security

Good network security is accomplished by utilizing port and protocol screening with routers, firewalls,
and Intrusion Detection Systems.Port and protocol screening with routers, firewalls,
and Intrusion Detection Systems create a bastion against network attacks.

A device that routes and translates information between interconnected networks is called a firewall.
Firewalls have a different function
Routers, not firewalls, use destination address and origin address to select the best path to route traffic.

When installing a firewall, the first action is to stop all communication.
After installation, the System Administrator adds rules that allow specific types of traffic to pass through the new firewall.
After installation of a firewall, the System Administrator adds rules
that allow specific types of traffic to pass through the new firewall

A switch is a data link layer device that forwards traffic based on MAC addresses.
Switching is performed in hardware instead of software, so it is significantly faster.

Network Security Wizards Dragon 4.0 is an example of vendors that offer  Intrusion Detection Systems or IDS

1.
Authentication is the process of verifying the identity of a user, device, or other entity.
Once the identity is verified, a trust relationship is established and further network interaction is possible.

2.
Authorization is the process of assigning various levels of access and capabilities for the authenticated user.
In other words, authorization allows assigned levels of access in the database environment.

3.
Oracle 8i supports 3 models for storing Authorizations in a centralized directory service. Public Key Infrastructure,
Microsoft Active Directory, or Distributed Computing Environment. PKI together with Oracle Internet Directory is the optimal method.

4.
Most issues of data security can be handled by Oracle8i authentication mechanisms.

5.
The init.ora file, or instance configuration file, is one of the key configuration files
in an Oracle database environment that must be protected.
This file contains all the initialization parameters: the configurable parameters that are applied when an instance is started up.

6.
A file transfer copy of the tnsnames.ora configuration file is a common way for hackers to discover whether the
AUDIT function is enabled. If they determine that AUDIT is enabled, they can take steps to cover their activities,
or even delete the audit trail.

7.
To protect the key configuration files at the operating system level,
the system administrator should ensure that UNIX file permissions and
the umask environment variable are set for the optimal combination of file restrictions in that environment.
The default value of umask is 022, but the UNIX system administrator responsible for that environment may
decide that a more restrictive value is appropriate.

8.
In Sun Solaris UNIX environments, a low level of security can be achieved using access control
utilities such as GETFACL and SETFACL. These access control list utilities are specific to the Sun Solaris UNIX platform

9.
Controlling access by using database object privileges is called DAC, or discretionary access control.
DAC controls access to any given object by granting specific privileges to user objects or roles.

10.
Giving a database user object the authority to perform INSERT or DELETE commands in a given table is an example of a privilege.
This privilege applies to a given user object, unlike a role which applies to a group of user objects.

11.
Virtual Private Database technology allows security access controls to be applied directly to views or tables.
Unlike other access control methods, defined access controls apply directly to the table or view, not the user object.

12.
Oracle Label Security provides fine-grained access control within the database by using access control tables and a security policy.
Label Security augments Virtual Private Databases to provide a tighter security for data.

13.
The transformation of data by using cryptography to make it unintelligible is known as encryption.
To encrypt a file is to render that file completely unreadable until it has been properly decrypted.

14.
DES and RC4 are examples of symmetric key encryption. 3DES, DES40 and RC2 are additional symmetrical encryption algorithms.

15.
Cryptography that requires key agreement, or keys on both sides of the session, is known as Diffie-Hellman cryptography.
This allows mutual authentication with the same common key. Advanced Security Option uses Diffie-Hellman cryptography.

16.
Cryptography that provides for private communications within a public network without trusting anyone to keep secrets is
called public key infrastructure, or PKI. HTTP and LDAP protocols are included within the public key infrastructure.

17.
The most widely used PKI application that supplies data integrity and encryption in the transport layer of the
Open Systems Interconnection (OSI) model is the secure sockets layer, or SSL, protocol.
SSL is typically used for authenticating servers and for the traffic encryption of credit cards and passwords.

18.
A data dictionary table called sys.aud$ is the database audit trail.
The database audit trail stores records which audit database statements, schema objects, and privileges.

19.
An entry in the operating system audit trail is always created when instance startup or instance shutdown occurs,
or when the sys user object logs in. The instance startup entry is necessary in order to
maintain a complete audit trail when the data dictionary is not available.

20.
The type of audit trail that efficiently consolidates audit records from multiple sources
(including Oracle databases and other applications which use the audit trail) is the operating system audit trail.
Operating system audit trails allow all audit records to reside in one place, including database audit trails.

21.
You can use Oracle Reports to create customized reports of audit information when the database audit trail is in use.
You can analyze database audit trail information and produce good reports from that analysis,
which is an advantage over using the operating system audit trail method.

22.
To protect the database audit trail from unauthorized deletions,
grant the Delete Any Table system privilege to security administrators only.
An unauthorized user with this system privilege can severely damage a database security trail, or even delete all the data.
Assign this privilege very carefully.

23.
Advanced Security Option provides a single source of integration with network encryption, single-sign-on services,
and security protocols. ASO is the centralized source for all of these security features.

24.
ASO ensures that data is not disclosed or stolen during Net8 transmissions by means of RSA encryption,
DES encryption, and Triple-DES encryption.

25.
The SSL feature of ASO allows you to use the SHA, or secure hash algorithm.
The SHA is slightly slower than MD5, but it is more secure against brute-force collision and inversion attacks.

26.
he SSO, or single sign-on, feature of ASO allows access to multiple accounts and applications with a single password.
SSO simplifies the management of user accounts and passwords for system administrators.

27.
LDAP stands for Lightweight Directory Access Protocol, which is a directory service standard based on the ISO X.500 specification.
LDAP is a protocol defined and maintained by the same task force which defined the HTTP and TCP/IP protocols.

28.
OID means Oracle Internet Directory, which is the LDAP directory available from Oracle.
OID is a directory service compliant with LDAP v. 3, and it offers scalability, security, and high availability.

29.
The scalability of OID allows thousands of LDAP clients to be connected together without harming performance.
Much of this scalability is accomplished using connection pooling and multithreaded server implementations.

30.
The Java-based tool for administering OID is called Directory Manager.
The Directory Manager tool provides administrative transparency for the Oracle environment,
and is based on Oracle Enterprise Manager.

32.
OID security controls data access at the authentication level, by using access control lists.
Data access is controlled with anonymous authentication methods, either password-based or certificate-based (through SSL).

33.
An enterprise user is defined and managed in a directory. All enterprise users have a unique identity which spans the enterprise.

34.
Enterprise User Security Management allows large user communities to access multiple applications with a single sign-on.
User credentials and authorizations are stored in a directory.
This allows single sign-ons using x.509v3 certificates over SSL.

35.
Groups of global roles are called enterprise roles, which are assigned to enterprise users in order to avoid
granting roles to hundreds or thousands of individual users.

36.
You can remove the need to create duplicate user objects in every database by using the shared schemas feature.
The benefit of shared schemas is fewer user accounts.

37.
The current user database link feature allows user objects to connect to another database instance as the procedure owner.
A current user database link requires global users and SSL.

38.
The Login server provides a single, enterprisewide authentication mechanism. This authentication mechanism allows users to
identify themselves securely to multiple applications through a single authentication step, or single sign-on (SSO).

39.
The single sign-on feature allows the storage of passwords in LDAP-compliant directory services such as Oracle Internet Directory.
Storing usernames and passwords in a directory improves efficiency by centralizing this administrative duty.

40.
A partner application can accept authentication directly from the Login server.
Partner applications are modified to work within the SSO framework.

41.
External applications are not modified to work within the SSO framework.
The Login server does not store the username and password, but only supplies this native information from the external application.
The benefits of LDAP directories are not available to external applications.

42.
During Oracle product installations, user objects are created with default passwords. SYS, SYSTEM,
and ORACLE are the most critical to examine, but all objects that may have default passwords should be examined.

43.
V_$PWFILE_USERS is the view that shows which user objects have been granted SYSDBA or SYSOPER privileges.
It is normal for INTERNAL and SYS objects to have the privileges, but suspect any other user objects that have these privileges.
When in doubt, revoke the privilege and monitor the change.

44.
Users with unlimited tablespace can accidentally or intentionally use 100 percent of available tablespace.
Review this ability by examining the DBA_TS_QUOTES view. User objects have unlimited tablespace
if that object displays MAX_BLOCKS or MAX_BYTES columns equal to -1.
Any user object that has this privilege should be examined closely for verification of need.

45.
Invoke SQL*Plus with the NOLOG switch to remove the plain-text password entry from the UNIX process table.
Sessions started with this /nolog SQL*Plus switch cannot reveal the password
when another session uses the Ps -ef|grep SQL*Plus command.

46.
The data dictionary view, DBA_ROLES, will reveal the names of all roles and their current password status.
It is a good view for reviewing any potential security risks related to roles and their respective passwords.
Review this view regularly to verify that these roles are not being misused,
and that a secure password policy is in place for all roles.

47.
Virtual Private Databases is a good security product but requires programming to implement.
Oracle Label Security provides similar row-level security out-of-the-box without this same need.
Oracle Label Security provides row-level security in databases without the need for programming that VPD requires.

48.
The Oracle Label Security administrative tool that allows you to quickly implement a security policy on a table is named Policy Manager.
Oracle Policy Manager allows administrators to use predefined security policies to quickly implement row-level security on any table.

49.
Oracle Label Security controls access to rows in database tables based on a label contained
in the row and the label privileges given to each user session. Beyond Directory Access Controls restrictions,
row-level security provides a finer level of security by using these two labels to implement further restrictions
and provide ease of administration.

50.
The user label specifies the data that a user or stored program unit has access to.
This is one element of security using Oracle Label Security.

51.
The row label specifies the sensitivity of the data placed under control. The row label has a different function than the user label.
The row label provides security on the data, not the user session or stored program unit.

52.
Oracle AUDIT performs the monitoring and recording of selected user database actions.
Oracle AUDIT is used to watch over user actions in a database instance.

53.
The AUDIT_TRAIL init.ora parameter is used to stop, start, and configure the AUDIT function for any given instance.
NONE is the default value of this parameter; the OS value of this parameter
enables all audit records to go to the operating system's audit trail,
and the dB value of this parameter enables database auditing.

54.
Minimize auditing. If only user login monitoring is required, listener log monitoring is an alternative to using AUDIT.
All sessions route through the listener, and an entry is made in the listener log for each session.

55.
To maintain optimal performance, you should periodically issue the SQL command truncate on the audit table. Old,
unnecessary data should be purged regularly. The length of time between truncate command invocations
that will maintain the optimal audit table size will vary by the volume of audit information retained.

56.
The most critical role to control is the DELETE_ANY_CATALOG role. Only DBAs should have this role.
This is key to protecting the audit trail. Restricting this role will ensure that the audit trail is protected from deletion.
Hackers will often remove or edit the audit trail to cover their activities.

57.
Advanced Security Option (ASO) encrypts all protocols in the database. Net8 connections to the database are encrypted,
as are all connections to the database.

58.
Data integrity is provided by the checksumming algorithm. The checksumming technique detects replay attacks,
where a valid $100.00 withdrawal is resubmitted 100 unauthorized times.

59.
DES is an example of native ASO cryptography. An example of an SSL cryptography that expands on DES is the 3 DES cryptography.
Triple Data Encryption Standard (DES) makes three passes during the cryptography process, providing a higher level of security.

60.
A system that uses polices and procedures to establish a secure information exchange is
called the public key infrastructure, or PKI.
Several elements of PKI include SSL, x.509v3 certificates, and the Certificate Authority.

61.
Benefits of using the public key infrastructure include the ability to scale to the Internet and accommodate millions of users.
Efficiency is paramount when millions of users are part of the community.

Practice:Demonstrating Oracle AUDIT Concepts and Procedures

This practice uses common UNIX and NT Oracle utilities to practice enabling AUDIT on a database.  You will:

  • See the procedure to enable and disable Oracle AUDIT on a database.
  • Understand the SQL commands used to audit a specific user schema object.
  • Investigate how to configure Audit to extend auditing into modified or new schema objects..

ASSUMPTIONS

  • This practice will reference SQL commands that function equally on UNIX operating systems, and the NT operating system, using SQL*Plus.
  • Results may vary slightly according to your Oracle environment.
  • Utilize the RealPlayer Demonstration in conjunction with this Practice, to further illustrate and guide this activity.
  • Login to your sqlplus session using the SYSTEM user object and the current password.

INSTRUCTIONS

1.

UNIX: Open a shell, login, locate and edit the initSID.ora file for your database:

NT: Use Window’ File Manager to locate the initSID.ora file for your database.

#audit_trail = true        # save,  original line

audit_trail = true         #activated for demonstration, rjm

 

Locate the line above, make a full copy of the line in the next newline, then uncomment (remove the # symbol) from the line.  Edit comments to reflect your reasons for the change.  Save the modified file, then shutdown/startup the instance.  Audit is now active on your database instance.

2.

UNIX: Open a shell, login, create a SQLPlus session with the SYSTEM connection.

NT: Create an SQLPlus session with SYSTEM connection.

sqlplus /nolog

 

SQL> audit select any table by scott;

 

Audit succeeded.

 

SQL> noaudit select any table by scott;

 

Noaudit succeeded.

 

SQL> audit all by scott;

 

Audit succeeded.

 

SQL> noaudit all by scott;

 

Noaudit succeeded.

 

SQL>

Now, all SELECT activity by the user Scott will be recorded in the audit trail, for our review later.

The NOAUDIT command following disables this selective monitoring once we have accumulated sufficient data to analyze.  The next commands will begin monitoring on ALL database activity for the user Scott, and then disables that same type of monitoring.

3.

UNIX: Open a shell, login, create a SQLPlus session with SYSTEM connection:

NT: Create a SQLPlus session with SYSTEM connection:

sqlplus /nolog

 

SQL> audit insert on default;

 

Audit succeeded.

 

SQL> audit delete on default;

 

Audit succeeded.

 

SQL> audit update on default;

 

Audit succeeded.

 

SQL> noaudit insert on default;

 

Noaudit succeeded.

 

SQL> noaudit delete on default;

 

Noaudit succeeded.

 

SQL> noaudit update on default;

 

Noaudit succeeded.

 

SQL>

These commands will extend INSERT, DELETE, UPDATE auditing to include future new or modified schema objects.

The second set of NOAUDIT commands disable those same audit actions.

Practice:Demonstrating Database User Objects, Roles and Permissions

This practice uses common UNIX and Windows NT Oracle utilities to review database user objects’ key roles and  permissions.  You will:

1.        See default user objects and their default passwords.

2.        Understand V$PWFILE_USER data dictionary view, and how to use this view to inspect SYSDBA or SYSOPER privileges assigned to user objects.

3.        Examine two key data dictionary views that reveal information about an important privilege for Oracle Audit.

4.        Investigate how to find user objects that are assigned unlimited tablespace in your active Oracle instances.

5.        Review the secure method for invoking SQLPlus, utilizing the /nolog command line switch.

ASSUMPTIONS

1.        This practice references SQL commands that function equivalently on the UNIX and NT operating systems.

2.        Results may vary slightly according to your Oracle environment.

3.        To further illustrate and guide this activity, view  this topic’s  Demo in conjunction with this Practice.

4.        Login to your sqlplus session using the SYSTEM user object and the current password.

INSTRUCTIONS

1.

UNIX: Open a shell, login, and type the following command:

NT: Open a DOS prompt, or invoke from the START/RUN pull down box.

 sqlplus system/<password>

                       

SQL> connect system/manager

Connected.

SQL> connect sys/change_on_install

Connected.

SQL> connect scott/tiger

Connected.

SQL>

Each connect command shows if the default password is in use with that particular default database user object.   Those objects using the default passwords should have their passwords changed.

2.

UNIX:  Open a shell, login, and type the following command

NT: Open a DOS prompt, or invoke from the START/RUN pull down box.

sqlplus system/<password> 

SQL> select * from v$pwfile_users;

 

USERNAME                       SYSDB SYSOP

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

INTERNAL                       TRUE      TRUE

SYS                            TRUE      TRUE

ROD                            TRUE      FALSE

 

SQL>

It is normal for INTERNAL or SYS to have the SYSOPER and/or SYSDBA privilege.   Any other user objects are suspect and should be scrutinized.

3.

UNIX: Open a shell, login, and type the following command:

NT: Open a DOS prompt, or invoke from the START/RUN pull down box.

 

sqlplus system/<current password> 

SQL> select role,privilege from role_sys_privs

       2  where role='DELETE_ANY_CATALOG';

no rows selected

 

SQL> select role,privilege from role_sys_privs

       2  where role='DELETE_ANY_CATALOG';

no rows selected

 

Any user objects that have the DELETE_ANY_CATALOG privilege should be scrutinized closely.  Verify the need for this object to have this privilege.  If in doubt, revoke the privilege.

4.

UNIX: Open a shell, login, and type this command

NT: Open a DOS prompt, or invoke from the START/RUN pull down box.

sqlplus system/<current password> 

SQL> select username,max_bytes,max_blocks from dba_ts_quotas;

 

USERNAME                        MAX_BYTES MAX_BLOCKS

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

RODOEMREPOS                            -1         -1

RODOEMREPOS                            -1         -1

 

SQL>

 

The numeric “-1” is the indication of unlimited tablespace assignments, whether in a MAX_BYTES or MAY_BLOCKS column.   Examine those usernames that display the “-1” value.   A username with unlimited tablespace privilege could accidentally or intentionally use 100% of that tablespace.

5.

UNIX: open a shell, login, and type the following command:

NT: no practice for this concept on NT.

sqlplus system/<current password> 

SQL> host ps -ef|grep sqlplus

 

The results of this UNIX command within sqlplus will display the sqlplus session with the SYSTEM user object’s password in plain text.  This is a security breach, and can be avoided with utilizing sqlplus in the NOLOG mode displayed next.

6.

UNIX: Open a shell, login, and type the following command:

NT: No practice for this concept on NT.

sqlplus system /nolog 

SQL> connect system/manager

Connected.

 

SQL> host ps -ef|grep sqlplus

 

The results of this UNIX command within sqlplus will display the sqlplus session with the SYSTEM user object’s password hidden.  The session now displays only the “/nolog” in the UNIX process table.   This keeps unauthorized users from gaining passwords by watching active sessions using the UNIX command “ps”.

Script: Computing Table Size

This script calculates the average row size for all tables in a schema.
It generates a script (getAvgCol.sql) from USER_TABLES and then runs it.
The following type of SELECT is generated for each table in USER_TABLES:

SELECT round(avg(nvl(vsize(COL1),0)) +
round(avg(nvl(vsize(COL2),0)) + … +
round(avg(nvl(vsize(COLn),0))

Where n=# of cols. on the table

Tables with LONG and LOB columns will not report row size properly. Also
tables with object types will throw the following error and will also not
report row size properly:

ORA-00932: inconsistent datatypes

=============
Sample Output
=============
ACCOUNTS 6
ACCTS 39
ACCT_ADDRS 38
BAD_DATA 116
BASE1 6
BONUS
CEG1 11
CHESS_SAVE
CHESS_SAVE_PLAYER
CITIES 36
COMPANY_SUMMARY 60
CR_FILES 113

Script:

SET ECHO off
REM NAME:   ROWSZ.SQL

drop table column_counts;
create table column_counts
        (
        table_name,
        column_count
        )
        as
        (
        select table_name, max(column_id)
        from user_tab_columns
        where data_type not like 'LONG%' AND table_name in
        (select table_name from user_tables)
        group by table_name
        )
        ;
set pages 0
set tab on
set trim on
set verify off
set feedback off
set termout off
set head off
set lines 100
set recsep off
set embedded on
spool getavgcol.sql
prompt column TB format A30
prompt set head off recsep off
prompt set lines 80 feedback off pages 0
prompt spool getavgcol
REM
column select_line format A8
column end_line format A1
column from_stmt format A34 word_wrap
column col_nm format A100
column col_val format A32
column tnm1 noprint
column tnmprint format A37
column column_id noprint
break on tnm1 skip 2
set null ''
clear breaks
select UTC.table_name tnm1,
        decode(column_id,1,'select ' || chr(39) || UTC.table_name || chr(39) ||
                ' TB, ', '        ') ||
        'round(avg(nvl(vsize('||column_name||'),0)),0)' ||
        decode(column_id,column_count, ' row_size from ' || UTC.table_name
             || ';'|| chr(10)||chr(10),
                ' +') col_nm
from user_tab_columns UTC, column_counts CC
where UTC.data_type not like 'LONG%' AND UTC.table_name = CC.table_name
order by UTC.table_name, UTC.column_id;
prompt spool off
prompt exit
spool off
drop table column_counts;
exit

Data Block Cache Header Format Changes (Oracle8 Physical layout)

Oracle8 has introduced a change with the data block cache header format.  The
basic idea is that incarnation and sequence numbers stored in the cache header
have been replaced with an SCN number and sequence number.  The size of the
cache header has remained 20 bytes.  The size of the block trailer is still 4
bytes.  Only the format of the cache header and the trailer has changed.

Oracle7 Implementation
~~~~~~~~~~~~~~~~~~~~~~

The current Oracle7 implementation stores the incarnation and sequence number
in the cache header of each data block to determine the current version of the
block.  A compressed version of the incarnation/sequence is maintained at the
end of the data block.  The incarnation and sequence numbers are each 4 byte
values.  The low-order 2 bytes of each value are stored as the last 4 bytes of
the data block.  This information is used to detect media corruption when
reading the block for normal operations or during recovery, or when validating
the block.  It allows for a consistency check to ensure the top of the block
is in sync with the bottom of the block.

See [NOTE:33242.1] for more details on the Oracle7 format.

Oracle8 Implementation
~~~~~~~~~~~~~~~~~~~~~~

The basic idea is to store the current redo generating SCN in the cache header
when making a change to a block.  Since multiple changes to a block can be
made at the same SCN, a sequence number is also stored in the cache header to
differentiate between different changes at the same SCN.  The sequence number
is increased each time a change is made at the same SCN.  The sequence number
is reset to 1 when making a change at a higher SCN than the SCN currently in
the block.

Oracle8 data block layout
-------------------------

        |   |   |   |   |   |   |   |   |   |   |   |   |   |   |   |   |
        | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |10 |11 |12 |13 |14 |15 |

        +-------+-------+---------------+---------------+---------------+
        |Typ|Fmt|Filler |     RDBA      |    SCNBase    |SCNWrap|Seq|Flg|
        |-------+-------+---------------+---------------+---------------|
        |ChkVal |Filler | <------                                       |
        |-------+-------+                                               |
        :                       Body of                                 :
        :                        Data Block                             :
        :                                                               :
        |                                               +---------------|
        |                                    ---------> |     Tail      |
        +---------------------------------------------------------------+

        | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | A | B | C | D | E | F |
        |   |   |   |   |   |   |   |   |   |   |   |   |   |   |   |   |

    The bytes in the header are defined as:

        Typ     Block Type (defined in k.h).

        Fmt     Block format.  In Oracle8 this is 2, converted on-the-fly.

        Filler  Not used currently.

        RDBA    Relative database address of the block.

        SCNBase SCN Base

        SCNWrap SCN Wrap

        Seq     Sequence number.  Incremented for every change made to the
                block at the same SCN.

        Flg     Flag (defined in kcbh.h)

        ChkVal  Optional check value for the block.  Set so that an xor of all
                the ub2 values in the block equals zero.  This may require the
                value to be zero (unlike Oracle7).  ChkVal will be verified
                only if KCBHFCKV is set in the Flg field.
                   - always for file header blocks
                   - if the init.ora <parameter:DB_BLOCK_CHECKSUM> is set.

        Tail    Consistency information used to verify the beginning and the
                end of the block are of the same version.  Lower order 2 bytes
                of SCNBase, plus block Type, plus SCN Seq number.

Migration to the New Format
~~~~~~~~~~~~~~~~~~~~~~~~~~~

Data block cache headers will be upgraded to the new Oracle8 format on-the-fly
when the block is read into the cache.  Oracle8 will do this automatically for
every block that is read into the cache during normal operation. The on-disk
copy is not updated unless the block becomes dirty.

Soft Corrupt in Oracle8
~~~~~~~~~~~~~~~~~~~~~~~

  Soft corrupt blocks in Oracle8 have:
     Seq# is 0xff
     flg  is 0x00

SCRIPT – to Tune the 'SESSION_CACHED_CURSORS' and 'OPEN_CURSORS' Parameters

Script:

select
  'session_cached_cursors'  parameter,
  lpad(value, 5)  value,
  decode(value, 0, '  n/a', to_char(100 * used / value, '990') || '%')  usage
from
  ( select
      max(s.value)  used
    from
      v$statname  n,
      v$sesstat  s
    where
      n.name = 'session cursor cache count' and
      s.statistic# = n.statistic#
  ),
  ( select
      value
    from
      v$parameter
    where
      name = 'session_cached_cursors'
  )
union all
select
  'open_cursors',
  lpad(value, 5),
  to_char(100 * used / value,  '990') || '%'
from
  ( select
      max(sum(s.value))  used
    from
      v$statname  n,
      v$sesstat  s
    where
      n.name in ('opened cursors current', 'session cursor cache count') and
      s.statistic# = n.statistic#
    group by
      s.sid
  ),
  ( select
      value
    from
      v$parameter
    where
      name = 'open_cursors'
  )
/

Sample Output:

PARAMETER              VALUE      USAGE
---------------------- ---------- -----
session_cached_cursors    20       100%
open_cursors             300        16%

select
  to_char(100 * sess / calls, '999999999990.00')||'%' cursor_cache_hits,
  to_char(100 * (calls - sess - hard) / calls, '999990.00')||'%' soft_parses,
  to_char(100 * hard / calls, '999990.00')||'%' hard_parses
from
  ( select sum(value) calls from v$sysstat
      where name in ('parse count (total)', 'parse count (failures)') ),
  ( select value hard from v$sysstat where name = 'parse count (hard)' ),
  ( select value sess from v$sysstat where name = 'session cursor cache hits' )
/

select
  to_char(100 * sess / calls, '999999999990.00')||'%' cursor_cache_hits,
  to_char(100 * (calls - sess - hard) / calls, '999990.00')||'%' soft_parses,
  to_char(100 * hard / calls, '999990.00')||'%' hard_parses
from
  ( select sum(value) calls from v$mystat m, v$statname s
    where m.statistic# = s.statistic# and
          name in ('parse count (total)', 'parse count (failures)') ),
  ( select value hard from v$mystat m, v$statname s
    where m.statistic# = s.statistic# and name = 'parse count (hard)' ),
  ( select value sess from v$mystat m, v$statname s
    where m.statistic# = s.statistic# and name = 'session cursor cache hits' )
/

SCRIPT TO GENERATE SQL*LOADER CONTROL FILE

This script prepares a SQL*Loader control file for a table already existing in the database. The script accepts
the table name and automatically creates a file with the table name and extension ‘ctl’.  This is specially
useful if you have the DDL statement to create a particular table and have a free-format ASCII-delimited file but
have not yet created a SQL*Loader control file for the loading operation.

Default choices for the file are as follows (alter to your needs):

Delimiter:              comma (‘,’)
INFILE file extension:  .dat
DATE format:            ‘MM/DD/YY’

You may define the Loader Data Types of the other Data Types by revising the DECODE function pertaining
to them.

Please note:
The name of the table to be unloaded needs to be provided when the script is executed as follows:

Script:

set echo off
set heading off
set verify off
set feedback off
set show off
set trim off
set pages 0
set concat on
set lines 300
set trimspool on
set trimout on

spool &1..ctl

select 'LOAD DATA'||chr (10)||
       'INFILE '''||lower (table_name)||'.dat'''||chr (10)||
       'INTO TABLE '||table_name||chr (10)||
       'FIELDS TERMINATED BY '','''||chr (10)||
       'TRAILING NULLCOLS'||chr (10)||'('
from   all_tables
where  table_name = upper ('&1');

select decode (rownum, 1, '   ', ' , ')||
       rpad (column_name, 33, ' ')||
       decode (data_type,
               'VARCHAR2', 'CHAR NULLIF ('||column_name||'=BLANKS)',
               'FLOAT',    'DECIMAL EXTERNAL NULLIF('||column_name||'=BLANKS)',
               'NUMBER',   decode (data_precision, 0,
                           'INTEGER EXTERNAL NULLIF ('||column_name||
                           '=BLANKS)', decode (data_scale, 0,
                           'INTEGER EXTERNAL NULLIF ('||
                           column_name||'=BLANKS)',
                           'DECIMAL EXTERNAL NULLIF ('||
                           column_name||'=BLANKS)')),
               'DATE',     'DATE "MM/DD/YY"  NULLIF ('||column_name||'=BLANKS)',
               null)
from   user_tab_columns
where  table_name = upper ('&1')
order  by column_id;

select ')'
from sys.dual;
spool off

Sample Output:

LOAD DATA
INFILE 'tv.dat'
INTO TABLE TV
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(  T1                               INTEGER EXTERNAL NULLIF (T1=BLANKS)
 , T2                               CHAR NULLIF (T2=BLANKS)
 , T3                               CHAR NULLIF (T3=BLANKS)
)

沪ICP备14014813号

沪公网安备 31010802001379号