转:oralce常用操作、查询语句(查看表空间)

时间:2024-11-05 18:05:05

http://highill.iteye.com/blog/1534858

最近整理一下oralce的常用语句,借此记录一下,在网上都应该能搜到,这里主要是整理分享。

一、操作语句 
建立表空间 MYDATE

  1. CREATE TABLESPACE "MYDATE"
  2. DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\MYDATE'
  3. SIZE 1500M AUTOEXTEND
  4. ON NEXT 200M MAXSIZE
  5. UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

建立用户highill,密码highill 并授权

  1. -- Create the user highill
  2. create user highill
  3. identified by highill
  4. default tablespace MYDATA
  5. temporary tablespace TEMP
  6. profile DEFAULT;
  7. -- Grant/Revoke role privileges
  8. grant connect to highill;
  9. grant dba to highill;
  10. grant resource to highill;
  11. -- Grant/Revoke system privileges
  12. grant unlimited tablespace to highill with admin option;

--修改表空间大小

  1. ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\MYDATA' RESIZE 32000M

数据库导入导出需要再cmd命令下进行 
导出文件

  1. cmd > exp highill/highill@ORCL file=d:/highill_2012.dmp

导入文件(需要清空用户下所有函数、存储过程、表、视图、物化视图等)

  1. cmd > imp highill/highill@ORCL file=d:/highill_2012.dmp full=y

仅导入一个表MYTABLE

  1. imp highill/highill@ORCL file=d:/highill_2012.dmp  tables=(MYTABLE)

二、下边说一下常用的SQL维护语句吧 
都是在PL/SQL环境下测试过的

--oracle传递参数  OBJECT_NAME = UPPER('&table_name'); 
--1.用户 
-----查看当前用户的缺省表空间

  1. SELECT USERNAME,
  2. DEFAULT_TABLESPACE
  3. FROM USER_USERS;

-----查看当前用户的角色

  1. SELECT *
  2. FROM USER_ROLE_PRIVS;

-----查看当前用户的系统权限和表级权限

  1. SELECT *
  2. FROM USER_SYS_PRIVS;
  3. SELECT *
  4. FROM USER_TAB_PRIVS;

-----显示当前会话所具有的权限

  1. SELECT *
  2. FROM SESSION_PRIVS;

-----显示指定用户所具有的系统权限

  1. SELECT *
  2. FROM DBA_SYS_PRIVS
  3. WHERE GRANTEE = 'highill';

--2.表 
-----查看用户下所有的表

  1. SELECT *
  2. FROM USER_TABLES;
  3. SELECT *
  4. FROM ALL_TABLES;

-----查看名称包含log字符的表

  1. SELECT OBJECT_NAME,
  2. OBJECT_ID
  3. FROM USER_OBJECTS   WHERE INSTR(OBJECT_NAME, 'LOG') > 0;

-----查看某表的创建时间

  1. SELECT USER_OBJECTS.*
  2. FROM USER_OBJECTS
  3. WHERE OBJECT_NAME IN (SELECT USER_TABLES.TABLE_NAME
  4. FROM USER_TABLES);

-----查看某表的大小 
-------输入查询

  1. SELECT SUM(BYTES) / (1024 * 1024) AS "size(M)"
  2. FROM USER_SEGMENTS   WHERE SEGMENT_NAME = 'MYTABLE';

-------查询用户下所有表大小

  1. SELECT USER_TABLES.TABLE_NAME,
  2. SUM(BYTES) / (1024 * 1024) AS "size(M)"
  3. FROM USER_TABLES,
  4. USER_SEGMENTS   WHERE USER_SEGMENTS.SEGMENT_NAME = USER_TABLES.TABLE_NAME
  5. GROUP BY USER_TABLES.TABLE_NAME;

-----查看放在ORACLE的内存区里的表

  1. SELECT TABLE_NAME,
  2. CACHE
  3. FROM USER_TABLES
  4. WHERE INSTR(CACHE,
  5. 'Y') > 0;

--3.索引 
-----查看索引个数和类别

  1. SELECT *
  2. FROM USER_INDEXES
  3. ORDER BY TABLE_NAME;

