473,324 Members | 2,166 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

difference between oracle 9i and 10g

3
Hi,

I want to know the difference between oracle 9i and 10g.


thanks and regards
sankar
Oct 26 '07 #1
3 11258
amitpatel66
2,367 Expert 2GB
Hi,

I want to know the difference between oracle 9i and 10g.


thanks and regards
sankar
Check here for 8i,9i,10g,11g features!!
Oct 26 '07 #2
gintsp
36
Hi,

I want to know the difference between oracle 9i and 10g.


thanks and regards
sankar
Each version of oracle docs contains New features document.

Gints Plivna
http://www.gplivna.eu
Oct 28 '07 #3
Hi Sankar,



I have ne useful new features of Oracle 10g below ,but i have't mentioned many important things ..



Flashback Versions Query



SQL> desc rates

Name Null? Type

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

CURRENCY VARCHAR2(4)

RATE NUMBER(15,10)

This table shows the exchange rate of US$ against various other currencies as shown in the CURRENCY column. In the financial services industry,

exchange rates are not merely updated when changed; rather, they are recorded in a history. This approach is required because bank transactions

can occur as applicable to a "past time," to accommodate the loss in time because of remittances. For example, for a transaction that occurs at

10:12AM but is effective as of 9:12AM, the applicable rate is that at 9:12AM, not now.

Up until now, the only option was to create a rate history table to store the rate changes, and then query that table to see if a history is available.

Another option was to record the start and end times of the applicability of the particular exchange rate in the RATES table itself. When the change

occurred, the END_TIME column in the existing row was updated to SYSDATE and a new row was inserted with the new rate with the END_TIME

as NULL.

In Oracle Database 10g, however, the Flashback Versions Query feature may obviate the need to maintain a history table or store start and end

times. Rather, using this feature, you can get the value of a row as of a specific time in the past with no additional setup. Bear in mind, however,

that it depends on the availability of the undo information in the database, so if the undo information has been aged out, this approach will fail.

For example, say that the DBA, in the course of normal business, updates the rate several times—or even deletes a row and reinserts it:

insert into rates values ('DOLL',1.1012);

commit;

update rates set rate = 1.1014;

commit;

update rates set rate = 1.1013;

commit;

delete rates;

commit;

insert into rates values ('DOLL',1.1016);

commit;

update rates set rate = 1.1011;

commit;

After this set of activities, the DBA would get the current committed value of RATE column by

SQL> select * from rates;

CURR RATE

---- -----

DOLL 1.1011

This output shows the current value of the RATE, not all the changes that have occurred since the first time the row was created. Thus using

Flashback Query, you can find out the value at a given point in time; but we are more interested in building an audit trail of the changes—somewhat

like recording changes through a camcorder, not just as a series of snapshots taken at a certain point.

The following query shows the changes made to the table:

select versions_starttime, versions_endtime, versions_xid,

versions_operation, rate

from rates versions between timestamp minvalue and maxvalue

order by VERSIONS_STARTTIME

/

VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V RATE

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

01-DEC-03 03.57.12 PM 01-DEC-03 03.57.30 PM 0002002800000C61 I 1.1012

01-DEC-03 03.57.30 PM 01-DEC-03 03.57.39 PM 000A000A00000029 U 1.1014

01-DEC-03 03.57.39 PM 01-DEC-03 03.57.55 PM 000A000B00000029 U 1.1013

01-DEC-03 03.57.55 PM 000A000C00000029 D 1.1013

01-DEC-03 03.58.07 PM 01-DEC-03 03.58.17 PM 000A000D00000029 I 1.1016

01-DEC-03 03.58.17 PM 000A000E00000029 U 1.1011

Note that all the changes to the row are shown here, even when the row was deleted and reinserted. The VERSION_OPERATION column shows

what operation (Insert/Update/Delete) was performed on the row. This was done without any need of a history table or additional columns.

In the above query, the columns versions_starttime, versions_endtime, versions_xid, versions_operation are pseudo-columns, similar to other

