Oracle-平时常用的SQL

--查看Oracle数据库版本
SELECT * FROM v$version;
--查询数据库所有模式用户
SELECT username, profile, default_tablespace, temporary_tablespace FROM dba_users;
--检查表空间的自动扩展开关
SELECT tablespace_name,SUBSTR (file_name, 1, 50), AUTOEXTENSIBLE FROM dba_data_files;
--在指定的用户下运行脚本
ALTER SESSION SET CURRENT_SCHEMA = TSSH;
--检查在当前数据库模式下是否存在指定的表
SELECT table_name FROM user_tables WHERE table_name = '表名';
--检查在当前表中是否存在指定的列
SELECT * FROM user_tab_cols WHERE table_name = 'TABLE_NAME' AND column_name = 'COLUMN_NAME';
--显示对象的DDL状态信息，TABLE可替换对象名称VIEWS等
SELECT DBMS_METADATA.get_ddl ('TABLE', '表名', '模式用户') FROM DUAL;
--在表空间中添加数据文件
ALTER TABLESPACE data01 ADD   '/work/oradata/STARTST/data01.dbf' SIZE 1000M AUTOEXTEND OFF;
--给指定的表空间增加大小
ALTER DATABASE '/u01/app/Test_data_01.dbf' RESIZE 2G;
--给出以 GB 为单位的数据库的实际大小
SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_data_files;
--查询读取耗费前十个SQL
SELECT *
  FROM (SELECT ROWNUM,
               SUBSTR(a.sql_text, 1, 200) sql_text,
               TRUNC(a.disk_reads / DECODE(a.executions, 0, 1, a.executions)) reads_per_execution,
               a.buffer_gets,
               a.disk_reads,
               a.executions,
               a.sorts,
               a.address
          FROM v$sqlarea a
         ORDER BY 3 DESC)
 WHERE ROWNUM < 10;
--在 Oracle 生成随机数据 
SELECT LEVEL empl_id,
           MOD (ROWNUM, 50000) dept_id,
           TRUNC (DBMS_RANDOM.VALUE (1000, 500000), 2) salary,
           DECODE (ROUND (DBMS_RANDOM.VALUE (1, 2)),  1, 'M',  2, 'F') gender,
           TO_DATE (
                 ROUND (DBMS_RANDOM.VALUE (1, 28))
              || '-'
              || ROUND (DBMS_RANDOM.VALUE (1, 12))
              || '-'
              || ROUND (DBMS_RANDOM.VALUE (1900, 2010)),
              'DD-MM-YYYY')
              dob,
           DBMS_RANDOM.STRING ('x', DBMS_RANDOM.VALUE (20, 50)) address
      FROM DUAL
CONNECT BY LEVEL < 10000;
--查询用户对表字段权限
select * from User_Col_Privs_Made  a where a.GRANTEE = '用户名';
--查询用户对表权限
select * from User_TAB_Privs_Made  a where a.GRANTEE = '用户名';

  --查询用户下所有表
  select SEGMENT_NAME, TABLESPACE_NAME, sum(BYTES / 1024 / 1024 /1024) || 'G'
  from USER_extents
  where USER_extents.SEGMENT_TYPE = 'TABLE'
  group by USER_extents.SEGMENT_NAME, USER_extents.TABLESPACE_NAME;

--查询过程中使用了那些表

SELECT * FROM user_source WHERE TYPE = 'PROCEDURE' AND upper(text) LIKE '%SRPT_YHCDFSE%';
select * from user_dependencies A where REFERENCED_OWNER = 'SHEAM' TYPE = 'PROCEDURE' ;

    常用SQL不定时更新