oralce常用操作、查询语句(查看表空间)
最近整理一下oralce的常用语句，借此记录一下，在网上都应该能搜到，这里主要是整理分享。

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

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

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

-- Create the user highill
  create user highill
  identified by highill
  default tablespace MYDATA
  temporary tablespace TEMP
  profile DEFAULT;
-- Grant/Revoke role privileges 
  grant connect to highill;
  grant dba to highill;
  grant resource to highill;
-- Grant/Revoke system privileges 
  grant unlimited tablespace to highill with admin option;

--修改表空间大小

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

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

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

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

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

仅导入一个表MYTABLE

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

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

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

SELECT USERNAME,
       DEFAULT_TABLESPACE
  FROM USER_USERS;

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

SELECT *
  FROM USER_ROLE_PRIVS;

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

SELECT *
  FROM USER_SYS_PRIVS;
SELECT *
  FROM USER_TAB_PRIVS;

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

SELECT *
  FROM SESSION_PRIVS;

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

SELECT *
  FROM DBA_SYS_PRIVS
 WHERE GRANTEE = 'CSG_CEB_TRUST';

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

SELECT *
  FROM USER_TABLES;
SELECT *
  FROM ALL_TABLES;

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

SELECT OBJECT_NAME,
       OBJECT_ID
  FROM USER_OBJECTS 　　WHERE INSTR(OBJECT_NAME, 'LOG') > 0;

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

SELECT USER_OBJECTS.*
  FROM USER_OBJECTS
 WHERE OBJECT_NAME IN (SELECT USER_TABLES.TABLE_NAME
                         FROM USER_TABLES);

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

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

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

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

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

SELECT TABLE_NAME,
       CACHE
  FROM USER_TABLES
 WHERE INSTR(CACHE,
             'Y') > 0;

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

SELECT *
  FROM USER_INDEXES
 ORDER BY TABLE_NAME;

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

SELECT *
  FROM USER_IND_COLUMNS
 WHERE INDEX_NAME IN (SELECT USER_INDEXES.INDEX_NAME
                        FROM USER_INDEXES);

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

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

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

SELECT *
  FROM USER_SEQUENCES;