sqlplus 查看Oracle中地理数据库的一些常用命令
分享
-
2019-10-17
文章来源：https://blog.csdn.net/hcdxp/ar ... 89153
https://blog.csdn.net/hcdxp/ar ... 09582
1.连接sde用户。
在Oracle数据库中使用建立好企业级地理数据库之后，会创建一个sde用户，在sqlplus中连接sde用户示例如下图。
2.查看pdb，con_name 查看表空间的名称及大小
我的Oracle环境开启了pdb，建库的时候填的是pdb的参数。
开启和关闭pdb。
SQL> alter pluggable database pdborcl open;
SQL> alter pluggable database pdborcl close;
切换到pdb和切换到cdb。
SQL> alter session set container=pdborcl;
会话已更改。
SQL> alter session set container=CDB$ROOT;
由下面看到，在cdb，查看表空间没有sde表空间的，但是在pdb是有sde表空间。
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBORCL READ WRITE NO
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL> SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size
2 FROM dba_tablespaces t, dba_data_files d
3 WHERE t.tablespace_name = d.tablespace_name
4 GROUP BY t.tablespace_name;
TABLESPACE_NAME TS_SIZE
------------------------------ ----------
SYSAUX 900
UNDOTBS1 645
USERS 5
SYSTEM 800
切换到pbd。
SQL> alter session set container=pdborcl;
会话已更改。
SQL> show con_name;
CON_NAME
------------------------------
PDBORCL
SQL> SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size
2 FROM dba_tablespaces t, dba_data_files d
3 WHERE t.tablespace_name = d.tablespace_name
4 GROUP BY t.tablespace_name;
TABLESPACE_NAME TS_SIZE
------------------------------ ----------
SYSAUX 610
SDE 400
USERS 5
EXAMPLE 1281
SYSTEM 280
3.查看表空间物理文件的名称及大小
SQL> SELECT tablespace_name,
2 file_id,
3 file_name,
4 round(bytes / (1024 * 1024), 0) total_space
5 FROM dba_data_files
6 ORDER BY tablespace_name;
TABLESPACE_NAME FILE_ID
------------------------------ ----------
FILE_NAME
--------------------------------------------------------------------------------
TOTAL_SPACE
-----------
EXAMPLE 10
E:\APP\ORCL\ORADATA\ORCL\PDBORCL\EXAMPLE01.DBF
1281
SDE 14
E:\APP\ORCL\PRODUCT\12.1.0\DBHOME_1\DATABASE\SDE01
400
TABLESPACE_NAME FILE_ID
------------------------------ ----------
FILE_NAME
--------------------------------------------------------------------------------
TOTAL_SPACE
-----------
SYSAUX 8
E:\APP\ORCL\ORADATA\ORCL\PDBORCL\SYSAUX01.DBF
610
SYSTEM 7
E:\APP\ORCL\ORADATA\ORCL\PDBORCL\SYSTEM01.DBF
TABLESPACE_NAME FILE_ID
------------------------------ ----------
FILE_NAME
--------------------------------------------------------------------------------
TOTAL_SPACE
-----------
280
USERS 9
E:\APP\ORCL\ORADATA\ORCL\PDBORCL\SAMPLE_SCHEMA_USERS01.DBF
5
4.查看控制文件
SQL> SELECT NAME FROM v$controlfile;
NAME
----------------------------------------------------------------------------------------------------
E:\APP\ORCL\ORADATA\ORCL\CONTROL01.CTL
E:\APP\ORCL\ORADATA\ORCL\CONTROL02.CTL
5.查看日志文件
SQL> SELECT MEMBER FROM v$logfile;
MEMBER
----------------------------------------------------------------------------------------------------
E:\APP\ORCL\ORADATA\ORCL\REDO03.LOG
E:\APP\ORCL\ORADATA\ORCL\REDO02.LOG
E:\APP\ORCL\ORADATA\ORCL\REDO01.LOG
6.查看表空间的使用情况
SQL> SELECT a.tablespace_name,
2 a.bytes total,
3 b.bytes used,
4 c.bytes free,
5 (b.bytes * 100) / a.bytes "% USED ",
6 (c.bytes * 100) / a.bytes "% FREE "
7 FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
8 WHERE a.tablespace_name = b.tablespace_name
9 AND a.tablespace_name = c.tablespace_name;
TABLESPACE_NAME TOTAL USED FREE % USED % FREE
------------------------------ ---------- ---------- ---------- ---------- ----------
SYSAUX 639631360 607649792 31522816 95 4.92827869
SDE 419430400 56098816 370933760 13.375 88.4375
USERS 5242880 327680 3866624 6.25 73.75
SYSTEM 293601280 288751616 3801088 98.3482143 1.29464286
EXAMPLE 1342832640 1277296640 64487424 95.1195705 4.80234261
7.查看数据库的版本
SQL> SELECT version
2 FROM product_component_version
3 WHERE substr(product, 1, 6) = 'Oracle';
VERSION
----------------------------------------------------------------------------------------------------
12.1.0.2.0
8.查看数据库的创建日期和归档方式
SQL> SELECT created, log_mode, log_mode FROM v$database;
CREATED LOG_MODE LOG_MODE
-------------- ------------ ------------
12-8月 -19 NOARCHIVELOG NOARCHIVELOG
9.查看当前用户的权限
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
ALTER SYSTEM
CREATE SESSION
CREATE TABLE
SELECT ANY TABLE
CREATE ANY INDEX
ALTER ANY INDEX
DROP ANY INDEX
CREATE PUBLIC SYNONYM
DROP PUBLIC SYNONYM
CREATE VIEW
CREATE ANY VIEW
PRIVILEGE
----------------------------------------
DROP ANY VIEW
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE ANY TRIGGER
CREATE TYPE
CREATE LIBRARY
CREATE OPERATOR
CREATE INDEXTYPE
ADMINISTER DATABASE TRIGGER
INHERIT ANY PRIVILEGES
已选择 22 行。
一些权限的说明。
CREATE SESSION 连接到Oracle。
CREATE TABLE CREATE TRIGGER 允许建表和触发器
CREATE SEQUENCE 允许建序列
CREATE PROCEDURE 创建存储过程
ADMINISTER DATABASE TRIGGER 管理触发器
10.查看当前用户拥有的表
SQL> select table_name from user_tables;
TABLE_NAME
--------------------------------------------------------------------------------
VERSION
SPATIAL_REFERENCES
GEOMETRY_COLUMNS
LAYERS
METADATA
RASTER_COLUMNS
TABLE_REGISTRY
COLUMN_REGISTRY
STATES
VERSIONS
MVTABLES_MODIFIED
TABLE_NAME
--------------------------------------------------------------------------------
STATE_LINEAGES
LOCATORS
SDE_XML_INDEXES
SDE_XML_INDEX_TAGS
SDE_XML_COLUMNS
INSTANCES
SDE_ARCHIVES
SDE_LAYER_STATS
BRANCHES
BRANCH_TABLES_MODIFIED
MULTIBRANCH_TABLES
TABLE_NAME
--------------------------------------------------------------------------------
TABLES_LAST_EDIT_TIME
LAYER_LOCKS
STATE_LOCKS
TABLE_LOCKS
OBJECT_LOCKS
PROCESS_INFORMATION
SDE_TABLES_MODIFIED
LINEAGES_MODIFIED
SDE_LOGFILE_POOL
DBTUNE
SERVER_CONFIG
TABLE_NAME
--------------------------------------------------------------------------------
ST_COORDINATE_SYSTEMS
ST_SPATIAL_REFERENCES
ST_GEOMETRY_COLUMNS
ST_GEOMETRY_INDEX
ST_UNITS_OF_MEASURE
ST_PARTITION_INDEX
GDB_TABLES_LAST_MODIFIED
GDB_ITEMS
SDE_XML_DOC1
DR$XMLDOCIX1_TX$I
DR$XMLDOCIX1_TX$R
TABLE_NAME
--------------------------------------------------------------------------------
SDE_XML_DOC2
DR$XMLDOCIX2_TX$I
DR$XMLDOCIX2_TX$R
SDE_XML_DOC3
DR$XMLDOCIX3_TX$I
DR$XMLDOCIX3_TX$R
GDB_ITEMRELATIONSHIPS
SDE_XML_DOC4
DR$XMLDOCIX4_TX$I
DR$XMLDOCIX4_TX$R
GDB_ITEMTYPES
TABLE_NAME
--------------------------------------------------------------------------------
GDB_ITEMRELATIONSHIPTYPES
GDB_REPLICALOG
GDB_LOCKS
GDB_CONFLICTS
SDE_LOGFILES
SDE_LOGFILE_DATA
DR$XMLDOCIX1_TX$K
DR$XMLDOCIX1_TX$N
DR$XMLDOCIX2_TX$K
DR$XMLDOCIX2_TX$N
DR$XMLDOCIX3_TX$K
TABLE_NAME
--------------------------------------------------------------------------------
DR$XMLDOCIX3_TX$N
S1_IDX$
DR$XMLDOCIX4_TX$K
DR$XMLDOCIX4_TX$N
已选择 70 行。
11.查看索引
SQL> select index_name,table_name from user_indexes order by table_name;
https://blog.csdn.net/hcdxp/ar ... 09582
1.连接sde用户。
在Oracle数据库中使用建立好企业级地理数据库之后，会创建一个sde用户，在sqlplus中连接sde用户示例如下图。
2.查看pdb，con_name 查看表空间的名称及大小
我的Oracle环境开启了pdb，建库的时候填的是pdb的参数。
开启和关闭pdb。
SQL> alter pluggable database pdborcl open;
SQL> alter pluggable database pdborcl close;
切换到pdb和切换到cdb。
SQL> alter session set container=pdborcl;
会话已更改。
SQL> alter session set container=CDB$ROOT;
由下面看到，在cdb，查看表空间没有sde表空间的，但是在pdb是有sde表空间。
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBORCL READ WRITE NO
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL> SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size
2 FROM dba_tablespaces t, dba_data_files d
3 WHERE t.tablespace_name = d.tablespace_name
4 GROUP BY t.tablespace_name;
TABLESPACE_NAME TS_SIZE
------------------------------ ----------
SYSAUX 900
UNDOTBS1 645
USERS 5
SYSTEM 800
切换到pbd。
SQL> alter session set container=pdborcl;
会话已更改。
SQL> show con_name;
CON_NAME
------------------------------
PDBORCL
SQL> SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size
2 FROM dba_tablespaces t, dba_data_files d
3 WHERE t.tablespace_name = d.tablespace_name
4 GROUP BY t.tablespace_name;
TABLESPACE_NAME TS_SIZE
------------------------------ ----------
SYSAUX 610
SDE 400
USERS 5
EXAMPLE 1281
SYSTEM 280
3.查看表空间物理文件的名称及大小
SQL> SELECT tablespace_name,
2 file_id,
3 file_name,
4 round(bytes / (1024 * 1024), 0) total_space
5 FROM dba_data_files
6 ORDER BY tablespace_name;
TABLESPACE_NAME FILE_ID
------------------------------ ----------
FILE_NAME
--------------------------------------------------------------------------------
TOTAL_SPACE
-----------
EXAMPLE 10
E:\APP\ORCL\ORADATA\ORCL\PDBORCL\EXAMPLE01.DBF
1281
SDE 14
E:\APP\ORCL\PRODUCT\12.1.0\DBHOME_1\DATABASE\SDE01
400
TABLESPACE_NAME FILE_ID
------------------------------ ----------
FILE_NAME
--------------------------------------------------------------------------------
TOTAL_SPACE
-----------
SYSAUX 8
E:\APP\ORCL\ORADATA\ORCL\PDBORCL\SYSAUX01.DBF
610
SYSTEM 7
E:\APP\ORCL\ORADATA\ORCL\PDBORCL\SYSTEM01.DBF
TABLESPACE_NAME FILE_ID
------------------------------ ----------
FILE_NAME
--------------------------------------------------------------------------------
TOTAL_SPACE
-----------
280
USERS 9
E:\APP\ORCL\ORADATA\ORCL\PDBORCL\SAMPLE_SCHEMA_USERS01.DBF
5
4.查看控制文件
SQL> SELECT NAME FROM v$controlfile;
NAME
----------------------------------------------------------------------------------------------------
E:\APP\ORCL\ORADATA\ORCL\CONTROL01.CTL
E:\APP\ORCL\ORADATA\ORCL\CONTROL02.CTL
5.查看日志文件
SQL> SELECT MEMBER FROM v$logfile;
MEMBER
----------------------------------------------------------------------------------------------------
E:\APP\ORCL\ORADATA\ORCL\REDO03.LOG
E:\APP\ORCL\ORADATA\ORCL\REDO02.LOG
E:\APP\ORCL\ORADATA\ORCL\REDO01.LOG
6.查看表空间的使用情况
SQL> SELECT a.tablespace_name,
2 a.bytes total,
3 b.bytes used,
4 c.bytes free,
5 (b.bytes * 100) / a.bytes "% USED ",
6 (c.bytes * 100) / a.bytes "% FREE "
7 FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
8 WHERE a.tablespace_name = b.tablespace_name
9 AND a.tablespace_name = c.tablespace_name;
TABLESPACE_NAME TOTAL USED FREE % USED % FREE
------------------------------ ---------- ---------- ---------- ---------- ----------
SYSAUX 639631360 607649792 31522816 95 4.92827869
SDE 419430400 56098816 370933760 13.375 88.4375
USERS 5242880 327680 3866624 6.25 73.75
SYSTEM 293601280 288751616 3801088 98.3482143 1.29464286
EXAMPLE 1342832640 1277296640 64487424 95.1195705 4.80234261
7.查看数据库的版本
SQL> SELECT version
2 FROM product_component_version
3 WHERE substr(product, 1, 6) = 'Oracle';
VERSION
----------------------------------------------------------------------------------------------------
12.1.0.2.0
8.查看数据库的创建日期和归档方式
SQL> SELECT created, log_mode, log_mode FROM v$database;
CREATED LOG_MODE LOG_MODE
-------------- ------------ ------------
12-8月 -19 NOARCHIVELOG NOARCHIVELOG
9.查看当前用户的权限
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
ALTER SYSTEM
CREATE SESSION
CREATE TABLE
SELECT ANY TABLE
CREATE ANY INDEX
ALTER ANY INDEX
DROP ANY INDEX
CREATE PUBLIC SYNONYM
DROP PUBLIC SYNONYM
CREATE VIEW
CREATE ANY VIEW
PRIVILEGE
----------------------------------------
DROP ANY VIEW
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE ANY TRIGGER
CREATE TYPE
CREATE LIBRARY
CREATE OPERATOR
CREATE INDEXTYPE
ADMINISTER DATABASE TRIGGER
INHERIT ANY PRIVILEGES
已选择 22 行。
一些权限的说明。
CREATE SESSION 连接到Oracle。
CREATE TABLE CREATE TRIGGER 允许建表和触发器
CREATE SEQUENCE 允许建序列
CREATE PROCEDURE 创建存储过程
ADMINISTER DATABASE TRIGGER 管理触发器
10.查看当前用户拥有的表
SQL> select table_name from user_tables;
TABLE_NAME
--------------------------------------------------------------------------------
VERSION
SPATIAL_REFERENCES
GEOMETRY_COLUMNS
LAYERS
METADATA
RASTER_COLUMNS
TABLE_REGISTRY
COLUMN_REGISTRY
STATES
VERSIONS
MVTABLES_MODIFIED
TABLE_NAME
--------------------------------------------------------------------------------
STATE_LINEAGES
LOCATORS
SDE_XML_INDEXES
SDE_XML_INDEX_TAGS
SDE_XML_COLUMNS
INSTANCES
SDE_ARCHIVES
SDE_LAYER_STATS
BRANCHES
BRANCH_TABLES_MODIFIED
MULTIBRANCH_TABLES
TABLE_NAME
--------------------------------------------------------------------------------
TABLES_LAST_EDIT_TIME
LAYER_LOCKS
STATE_LOCKS
TABLE_LOCKS
OBJECT_LOCKS
PROCESS_INFORMATION
SDE_TABLES_MODIFIED
LINEAGES_MODIFIED
SDE_LOGFILE_POOL
DBTUNE
SERVER_CONFIG
TABLE_NAME
--------------------------------------------------------------------------------
ST_COORDINATE_SYSTEMS
ST_SPATIAL_REFERENCES
ST_GEOMETRY_COLUMNS
ST_GEOMETRY_INDEX
ST_UNITS_OF_MEASURE
ST_PARTITION_INDEX
GDB_TABLES_LAST_MODIFIED
GDB_ITEMS
SDE_XML_DOC1
DR$XMLDOCIX1_TX$I
DR$XMLDOCIX1_TX$R
TABLE_NAME
--------------------------------------------------------------------------------
SDE_XML_DOC2
DR$XMLDOCIX2_TX$I
DR$XMLDOCIX2_TX$R
SDE_XML_DOC3
DR$XMLDOCIX3_TX$I
DR$XMLDOCIX3_TX$R
GDB_ITEMRELATIONSHIPS
SDE_XML_DOC4
DR$XMLDOCIX4_TX$I
DR$XMLDOCIX4_TX$R
GDB_ITEMTYPES
TABLE_NAME
--------------------------------------------------------------------------------
GDB_ITEMRELATIONSHIPTYPES
GDB_REPLICALOG
GDB_LOCKS
GDB_CONFLICTS
SDE_LOGFILES
SDE_LOGFILE_DATA
DR$XMLDOCIX1_TX$K
DR$XMLDOCIX1_TX$N
DR$XMLDOCIX2_TX$K
DR$XMLDOCIX2_TX$N
DR$XMLDOCIX3_TX$K
TABLE_NAME
--------------------------------------------------------------------------------
DR$XMLDOCIX3_TX$N
S1_IDX$
DR$XMLDOCIX4_TX$K
DR$XMLDOCIX4_TX$N
已选择 70 行。
11.查看索引
SQL> select index_name,table_name from user_indexes order by table_name;
