转:oralce常用操作、查询语句(查看表空间)
http://highill.iteye.com/blog/1534858
最近整理一下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 = 'highill';
--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;
--5.视图
-----查看视图的名称
- SELECT *
- FROM USER_VIEWS;
--6.同义词
-----查看同义词的名称
- SELECT *
- FROM USER_SYNONYMS;
--7.约束条件
-----查看某表的约束条件
- SELECT *
- FROM USER_CONSTRAINTS
- WHERE TABLE_NAME IN (SELECT USER_TABLES.TABLE_NAME
- FROM USER_TABLES);
- SELECT USER_CONSTRAINTS.*,
- USER_CONS_COLUMNS.*
- FROM USER_CONSTRAINTS,
- USER_CONS_COLUMNS
- WHERE USER_CONSTRAINTS.TABLE_NAME IN
- (SELECT USER_TABLES.TABLE_NAME
- FROM USER_TABLES)
- AND USER_CONSTRAINTS.OWNER = USER_CONS_COLUMNS.OWNER
- AND USER_CONSTRAINTS.CONSTRAINT_NAME = USER_CONS_COLUMNS.CONSTRAINT_NAME
- ORDER BY USER_CONS_COLUMNS.POSITION;
--8.存储函数和过程
-----查看函数和过程的状态
- SELECT *
- FROM USER_OBJECTS
- WHERE OBJECT_TYPE = 'FUNCTION';
- SELECT *
- FROM USER_OBJECTS
- WHERE OBJECT_TYPE = 'PROCEDURE';
-----查看函数和过程的源代码
- SELECT *
- FROM ALL_SOURCE
- WHERE NAME IN
- (SELECT USER_OBJECTS.OBJECT_NAME
- FROM USER_OBJECTS
- WHERE USER_OBJECTS.OBJECT_TYPE IN ('FUNCTION', 'PROCEDURE'));
三、查看表空间用量
--查询表空间 使用量 方法
- SELECT DBF.TABLESPACE_NAME AS "表空间",
- DBF.TOTALSPACE AS "总量(M)",
- (DBF.TOTALSPACE - DFS.FREESPACE) AS "使用总量(M)",
- DFS.FREESPACE AS "空闲总量(M)",
- DBF.TOTALBLOCKS AS "总块数",
- (DBF.TOTALBLOCKS - DFS.FREEBLOCKS) AS "使用块数",
- DFS.FREEBLOCKS AS "空闲块数",
- (1 - (DFS.FREESPACE / DBF.TOTALSPACE)) * 100 AS "使用比例",
- (DFS.FREESPACE / DBF.TOTALSPACE) * 100 AS "空闲比例"
- FROM (SELECT T.TABLESPACE_NAME,
- SUM(T.BYTES) / 1024 / 1024 TOTALSPACE,
- SUM(T.BLOCKS) TOTALBLOCKS
- FROM DBA_DATA_FILES T
- GROUP BY T.TABLESPACE_NAME) DBF,
- (SELECT TT.TABLESPACE_NAME,
- SUM(TT.BYTES) / 1024 / 1024 FREESPACE,
- SUM(TT.BLOCKS) FREEBLOCKS
- FROM DBA_FREE_SPACE TT
- GROUP BY TT.TABLESPACE_NAME) DFS
- WHERE TRIM(DBF.TABLESPACE_NAME) = TRIM(DFS.TABLESPACE_NAME);