-----查看索引被索引的字段

  1. SELECT *
  2. FROM USER_IND_COLUMNS
  3. WHERE INDEX_NAME IN (SELECT USER_INDEXES.INDEX_NAME
  4. FROM USER_INDEXES);

-----查看索引的大小

  1. SELECT USER_INDEXES.INDEX_NAME,
  2. SUM(BYTES) / (1024 * 1024) AS "size(M)"
  3. FROM USER_SEGMENTS,
  4. USER_INDEXES   
  5. WHERE USER_SEGMENTS.SEGMENT_NAME = USER_INDEXES.INDEX_NAME
  6. GROUP BY USER_INDEXES.INDEX_NAME;

--4.序列号 
-----查看序列号,last_number是当前值

  1. SELECT *
  2. FROM USER_SEQUENCES;

--5.视图 
-----查看视图的名称

  1. SELECT *
  2. FROM USER_VIEWS;

--6.同义词 
-----查看同义词的名称

  1. SELECT *
  2. FROM USER_SYNONYMS;

--7.约束条件 
-----查看某表的约束条件

  1. SELECT *
  2. FROM USER_CONSTRAINTS
  3. WHERE TABLE_NAME IN (SELECT USER_TABLES.TABLE_NAME
  4. FROM USER_TABLES);
  5. SELECT USER_CONSTRAINTS.*,
  6. USER_CONS_COLUMNS.*
  7. FROM USER_CONSTRAINTS,
  8. USER_CONS_COLUMNS
  9. WHERE USER_CONSTRAINTS.TABLE_NAME IN
  10. (SELECT USER_TABLES.TABLE_NAME
  11. FROM USER_TABLES)
  12. AND USER_CONSTRAINTS.OWNER = USER_CONS_COLUMNS.OWNER
  13. AND USER_CONSTRAINTS.CONSTRAINT_NAME = USER_CONS_COLUMNS.CONSTRAINT_NAME
  14. ORDER BY USER_CONS_COLUMNS.POSITION;

--8.存储函数和过程 
-----查看函数和过程的状态

  1. SELECT *
  2. FROM USER_OBJECTS
  3. WHERE OBJECT_TYPE = 'FUNCTION';
  4. SELECT *
  5. FROM USER_OBJECTS
  6. WHERE OBJECT_TYPE = 'PROCEDURE';

-----查看函数和过程的源代码

  1. SELECT *
  2. FROM ALL_SOURCE
  3. WHERE NAME IN
  4. (SELECT USER_OBJECTS.OBJECT_NAME
  5. FROM USER_OBJECTS
  6. WHERE USER_OBJECTS.OBJECT_TYPE IN ('FUNCTION', 'PROCEDURE'));

三、查看表空间用量 
--查询表空间 使用量 方法

    1. SELECT DBF.TABLESPACE_NAME AS "表空间",
    2. DBF.TOTALSPACE AS "总量(M)",
    3. (DBF.TOTALSPACE - DFS.FREESPACE) AS "使用总量(M)",
    4. DFS.FREESPACE AS "空闲总量(M)",
    5. DBF.TOTALBLOCKS AS "总块数",
    6. (DBF.TOTALBLOCKS - DFS.FREEBLOCKS) AS "使用块数",
    7. DFS.FREEBLOCKS AS "空闲块数",
    8. (1 - (DFS.FREESPACE / DBF.TOTALSPACE)) * 100 AS "使用比例",
    9. (DFS.FREESPACE / DBF.TOTALSPACE) * 100 AS "空闲比例"
    10. FROM (SELECT T.TABLESPACE_NAME,
    11. SUM(T.BYTES) / 1024 / 1024 TOTALSPACE,
    12. SUM(T.BLOCKS) TOTALBLOCKS
    13. FROM DBA_DATA_FILES T
    14. GROUP BY T.TABLESPACE_NAME) DBF,
    15. (SELECT TT.TABLESPACE_NAME,
    16. SUM(TT.BYTES) / 1024 / 1024 FREESPACE,
    17. SUM(TT.BLOCKS) FREEBLOCKS
    18. FROM DBA_FREE_SPACE TT
    19. GROUP BY TT.TABLESPACE_NAME) DFS
    20. WHERE TRIM(DBF.TABLESPACE_NAME) = TRIM(DFS.TABLESPACE_NAME);