familiar ones such as ROWNUM, LEVEL. Other pseudo-columns—such as VERSIONS_STARTSCN and VERSIONS_ENDSCN—show the

System Change Numbers at that time. The column versions_xid shows the identifier of the transaction that changed the row. More details about the

transaction can be found from the view FLASHBACK_TRANSACTION_QUERY, where the column XID shows the transaction id. For instance,

using the VERSIONS_XID value 000A000D00000029 from above, the UNDO_SQL value shows the actual statement.

SELECT UNDO_SQL

FROM FLASHBACK_TRANSACTION_QUERY

WHERE XID = '000A000D00000029';

UNDO_SQL

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

insert into "ANANDA"."RATES"("CURRENCY","RATE") values ('DOLL','1.1013');

In addition to the actual statement, this view also shows the timestamp and SCN of commit and the SCN and timestamp at the start of the query,

among other information.

Finding Out Changes During a Period

Now, let's see how we can use the information effectively. Suppose we want to find out the value of the RATE column at 3:57:54 PM. We can issue:

select rate, versions_starttime, versions_endtime

from rates versions

between timestamp

to_date('12/1/2003 15:57:54','mm/dd/yyyy hh24:mi:ss')

and to_date('12/1/2003 16:57:55','mm/dd/yyyy hh24:mi:ss')

/

RATE VERSIONS_STARTTIME VERSIONS_ENDTIME

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

1.1011

This query is similar to the flashback queries. In the above example, the start and end times are null, indicating that the rate did not change during

the time period; rather, it includes a time period. You could also use the SCN to find the value of a version in the past. The SCN numbers can be

obtained from the pseudo-columns VERSIONS_STARTSCN and VERSIONS_ENDSCN. Here is an example:

select rate, versions_starttime, versions_endtime

from rates versions

between scn 1000 and 1001

/

Using the keywords MINVALUE and MAXVALUE, all the changes that are available from the undo segments is displayed. You can even give a

specific date or SCN value as one of the end points of the ranges and the other as the literal MAXVALUE or MINVALUE. For instance, here is a

query that tells us the changes from 3:57:52 PM only; not the complete range:

select versions_starttime, versions_endtime, versions_xid,

versions_operation, rate

from rates versions between timestamp

to_date('12/11/2003 15:57:52', 'mm/dd/yyyy hh24:mi:ss')

and maxvalue

order by VERSIONS_STARTTIME

/

VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V RATE

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

01-DEC-03 03.57.55 PM 000A000C00000029 D 1.1013

01-DEC-03 03.58.07 PM 01-DEC-03 03.58.17 PM 000A000D00000029 I 1.1016

01-DEC-03 03.58.17 PM 000A000E00000029 U 1.1011

Final Analysis

Flashback Versions Query replicates the short-term volatile value auditing of table changes out of the box. This advantage enables the DBA to get

not a specific value in the past, but all the changes in between, going as far bask as the available data in undo segments. Therefore, the maximum

available versions are dependent on the UNDO_RETENTION parameter.

How Much Longer?: Rollback Monitoring

Give users an accurate estimate of the duration of a rollback operation

Are we there yet? How much longer?

Sound familiar? These questions may come from the back seat on your way to the kids' favorite theme park, often incessantly and with increasing

frequency. Wouldn't you want to tell them exactly how much longer it will take—or better yet, know the answer yourself?

Similarly, when a long, running transaction has been rolled back, there are often several users breathing down your neck asking the same

questions. The questions are justified, because the transaction holds the locks and normal processing often suffers as the rollback progresses.

In Oracle 9i Database and below, you can issue the query

SELECT USED_UREC

FROM V$TRANSACTION;

which returns the number of undo records used by the current transaction, and if executed repeatedly, will show continuously reduced values

because the rollback process will release the undo records as it progresses. You can then calculate the rate by taking snapshots for an interval and

then extrapolate the result to estimate the finishing time.

Although there is a column called START_TIME in the view V$TRANSACTION, the column shows only the starting time of the entire transaction

