Oracle Database experience series-Article 1 and Article 1

Oracle Database experience series-Article 1 and Article 1
1) change the date display type of the query results in SQL developer in oracle 11g r2, that is, the setting of the session date type:
Alter session set nls_date_format = 'yyyy-MM-DD HH24: MI: ss ';
Oracle field:
Alter table operator_daily add (op_type number (11 ));

2) The dense_rank () function is a continuous sorting. When there are two second names, it still follows the third name.

3) The rank () function is a skip sorting. When there are two second names, the next is the fourth one (also in each group)

4) View table constraints: Select * from user_constraints where table_name = 'parent ';

Constraint_type: c: check p: primary key r: foreign key u: unique

5) create tablespace mcp datafile 'e: \ oracle \ tablespace \ data. dbf' size 512 m;

6) when the oracle field is varchar2, The substr function is used for conversion, instead of tu_char.

7) when case when exists in the statement, the group by clause must be written into the conditions in case when. Otherwise, the group rules are not met;

8) backup table:
Create table table_name as select * from table_namexxx

Oracle Database sequence Problems

Oracle contains five database objects: alias, sequence, view, index, and cluster. the alias and sequence are specific database objects in Oracle. That is to say, any user can call the same sequence as long as they have permissions. The sequence starts from where it is specified when the user creates the sequence. For example, in sys, you create a sequence, the first query returns the initial value 1, and then the reference to the NEXTVAL pseudo column will automatically add the corresponding data according to your settings, and then you will query the sequence under the scott user, it will become 2, and we hope to adopt

Sequence problems in oracle databases

1
Increment by-2: The default value of maxvalue is-1, so

Start with 100 cannot be greater than MAXVALUE-1

2
The descending sequence is maxvalue, that is, if you do not write start with 100 in the first statement, it is equivalent to start with-1.

I will not explain why the second statement is correct.