Scn and rowid in oracle

The scn and rowid in oracle explain the two most important concepts in oracle: scn and rowidscn www.2cto.com Scn: a number that is automatically maintained by the DBMS after the Oracle database is updated. In Oracle, there are four important SCN types: System checkpoint SCN, data file checkpoint SCN, startup SCN, and termination SCN. The oracle system must have a number corresponding to everything, which is equivalent to the internal time of the database. The number is in the front and rear of the database. It is transparent to users, and the management of scn is completely managed by oracle. 1. When a checkpoint is completed, Oracle stores the scn of the system checkpoint in the controlfile. A Checkpoint can be understood as a database storing data blocks in the memory according to a certain rule (not accurate. See the latest checkpoint scnselect checkpoint_change # from v $ database; 2. Data File checkpoint scn. After a checkpoint is completed, oracle separately stores the scn of each data file in the controlfile. It is mainly used for instance recovery. It will check the scnselect name, checkpoint_change # from v $ datafile of the data file by referring to the scn Number of the control file for corresponding processing (Major recovery and rollback; 3. Start scn Oracle to store the checkpoint's scn in the header of each data file. This value is called the startup scn because it is used when the database instance is started, check whether database recovery is required. It mainly compares with the scn of each data file in the control file to see if it needs to be restored. 4. Terminate scn each data file's terminate scn is stored in the controlfile. Www.2cto.com view the current scn number Select current_scn from v $ database; the above several scn numbers are mainly used for oracle recovery and backup, and some are automatically completed by oracle, some of them need to be manually operated based on the above several scn numbers. Therefore, the above several scn are very important. You must understand and search for them. Generally, it is used together with log. Here I only list the scn numbers for log query. The specific operations will be explained later. Select group #, first_change # from v $ log; the unique row number of each record in the RowidRowid database, it is an 18-bit 64-in structure and is calculated based on the location where the row exists. Rowid essentially limits the size of database files and the number of tablespace files. The rowid pseudo column is not stored in the database. It is not the database data. This is from the logic structure of the database and table. In fact, in the physical structure, each row is composed of one or more row pieces. the header of each row piece contains the address of this piece, that is, rowid. in this sense, rowid still occupies disk space. select ROWID from scott. emp where rownum <2; ROWID -------------------- AAABnlAAFAAAAAPAAA ROWID format: Data Object number file number block number row number Oooooooo fff bbbbbb rrr we can see from the rowid above, we can know: AAABnl is the data object number (j is the table, index, etc.) AAF is the relevant file number AAAAAP is the block number AA A is the row number. The rowid function is used to process rowid. 1 Dbms_rowid.rowid_object (rowid) is used to obtain the row's object number 2 bytes (rowid) rfile # Get the row's file number 3 dbms_rowid.rowid_block_number) block # obtain the block number of the row 4 dbms_rowid.rowid_row_number (rowid) row #. Wait until the row number 5 dbms_rowid.rowid_to_absolute_fno (rowid, 'sys ', 'test ') file # You can use the above functions to perform special processing on a table. Some people can easily confuse rowid and rownum. These two concepts are completely different. rowid is the unique identifier for recording a row of data and is inconvenient, rownum is the sequence in which you extract data from the database. It can be different for each query. It is a temporary parameter.