Get database time SQL and row-level lock summary-shared lock-exclusive lock-deadlock

--trunc (Date,[fmt])/trunc (Number[,decimals])
SELECT sysdate from dual;
SELECT TRUNC (sysdate) from dual
SELECT TRUNC (12.34524,2) from dual; --12.34
SELECT TRUNC ( -12.34724,2) from dual; --12.34
SELECT TRUNC (12.34524,-1) from dual; --10
--Returns the first day of the current month
Select TRUNC (sysdate, ' month ') "first day of the current month" from dual;
--Get the last day of the current month
Select TRUNC (Last_day (sysdate)) "Last day of the current month" from dual;
--Get the first day of the current year
Select TRUNC (Sysdate, ' year ') "Year first day" from dual;
--Get the last day of the current year
Select Add_months (TRUNC (Sysdate, ' year '), and 1 "year last day" from dual;
--Get the number of days/years of the current month
SELECT CAST (To_char (Last_day (sysdate), ' DD ') as INT) number_of_days from dual;
SELECT CAST (To_char (Last_day (sysdate), ' yyyy ') as INT) number_of_days from dual;
--Get the number of days remaining in the current month
Select Sysdate, Last_day (sysdate) "Last", Last_day (sysdate)-sysdate ' Day left ' from dual;
--Get a number of days between two dates
Select TRUNC (to_date (' 2014/02/01 ', ' yyyy/mm/dd ')-to_date (' 2012/05/01 ', ' Yyyy/mm/dd '), 1) "Days" from dual;
--Displays the date of the current year ending at the beginning and end of the month
SELECT add_months (TRUNC (to_date (' 2016/05/01 ', ' yyyy/mm/dd '), ' MONTH '), i) start_date,
TRUNC (Last_day (Add_months (to_date (' 2016/05/01 ', ' yyyy/mm/dd '), i))) end_date
From XMLTABLE (
' for $i with 0 to Xs:int (A) return $i '
Passing XMLELEMENT (
A
Floor (
Months_between (
Add_months (TRUNC (to_date (' 2016/12/01 ', ' yyyy/mm/dd '), ' year ')-1, (a), to_date (' 2016/05/01 ', ' yyyy/mm/dd '))
)
)
Columns I INTEGER PATH '. '
);
SELECT add_months (TRUNC (sysdate, ' MONTH '), 1) start_date from dual;
SELECT TRUNC (Last_day (Add_months (sysdate, 1))) end_date from dual;
--Get the number of seconds to start today and the number of seconds left
Select (Sysdate-trunc (sysdate)) *24*60*60 "Past", (TRUNC (sysdate + 1)-sysdate) *24*60*60 ' future ' from dual;

--Numeric related
--converts numeric values to text
SELECT to_char (to_date (1526, ' J '), ' JSP ') from dual;
--query string in the source code of the package
SELECT * from Dba_source
WHERE UPPER (text) like '%foo_something% '
and owner = ' user_name '
-- Doing row data multiplication in Oracle
with TBL
     as (SELECT-2 num from DUAL
     & nbsp;   UNION
         SELECT-3 num from DUAL
          UNION
         SELECT-4 num from DUAL),
     sign_val
     as (SELECT case MOD (COUNT (*), 2) while 0 then 1 E LSE-1 END val
           from TBL
           WHERE num < 0)
  SELECT EXP (SUM (LN (ABS (num))) * Val
    F ROM tbl, Sign_val
GROUP by Val;

--Generate random values
SELECT ROUND (Dbms_random.value () *100) +1 as random_num from dual;
--Check if there is any data in the table
SELECT *
From Sino_dp_header_input
WHERE ROWNUM = 1;

--Data dictionary query
--Check whether the specified table exists in the current database mode
SELECT * from User_tables
WHERE table_name = ' sino_dp_header_input ';
--Checks whether the specified column exists in the current table
SELECT column_name as FOUND
From User_tab_cols
WHERE table_name = ' sino_dp_header_input '
and column_name = ' Dp_number '
--Get the current pattern name
SELECT sys_context (' Userenv ', ' Current_schema ') from dual;

--Database management query
-database version information
SELECT * from V$version;
--Database default information
SELECT username,profile,default_tablespace,temporary_tablespace from Dba_users;
--Database character setting information
SELECT * from Nls_database_parameters;
--store case-sensitive data, but the index is case-insensitive
CREATE TABLE tab (col1 VARCHAR2);
CREATE INDEX idx1 on tab (UPPER (col1));
ANALYZE TABLE a COMPUTE STATISTICS;
--Adjust the tablespace without adding data files
ALTER DATABASE datafile '/work/oradata/startst/star02d.dbf ' RESIZE 2000M;
--Check the automatic expansion switch for table space
SELECT SUBSTR (file_name,1,50), autoextensible from Dba_database_files;
SELECT Tablespace_name, autoextensible from Dba_data_files;
--Add a data file to the tablespace
ALTER tablespace data01 add datafile '/work/oradata/startst/data01.dbf ' SIZE 1000m autoextend off;< br>--gets the actual size of the database
SELECT SUM (bytes)/1024/1024/1024 as GB from Dba_data_files;

--Performance-related queries
--Query the user CPU usage rate
SELECT Ss.username,se.sid, value/100 cpu_usage_seconds
From V$vession SS, V$sesstat se,v$statname SN
WHERE se.statics# = sn.statics#
and NAME like '%cpu used by this session% '
and Se.sid = Ss.sid
and ss.status = ' ACTIVE '
And Ss.username is not NULL
ORDER by VALUE DESC;

--Get current session ID, process ID, client ID, etc.
SELECT B.sid, B.serial#,a.spid ProcessID, b.process clientpid
From V$process A, v$session b
WHERE a.addr = b.paddr and B.audsid = USERENV (' SessionID ');
--Query and display the actual Oracle link in the view
SELECT Osuser,username,machine,program
From V$session
ORDER by Osuser;
--Query and display the number of sessions of users and users linking Oracle
SELECT username, COUNT (username) CO
From V$session
GROUP by username
ORDER by Co DESC;

--Get the number of objects owned by the owner
SELECT Owner,count (owner) obj
From Dba_objects
GROUP by Owner
ORDER by obj DESC;

--what is the condition of the query lock?
V$lock, V$locked_object, V$session, V$sqlarea, v$process;
--the method of querying the lock table:
SELECT S.sid session_id, S.username,
DECODE (lmode, 0, ' None ', 1, ' Null ', 2, ' Row-s (SS) ', 3, ' Row-x (SX) ', 4, ' Share ', 5, ' S/row-x (SSX) ', 6, ' Exclusive ', To_c HAR (Lmode))
Mode_held, DECODE (REQUEST, 0, ' None ', 1, ' Null ', 2, ' Row-s (SS) ', 3, ' Row-x (SX) ', 4, ' Share ', 5, ' S/row-x (SSX) ', 6, ' EXC Lusive ', To_char (REQUEST))
mode_requested, o.ccbzzp| | '. ' | | o.object_name| | ' (' | | o.object_type| | ') ', S.type Lock_type, L.id1 lock_id1, L.id2 Lock_id2
From V$lock L, SYS. Dba_objects O, v$session S WHERE l.sid = s.sid and l.id1 = o.object_id;
--How do I unlock it?
ALTER SYSTEM KILL SESSION ' sid,serir# ';

--mysql the use of row-level locks and the prevention of deadlock

--1.mysql's InnoDB, which supports transaction and row-level locks, can use row-level locks to handle business such as user withdrawals. A deadlock occurs when a MySQL lock is used.
--mysql Row-level locks
--shared Lock: A shared lock is a read lock, and all transactions can only read to it and cannot be written, plus shared locks can only be added to other transactions before the transaction, and no other type of lock can be added.
SELECT ID from TABLE WHERE ID in ($) lock in SHARE MODE--the data of the result set will be shared lock
--Exclusive Lock: If a transaction has an exclusive lock on a row, only the transaction can read and write, and before the end of this transaction, other transactions cannot lock it, other processes can read, cannot write, wait for its release
SELECT ID from TABLE WHERE ID in (.) for UPDATE
--Deadlock: More than two processes require the other to release resources so that the process waits. Conditions: Mutually exclusive conditions, loop conditions, inalienable, indispensable, the corresponding as long as the illusion of one of the conditions of deadlock will not produce
--The first statement takes precedence over the name index, because name is not the primary key index, and the primary key index is used
--The second statement uses the primary key index first, and then the name index,
--If two statements are executed at the same time, the first statement executes the name index waiting for the second one to release the primary key index, and the second performs the primary key index waiting for the first name index.
--This creates a deadlock.
UPDATE mk_user SET NAME = ' 1 ' WHERE name= ' id '
UPDATE mk_user SET NAME = ' 2 ' WHERE ID = 2
--Transformation
UPDATE mk_user SET NAME = ' 1 ' where ID = (SELECT ID from mk_user where NAME = ' id ')
UPDATE mk_user SET NAME = ' 2 ' WHERE ID = 2
--Operation Limit shared and non-shared locks
--Lock application Prerequisite: No thread is currently using exclusive locks on any row in the result set, otherwise the request is blocked
--Thread read operation write operation share lock request exclusive lock request
--Use shared lock to read and write/not write (error) can apply
--Not applicable shared lock readable non-writable (blocked) can apply for non-application (blocking)

--1. Using a shared lock thread to read its locked record, other threads also read the lock record, and the data read by the two threads belongs to the same version
--2. For write operations, threads that use shared locks need to be discussed separately,
--  ① when a shared lock is used on the execution record by the thread only, it is possible to write to the record (including updates and deletions), because the thread has requested an exclusive lock on the record before the write operation, and the
-- ② when another thread also uses a shared lock on the record. Write operation is not allowed, the system will be prompted with an error.
--a thread that does not use a shared lock, does not write, writes a join block
--3. Use a shared lock to request a shared lock on the locked record again, the system does not make an error, and other threads can request a shared lock on the locked record.
--4. Using a shared lock to request exclusive access to its locked record, no other process can request to lock the record to lock, the request will be blocked

--Exclusive Lock: MySQL adds an exclusive lock on each row of the query result set, and the update and delete operations of any record are automatically added to the lock in the transaction operation.
--Lock application Prerequisite: No thread is currently using exclusive or shared locks on any row in the result set, or the request is blocked
--Thread read operation write operation share lock request exclusive lock request
--Use exclusive lock to read (new version) can be written can apply for
--Not applicable exclusive lock readable (old version) not writable (blocked) not apply (blocked) not apply (block)
--1. Using an exclusive lock thread can read its locked record, read the latest version of the current transaction, and for a thread that does not apply an exclusive lock, it is also possible to read operations, which is a consistent, non-locking read.
-that is, for the same record, the database records multiple versions, and the update operation within the transaction reacts to the new version, and the old version is provided to other threads for read operations
--2. Use an exclusive lock thread to write to its locked record, which is not allowed for a thread that does not use an exclusive lock for write operations to a locked record.
--3. Use an exclusive lock process to request a shared lock on its locked record, but after requesting a shared lock, the thread does not release the original exclusive lock, so the data is externally exclusive, and other threads cannot request a shared lock on the locked data

Get database time SQL and row-level lock summary-shared lock-exclusive lock-deadlock