首先,我的Oracle是12c,开启了pdb,在此基础上建了sde库。
1.查看pdb,con_name 查看表空间的名称及大小
设置显示条数为100。
set pagesize 100;
设置显示列数为100。
set linesize 100;
开启和关闭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表空间的。
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
2.查看表空间物理文件的名称及大小
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
3.查看回滚端名称及大小
SQL> SELECT segment_name,
2 tablespace_name,
3 r.status,
4 (initial_extent / 1024) initialextent,
5 (next_extent / 1024) nextextent,
6 max_extents,
7 v.curext curextent
8 FROM dba_rollback_segs r, v$rollstat v
9 WHERE r.segment_id = v.usn(+)
10 ORDER BY segment_name;
SEGMENT_NAME TABLESPACE_NAME STATUS INITIALEXTENT
------------------------------ ------------------------------ ---------------- -------------
NEXTEXTENT MAX_EXTENTS CUREXTENT
---------- ----------- ----------
SYSTEM SYSTEM ONLINE 112
56 32765 3
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