oracle常用sql

1、按用户查看session

select sid,serial#,username,status from v$session where username='APP0104';

 

SQL> select sid,serial#,username,status,PROGRAM from v$session where username='APP0104';

       SID    SERIAL# USERNAME                       STATUS
---------- ---------- ------------------------------ --------
       397         81 APP0104                        INACTIVE
       406        491 APP0104                        INACTIVE
       420        115 APP0104                        ACTIVE

2、杀掉某一个session

alter system kill session 'sid,serial#' ;

SQL> alter system kill session '402,30235' ;

3、查看某个session对应的sql

select sql_text from v$sqltext a where a.hashvalue=(select sql_hash_value from v$session b where b.sid='383') order by piece asc
4、查看某个用户的表空间使用情况

COLUMN "Tablespace" format a13
COLUMN "Used MB"    format 99,999,999
COLUMN "Free MB"    format 99,999,999
COLUMN "Total MB"  format 99,999,999

SELECT fs.tablespace_name "Tablespace",
      (df.totalspace - fs.freespace) "Used MB",
      fs.freespace "Free MB",
      df.totalspace "Total MB",
      ROUND (100 * (fs.freespace / df.totalspace)) "Pct. Free"
  FROM (SELECT  tablespace_name, ROUND (SUM (BYTES) / 1024 / 1024) totalspace
            FROM dba_data_files
        GROUP BY tablespace_name) df,
      (SELECT  tablespace_name, ROUND (SUM (BYTES) / 1024 / 1024) freespace
            FROM dba_free_space
        GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name
  AND fs.tablespace_name='APP0104';

5、查看某个表字段的注解

  select table_name, column_name,comments from user_col_comments where table_name='SP_INVOICE_SUB';
6、查看某个表的注解

  SELECT TABLE_NAME,TABLE_TYPE,COMMENTS FROM USER_TAB_COMMENTS where TABLE_NAME='SP_INVOICE_SUB';

 

 7、查看表空间的文件

  select FILE_NAME,TABLESPACE_NAME from dba_data_files where TABLESPACE_NAME='APP0104';