Use of several common pseudo columns in Oracle (pseudo-column)

Oracle's pseudo-column is often translated into pseudo columns, and someone translates it into virtual fields. Data operations often bring unexpected results.
Frequently Used
Sysdate, policimestamp, rownum, rowid, currval, nextval, uid, user, level, ora_rowscn

1. sysdate, policimestamp,
Sysdate returns the current system time. Systimestamp returns the current date and time sysdate using timestamp with timezone. The systimestamp is the time of the database's time zone.

Select sysdate from dual
Get current server time
Result: 8:39:28

Select sysdate, sysdate-interval '1' minute from dual
Get the current server minus 1 minute
Result: 1 8:40:28 8:39:28

Select sysdate, sysdate-interval '1' hour from dual
Get the current server time minus 1 hour
Result: 1 8:40:53 7:40:53

Select sysdate, sysdate-interval '1' day from dual
Get the current server time minus the time of 1 day
Result: 1 8:41:19 8:41:19

Select sysdate, sysdate-interval '1' month from dual
Get the current server time minus the time of January 1, January
Result: 1 8:41:38 8:41:38

Select sysdate, sysdate-interval '1' year from dual
Current server time minus 7 years
Result: 1 8:41:50 8:41:50

Select sysdate, sysdate-7 * interval '1' hour from dual
Multiply by a number according to the time interval
Result: 1 8:42:11 1:42:11 (7 hours interval)

The preceding result can also be used to obtain the corresponding time through the four arithmetic operations of the date.

Select sysdate, sysdate-1 from dual;
By default, one is subtracted from the current server by one day.
Result: 1 8:41:19 8:41:19

Select sysdate, sysdate-1/24 from dual;
By default, the system minus 1/24 is the current server minus one hour.
Result: 1 8:40:53 7:40:53

Select sysdate, sysdate-1/24/60 from dual;
By default, the system will subtract 1/24/60 from the current server minus one minute.
Result: 1 8:40:28 8:39:28

Select sysdate-1/24/60/60 from dual;
By default, 1/24/6/600 is subtracted from the current server by one second.
Result: 1 8:41:19 8:40:19

Other date formatting is not described

(2) systimestamp is a function that returns the date of the current system.

Select distinct imestamp from dual;
Get current database time
Result: 1 month,-09 08.38.29.406000 AM +

Select to_char (systimestamp, 'yyyy-MM-DD hh24: MI: ss') from dual;
Display the current date according to the format
Result: 1 08:52:45

Select to_char (systimestamp, 'ssss. ff') from dual;
Convert to the corresponding character type to obtain the current time in milliseconds
Result: 1 3939.125000

Select
Extract (year from Hangzhou imestamp) ey,
Extract (month from Hangzhou imestamp) Em,
Extract (day from your imestamp) Ed,
Extract (hour from policimestamp) Eh,
Extract (minute from policimestamp) Em,
Extract (second from policimestamp) es,
Extract (timezone_hour from policimestamp) Th,
Extract (timezone_minute from policimestamp) TM,
Extract (timezone_region from systimestamp) tr,
Extract (timezone_abbr from paiimestamp) Ta
From dual;
Result: 1 2009 7 14 0 47 38.546 8 0 unknown unk

2. rownum, rowid

Rownum is a sequence in which Oracle databases read data from data files or buffers. If it obtains the first record, the rownum value is 1, the second record is 2, and so on. If you use>,> =, =,... and, because the rownum of the first record obtained from the buffer or data file is 1, it is deleted and then removed. However, its rownum is still 1 and deleted, and so on, no data is available. rownum is the serial number of the matching result. It always starts from 1. Therefore, the result you selected cannot be less than 1, and there are other values greater than 1.

Rowid can be said to exist physically, indicating the unique location ID of the record in the tablespace, which is unique in the DB. As long as the record has not been moved, the rowid remains unchanged. Compared with a table, rowid is similar to a common column in a table. Therefore, if rowid is used as a condition, rownum does not occur.

Obtain the first 10 records
Select rowid, rownum, grade from ubs_grade where rownum <= 10;

Obtain 3-5 records in the table:
Select * from (
Select rownum R, grade from ubs_grade
Where rownum <= 5
Order by grade)
Where R> 2

Result:
1 3 B
2 4 B
3 5 C

Select rowid, rownum, grade from ubs_grade where rownum between 1 and 10;
Result:
1 aaancoaafaaaadyaaa 1
2 aaancoaafaaaadyaab 2
3 aaancoaafaaaadyaac 3 B
4 aaancoaafaaaadyaad 4 B
5 aaancoaafaaaadyaae 5 C

3 currval and nextval should be used with sequence.

Nextval or currval must be defined by the sequence names (or synonyms) that reside in the same database in the sequence. nextval or sequence. currval format. The first value returned by nextval is the initial value. The subsequent nextval will automatically increase the value of your defined increment by and then return the added value. Currval always returns the value of the current sequence, but currval can be used only after the first nextval initialization; otherwise, an error will occur. Nextval increases the sequence value once. Therefore, if you use multiple nextval values in the same statement, the values are different.

Create a serial number first
Create sequence ubs_sequence
Increment by 1 -- add several
Start with 1 -- count from 1
Nomaxvalue -- do not set the maximum value
Nocycle -- always accumulate without repeating
Cache 10;

Once ubs_sequence is defined, you can use currval, nextval
Currval = returns the current Sequence Value
Nextval = increase the sequence value, and then return the Sequence Value
For example:
Ubs_sequence.currval
Ubs_sequence.nextval

3. uid, user,
Returns the user ID of the database user, which is not very practical.
Select uid, user from dual;
Result: 1 55 dezai

4. Level
Level is only used in select statements that execute hierarchy tree traversal on a table.

5. ora_rowscn
Oracle will record the last changed SCN of each record in the table and execute the lock (select... For update), or incremental data extraction is useful. By default, ora_rowscn for each row of records is block-based, unless you execute create table... Rowdependencies ).
By default, all records in each block have the same ora_rowscn. When any record in the block changes, the ora_rowscn of all records in the block changes to the latest value, the minimum granularity of ora_rowscn is block.

Others
1. SQL> select sys_guid () from dual;
Result:
Sys_guid ()
--------------------------------
45690ea57a5f47fabe6f77c17980abc0

2. Obtain the local IP address and Server IP Address:
SQL> select sys_context ('userenv', 'IP _ address'), utl_inaddr.get_host_address from dual;
Result:
Sys_context ('userenv', 'IP _ ADDR get_host_address
----------------------------------------------------------------------
172.28.2.33

3. Random Number Generation: (between and)
Select round (dbms_random.value (100,200) from duals

4. nlssort (), used for language sorting
Pinyin
Select * from team order by nlssort (sorting field name, 'nls _ sort = schinese_pinyin_m ')
Stroke
Select * from team order by nlssort (sorting field name, 'nls _ sort = schinese_stroke_m ')
Radical
Select * from team order by nlssort (sorting field name, 'nls _ sort = schinese_radical_m ')

5. Statements with poor query performance

Select address, SQL _text, buffer_gets, executions, buffer_gets/executions AVG
From v $ sqlarea
Where executions> 0 and buffer_gets> 100000;

6. query the name, equipment, and version of the current database.
Select instance_name, status, version from V $ instance;

Http://www.dezai.cn/blog/article.asp? Id = 260
To sum up, I would like to share with you a lot of comments.

This article references the following articles and thanks to the author:
Http://bbs.linuxpk.com/thread-10595-1-1.html

Http://topic.csdn.net/u/20080924/15/8070e357-2aca-4ba2-924a-ceeb1937e1ab.html