在我们进行Oracle数据库管理中经常会使用一些常用的SQL,下面就分享一下。
1:查看表空间的名称和大小
SQL> select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size 2 from dba_tablespaces t, dba_data_files d where t.tablespace_name = d.tablespace_name group by t.tablespace_name; TABLESPACE_NAME TS_SIZE ------------------------------------------------------------ ---------- UNDOTBS1 165 SYSAUX 640 SDE 10000 USERS 5 SYSTEM 7002:查看表空间的空闲大小
SQL> select sum(bytes)/(1024*1024) as free_space,tablespace_name 2 from dba_free_space group by tablespace_name; FREE_SPACE TABLESPACE_NAME ---------- ------------------------------------------------------------ 64.375 SYSAUX 152.75 UNDOTBS1 9251.5 SDE 3.6875 USERS .25 SYSTEM3:表空间整体使用情况
SQL> SELECT A.TABLESPACE_NAME,A.BYTES/(1024*1024) "TOTAL(MB)",B.BYTES/(1024*1024) "USED(MB)", C.BYTES/(1024*1024) "FREE( MB)", 2 (B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE" 3 FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C 4 WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME; TABLESPACE_NAME TOTAL(MB) USED(MB) FREE(MB) % USED % FREE ------------------------------------------------------------ ---------- ---------- ---------- ---------- ---------- SYSAUX 640 574.875 64.125 89.8242188 10.0195313 SDE 10000 755.5 9251.5 7.555 92.515 UNDOTBS1 165 11.3125 152.6875 6.85606061 92.5378788 USERS 5 .3125 3.6875 6.25 73.75 SYSTEM 700 698.75 .25 99.8214286 .035714286
4:查看某个表的大小
SQL> select sum(bytes)/(1024*1024) as "size(M)" from dba_segments where 2 owner='SDE' and segment_name='ROAD'; size(M) ---------- 1.5
5:建立表空间
CREATE SMALLFILE TABLESPACE "ESRI" DATAFILE 'E:\APP\ESRI\ORADATA\ORCL\ESRI' SIZE 5000M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO DEFAULT NOCOMPRESS CREATE SMALLFILE TABLESPACE "ESRI" DATAFILE 'E:\APP\ESRI\ORADATA\ORCL\ESRI' SIZE 10000M AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO DEFAULT NOCOMPRESS CREATE BIGFILE TABLESPACE "ESRI" DATAFILE 'E:\APP\ESRI\ORADATA\ORCL\ESRI' SIZE 50000M AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO DEFAULT NOCOMPRESS
6:删除表空间
drop tablespace "tablespacename" including contents and datafiles cascade constraints;
7:创建用户赋予权限
CREATE USER "TEST" PROFILE "DEFAULT" IDENTIFIED BY "*******" DEFAULT TABLESPACE "ESRI" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK GRANT "CONNECT" TO "TEST" GRANT "DBA" TO "TEST" GRANT "RESOURCE" TO "TEST"
SQL> select privilege from dba_SYS_privs where grantee='SDE'; PRIVILEGE -------------------------------------------------------------------------------- CREATE SESSION ADMINISTER DATABASE TRIGGER CREATE TYPE CREATE PROCEDURE DROP PUBLIC SYNONYM CREATE SEQUENCE CREATE PUBLIC SYNONYM CREATE TABLE CREATE OPERATOR CREATE INDEXTYPE CREATE VIEW PRIVILEGE -------------------------------------------------------------------------------- CREATE LIBRARY CREATE TRIGGER UNLIMITED TABLESPACE 已选择14行。
9:查看某个用户下的分区表信息
SQL> select TABLE_OWNER,TABLE_NAME, PARTITION_NAME,TABLESPACE_NAME from dba_tab_partitions where table_owner='TEST'; TABLE_OWNER TABLE_NAME PARTITION_NAME TABLESPACE_NAME ------------------------------ ------------------------------ ------------------------------ ------------------------ --- TEST CCC_1 CUS_PART1 ESRI TEST CCC CUS_PART1 ESRI TEST CCC_1 CUS_PART2 ESRI2 TEST CCC CUS_PART2 ESRI2
10:查看数据库的字符集
Windows操作系统来说:(Windows 7 64Bit、Oracle11.2.0.1 64Bit服务器、Oracle11.2.0.1 32Bit客户端)
我们可以通过查看注册表的方法来查询
Oracle服务器端字符集 HKEY_LOCAL_MACHINE-SOFTWARE-ORACLE-KEY_OraDB11g_home1-NLS_LANG Oracle客户端字符集 HKEY_LOCAL_MACHINE-SOFTWARE-Wow6432Node-ORACLE-KEY_OraDB11g_home1-NLS_LANG
服务器端也可以通过SQL语句查询
SQL> select userenv('language') from dual; USERENV('LANGUAGE') ---------------------------------------------------- AMERICAN_AMERICA.WE8MSWIN1252
11:得到Oracle后台跟踪文件
SELECT d.VALUE || '/' || LOWER (RTRIM (i.INSTANCE, CHR (0))) || '_ora_' || p.spid || '.trc' trace_file_name FROM (SELECT p.spid FROM v$mystat m, v$session s, v$process p WHERE m.statistic# = 1 AND s.SID = &SID AND p.addr = s.paddr) p, (SELECT t.INSTANCE FROM v$thread t, v$parameter v WHERE v.NAME = 'thread' AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i, (SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') d
-------------------------------------------------------------------------------------------------------
版权所有,文章允许转载,但必须以链接方式注明源地址,否则追究法律责任!
Blog: http://blog.csdn.net/linghe301
Weibo: http://www.weibo.com/linghe301
-------------------------------------------------------------------------------------------------------