Oracle Common Statements

1. See which table a field is in

Select owner, TABLE_NAME

From Dba_tab_columns

where lower (column_name) = ' field name ';

2. Export sequence

SELECT ' Create sequence username. ' | |  Sequence_name | |
' MinValue ' | |  Min_value | |
' MaxValue ' | |  Max_value | |
' Start with ' | |  Last_number | |
' Increment by ' | |  increment_by | |
' Cache ' | | Cache_size | | ‘ ;‘
From User_sequences;

3. Batch Increase sequence

Declare

Vc_sql VARCHAR2 (200);

N_num number;

Begin

For I in 1.. LOOP

For a in (select * from User_sequences t where t.sequence_name like ' seq% ') loop

Vc_sql: = ' SELECT ' | | A.sequence_name | | ‘. Nextval from dual ';

Execute Immediate vc_sql

into N_num;

End Loop;

END LOOP;

End

4, modify the database user password validity period

The default validity period of the database user password is 180 days, so almost half a year or so will appear, in order to prevent future appearance,

It is recommended that you set the password to be permanent, with the following steps:

CMD sqlplu/as sysdba

(1) Check the user's proifle is which, generally is default:sql>select username,profile from Dba_users;

(2) To view the password expiration settings for the specified profile (such as default):

Sql>select * from Dba_profiles s WHERE s.profile= ' DEFAULT ' and resource_name= ' password_life_time ';

(3) Change the password validity period from the default of 180 days to "unlimited":

Sql>alter profile DEFAULT LIMIT password_life_time UNLIMITED;

You do not need to restart the database after you modify it, it takes effect immediately.

? 5, create Dblink

Create DATABASE link linkname
Connect to Linknameidentified by pwd
Using ' (DESCRIPTION =
(Address_list =
(ADDRESS = (PROTOCOL = TCP) (HOST = ipaddress) (PORT = 1521))
)
(Connect_data =
(service_name = ORCL)
)
)’;

6, Oracle Timer (JOB) each time period to summarize

1), per minute execution

Interval = TRUNC (sysdate, ' mi ') + 1/(24*60) 2, daily scheduled execution

Example: Daily 2 o'clock in the morning execution

Interval = TRUNC (sysdate) + 1 +2/(24)

2), every half-hour execution

sysdate+30/1440

3), 15 minutes per hour of execution

For example: 8:15,9:15,10:15 ...:

Trunc (sysdate, ' hh ') +75/1440.

4), weekly scheduled execution

For example: Every Monday 2 o'clock in the morning execution

Interval = TRUNC (Next_day (sysdate,2)) +2/24-Monday, the second day of the week

5), executed every Sunday

Next_day (Trunc (sysdate), ' SUNDAY ')

6), monthly scheduled execution

For example: 1st 2 o'clock in the morning every month to execute

Interval =>trunc (Last_day (sysdate)) +1+2/24

7), quarterly scheduled execution

For example, the first day of each quarter is 2 o'clock in the morning execution

Interval = TRUNC (Add_months (sysdate,3), ' Q ') + 2/24

8), every half-yearly scheduled execution

For example: Every July 1 and January 1 2 o'clock in the morning

Interval = Add_months (trunc (sysdate, ' yyyy '), 6) +2/24

9), scheduled to execute every year

For example: January 1 2 o'clock in the morning every year to execute

Interval =>add_months (trunc (sysdate, ' yyyy '), 12) +2/24

10), daily 20 o'clock in the afternoon execution

Trunc (sysdate, ' dd ') + 20/24 or trunc (sysdate+1) + 20/24

11), every Friday 11:10:20 to execute

Trunc (Next_day (sysdate, ' Friday ')) +23/24+10/1440+20/86400

12), 15th 11:10:20 each month to execute

Last_day (Add_months (TRUNC (sysdate, ' MM '),-1)) +15 +23/24+10/1440+20/86400

13), June 5 11:10:20 each year to execute

Trunc (To_date (To_char (sysdate, ' yyyy ') +1| | ' 0605 ', ' yyyy-mm-dd ')) +23/24+10/1440+20/86400

7. View the number of connections

SELECT * from Gv$license;

8. Deadlock Query

/* Find deadlock */SELECT/*+ rule */s.sid| | ', ' | | S.serial#,s.username,s.client_info, Decode (l.type, ' TM ', ' TABLE lock ', ' TX ', ' ROW lock ', NULL) Lock_level, O.owner, O.object_name,o.object_type, S.terminal,s.machine,s.program,s.osuser,a.sql_text from V$session s,v$lock L,dba_ Objects O,v$sqlarea a WHERE l.sid = s.sid and L.id1 = o.object_id (+) and s.prev_sql_addr = A.address and s.username are not NULL ORDER BY S.client_info

Find who holds the lock does not release SELECT/*+ rule */s.username, decode (l.type, ' TM ', ' TABLE lock ', ' TX ', ' ROW lock ', NULL) Lock_level, O.objec　　  T_name, S.sid, l.block from v$session s,v$lock l,dba_objects o WHERE l.sid = s.sid and L.id1 = o.object_id (+) And S.username is not NULL for order by S.username; If the block is listed as 1, the corresponding SID session is holding a object_name lock, and other object_name-related locks are waiting for the SID to commit or rollback.

/* KILL lock */alter system disconnect session ' 935,28622 ' immediate;

Oracle Common Statements