你好,游客 登录
背景:
阅读新闻

ArcSDE for Oracle常用的SQL语句 - ArcGIS技术研究

[日期:2013-04-11] 来源:  作者: [字体: ]

在我们进行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                                                              700
2:查看表空间的空闲大小
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 SYSTEM
3:表空间整体使用情况
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"


8:查看某个用户的相关权限
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

-------------------------------------------------------------------------------------------------------






收藏 推荐 打印 | 录入:admin | 阅读:
相关新闻