DBA管理常用SQL（未完待续）

        查询锁表：

        

select sess.SID,
       sess.SERIAL#,
       sess.USERNAME,
       decode(l.LMODE,
              0,
              'none',
              1,
              'null',
              2,
              'SS',
              3,
              'SX',
              4,
              'Share',
              5,
              'SRX',
              6,
              'X',
              l.LMODE) LockMode
  from v$lock l, v$session sess, dba_objects do
 where l.SID = sess.SID
   and l.ID1 = do.OBJECT_ID;

        查询某表的空间占用：

        

select segment_name,
       alt.OWNER
       tablespace_name,
       segment_type,
       round((BYTES /1024/1024/1024), 2) as "大小（GB）"
  from user_segments, all_tables alt
 where segment_type = 'TABLE'
   and segment_name like '%HISTORY%'
   and alt.TABLE_NAME = segment_name
   order by tablespace_name, BYTES desc;

        建立Database link的语句：

        

create database link dblink_TEST
  connect to wings identified by wings
  using '(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = TESTDB)
    )
  )';