(that is, before the rollback was issued). Therefore, extrapolation aside, there is no way for you to know when the rollback was actually issued.

Extended Statistics for Transaction Rollback

In Oracle Database 10g, this exercise is trivial. When a transaction rolls back, the event is recorded in the view V$SESSION_LONGOPS, which

shows long running transactions. For rollback purpose, if the process takes more than six seconds, the record appears in the view. After the

rollback is issued, you would probably conceal your monitor screen from prying eyes and issue the following query:

select time_remaining

from v$session_longops

where sid = <sid of the session doing the rollback>;

Now that you realize how important this view V$SESSION_LONGOPS is, let's see what else it has to offer. This view was available pre-Oracle

Database 10g, but the information on rollback transactions was not captured. To show all the columns in a readable manner, we will use the

PRINT_TABLE function described by Tom Kyte at AskTom.com. This procedure simply displays the columns in a tabular manner rather than the

usual line manner.

SQL> set serveroutput on size 999999

SQL> exec print_table('select * from v$session_longops where sid = 9')

SID : 9

SERIAL# : 68

OPNAME : Transaction Rollback

TARGET :

TARGET_DESC : xid:0x000e.01c.00000067

SOFAR : 10234

TOTALWORK : 20554

UNITS : Blocks

START_TIME : 07-dec-2003 21:20:07

LAST_UPDATE_TIME : 07-dec-2003 21:21:24

TIME_REMAINING : 77

ELAPSED_SECONDS : 77

CONTEXT : 0

MESSAGE : Transaction Rollback: xid:0x000e.01c.00000067 :

10234 out of 20554 Blocks done

USERNAME : SYS

SQL_ADDRESS : 00000003B719ED08

SQL_HASH_VALUE : 1430203031

SQL_ID : 306w9c5amyanr

QCSID : 0

Let's examine each of these columns carefully. There may be more than one long running operation in the session—especially because the view

contains the history of all long running operations in previous sessions. The column OPNAME shows that this record is for "Transaction Rollback,"

which points us in the right direction. The column TIME_REMAINING shows the estimated remaining time in seconds, described earlier and the

column ELAPSED_SECONDS shows the time consumed so far.

So how does this table offer an estimate of the remaining time? Clues can be found in the columns TOTALWORK, which shows the total amount of

"work" to do, and SOFAR, which shows how much has been done so far. The unit of work is shown in column UNITS. In this case, it's in blocks;

therefore, a total of 10,234 blocks have been rolled back so far, out of 20,554. The operation so far has taken 77 seconds. Hence the remaining

blocks will take:

77 * ( 10234 / (20554-10234) ) ??77 seconds

But you don't have to take that route to get the number; it's shown clearly for you. Finally, the column LAST_UPDATE_TIME shows the time as of

which the view contents are current, which will serve to reinforce your interpretation of the results.

SQL Statement

Another important new piece of information is the identifier of the SQL statement that is being rolled back. Earlier, the SQL_ADDRESS and

SQL_HASH_VALUE were used to get the SQL statement that was being rolled back. The new column SQL_ID corresponds to the SQL_ID of the

view V$SQL as shown below:

SELECT SQL_TEXT

FROM V$SQL

WHERE SQL_ID = <value of SQL_ID from V$SESSION_LONGOPS>;

This query returns the statement that was rolled back, thereby providing an additional check along with the address and hash value of the SQL

statement.

Parallel Instance Recovery

If the DML operation was a parallel operation, the column QCSID shows the SID of the parallel query server sessions. In the event of a parallel

rollback, such as during instance recovery and subsequent recovery of a failed transaction, this information often comes in handy.

For example, suppose that during a large update the instance shuts down abnormally. When the instance comes up, the failed transaction is rolled

back. If the value of the initialization parameter for parallel recovery is enabled, the rollback occurs in parallel instead of serially, as it occurs in

regular transaction rollback. The next task is to estimate the completion time of the rollback process.

The view V$FAST_START_TRANSACTIONS shows the transaction(s) occurring to roll-back the failed ones. A similar view, V

