1 person found this helpful
sid is "session identifier" which represents a session
A transaction is a logical, atomic unit of work that contains one or more SQL statements. A transaction groups SQL statements so that they are either allcommitted, which means they are applied to the database, or all rolled back, which means they are undone from the database. Oracle Database assigns every transaction a unique identifier called a transaction ID.
V$SESSION_LONGOPSdisplays the status of various operations that run for longer than 6 seconds (in absolute time). These operations currently include many backup and recovery functions, statistics gathering, and query execution, and more operations are added for every Oracle release.
1 person found this helpful
Didn't completely get it but i am guessing you want details of a query how it ran in past 10 days
you can get its sql_id from its current session or from the awr report during the duration in which it ran and then with the help of that sql_id
you can pull awrsqrpt.sql report (ORACLE_HOME/rdbms/admin/awrsqrpt.sql) .
1. Is there any data dictionary table to record history of transactions data? For example, I want to look at past 10 days transactions.
By default, oracle does not store this history. Imagine if you have a very busy OTLP system with thousands of small transactions each day. The overhead alone of capturing and maintaining this, for a whole database would be nothing small.
However, a number of features can track transaction history (DML or DDL) when enabled. You can use AUDIT to capture all transactions done against a table, or if you want to go further than that, you can also use flashback data archive. Flashback enables you to query the transaction history of a table, but not only that, it enables you to query the table as if it was frozen at any time in the past. (depending on the retention you set)
2. So far I know v$transaction and v$session, they only store current trans data, once transaction is done, the trans record in data dictionary table disappears.
3. table V$SESSION_LONGOPS see following
V$SESSION_LONGOPS does not show transaction information. It shows long duration operations (hence longops). It shows things like, full table scans, large joins being done in temp tablespace, large sorts, etc. This is good for monitoring long running queries and backups.
V$TRANSACTION is the view that tracks active transactions on the system.
There's no data dictionary table. You can audit, which is resource intensive and has to be set up, or you can use the feature that enables Oracle to redo transactions in case there is a crash, called redo. If you have a proper production system, the redo logs are archived, and you can mine them with logminer. You can also flashback as Alvaro mentioned, if you are set up for that.
What you really are looking for in those 10 days determines what is best for you. Baris gave you the official definition of transaction, but many applications do not properly map transactions with what a business considers a transaction. For one example, sometimes an app will allow a trial of many things being updated, then rollback. There was a transaction active, but as far as the business was concerned there was no transaction. And as far as the app is concerned afterwards, it never happened. But the database would disagree. Things can get real confusing when people throw in autonomous transactions.