$FAST_START_SERVERS, shows the number of parallel query servers working on the rollback. These two views were available in previous

versions, but the new column XID, which indicates transaction identifier, makes the joining easier. In Oracle9i Database and below, you would have

had to join the views on three columns (USN - Undo Segment Number, SLT - the Slot Number within the Undo Segment, and SEQ - the sequence

number). The parent sets were shown in PARENTUSN, PARENTSLT, and PARENTSEQ. In Oracle Database 10g, you only need to join it on the

XID column and the parent XID is indicated by an intuitive name: PXID.

The most useful piece of information comes from the column RCVSERVERS in V$FAST_START_TRANSACTIONS view. If parallel rollback is

going on, the number of parallel query servers is indicated in this column. You could check it to see how many parallel query processes started:

select rcvservers from v$fast_start_transactions;

If the output shows just 1, then the transaction is being rolled back serially by SMON process--obviously an inefficient way to do that. You can

modify the initialization parameter RECOVERY_PARALLELISM to value other than 0 and 1 and restart the instance for a parallel rollback. You can

then issue ALTER SYSTEM SET FAST_START_PARALLEL_ROLLBACK = HIGH to create parallel servers as much as 4 times the number of

CPUs.

If the output of the above query shows anything other than 1, then parallel rollback is occurring. You can query the same view (V

$FAST_START_TRANSACTIONS) to get the parent and child transactions (parent transaction id - PXID, and child - XID). The XID can also be

used to join this view with V$FAST_START_SERVERS to get additional details.

Conclusion

In summary, when a long-running transaction is rolling back in Oracle Database 10g—be it the parallel instance recovery sessions or a user issued

rollback statement—all you have to do is to look at the view V$SESSION_LONGOPS and estimate to a resolution of a second how much longer it

will take.

Thanks & Regards,
Vinod Sadanandan
Oracle DBA
Oct 30 '07 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

34
by: yensao | last post by:
Hi, I have a hard time to understand difference and similarities between Relational database model and the Object-Oriented model. Can somebody help me with this? Thank you in advance. ...
1
by: MartyP | last post by:
I am using SQL 2000 and/or SQL 2005 to link to an Oracle Db using OPENQUERY. The SQL 2000 box is SP4 on Win2000. I can run a query thru Q.A. for SQL 2000 or I can run an Oracle query thru...
4
by: VIKAS17786 | last post by:
COULD YOU EXPLAIN ME WHAT IS DIFFERENCE BETWEEN ORACLE 8i AND ORACLE 9i ? ........................... ALSO WHAT IS DIFFERENCE BETWEEN DBMS AND RDBMS ? ..... I...
2
by: Devika Apk | last post by:
.) I have installed oracle client in my machine but i'm not able to export the data's from another user. .) I created a new datacase using oracle assistants but i'm not able to create a user for...
0
kiss07
by: kiss07 | last post by:
Hi friends, I have one doubt , whts difference between Time and Timestamp data type in Oracle 9i. Thanks, Kiss07.
2
by: rajaraj | last post by:
pls tell me differece between oracle 8i and oracle 9i I also want the enhanced features in oracle 9i rathere than oracle 8i and wt commands to be used in oracle 9i other than oracle 8i
3
by: Wolfgang Meister | last post by:
In order to retrieve a NUMBER value out of a column from an Oracle Table iinto a local variable there are two functions: Decimal var = reader.GetDecimal(1); or Decimal var =...
5
by: bhushanbagul | last post by:
Hi All Please let me know exactly what difference i and g makes in oracle version i.e. when we say oracle 10g or oracle 11i Thanks Bhushan
6
Inbaraj
by: Inbaraj | last post by:
Hi.... Can any one say me the difference between the Oracle 9i and 10g. I was asked this question in the interview can any one say me the difference.... Thanks in advance... with reg...
3
by: barrathi | last post by:
Hai all, can you explain DIFFERENCE BETWEEN ORACLE 9I AND ORACLE 10G? thanks in advance.
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.