db19检查

ps -ef | grep smon

ps -ef | grep tns

lsnrctl status

tnsping cdb1

select file_name from dba_data_files;

 select instance_name,status from v$instance;

select name,open_mode from v$database; 

select max(sequence#) from v$archived_log; 

 列出数据库中所有无效对象

SELECT owner,

       object_type,

       object_name,

       status

FROM   dba_objects

WHERE  status = 'INVALID'

ORDER BY owner, object_type, object_name;

 数据库注册表组件

select comp_name,version,status from dba_registry;

 实例启动时间

SELECT INSTANCE_NAME,TO_CHAR(STARTUP_TIME, 'HH24:MI DD-MON-YY') "STARTUP TIME"

FROM V$INSTANCE;

 启动历史

SELECT INSTANCE_NAME,TO_CHAR(STARTUP_TIME, 'HH24:MI DD-MON-YY') FROM DBA_HIST_DATABASE_INSTANCE

ORDER BY STARTUP_TIME DESC;

数据库的运行时间 

SELECT
'HOSTNAME : ' || HOST_NAME
,'INSTANCE NAME : ' || INSTANCE_NAME
,'STARTED AT : ' || TO_CHAR(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') STIME
,'UPTIME : ' || FLOOR(SYSDATE - STARTUP_TIME) || ' DAYS(S) ' ||
TRUNC( 24*((SYSDATE-STARTUP_TIME) -
TRUNC(SYSDATE-STARTUP_TIME))) || ' HOUR(S) ' ||
MOD(TRUNC(1440*((SYSDATE-STARTUP_TIME) -
TRUNC(SYSDATE-STARTUP_TIME))), 60) ||' MINUTE(S) ' ||
MOD(TRUNC(86400*((SYSDATE-STARTUP_TIME) -
TRUNC(SYSDATE-STARTUP_TIME))), 60) ||' SECONDS' UPTIME
FROM
SYS.V_$INSTANCE;

数据库的实际大小

 SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_data_files;

 数据库中数据占用大小

SELECT SUM (bytes)/1024/1024/1024 AS GB FROM dba_segments;

 检查数据库加上临时文件和重做文件的总体大小

select
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
from dual;

 检查 User 或 Schema 的大小

select owner, sum(bytes)/1024/1024 Size_MB from dba_segments group by owner;

DBA_SEGMENTS 描述为数据库中的所有段分配的存储

检查数据库的 PDB 大小 

select con_id, name, open_mode, total_size from v$pdbs;

 检查 CDB 和 PDB 数据库中数据文件的大小

select a.con_id,c.name,b.tablespace_name,a.bytes_alloc/(1024*1024) "MAXSIZE (MB)",nvl(a.physical_bytes,0)/(1024*1024) "ALLOC (MB)" ,nvl(b.tot_used,0)/(1024*1024) "USED (MB)"
from
(select con_id,tablespace_name, sum(bytes) physical_bytes,sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
from cdb_data_files group by con_id,tablespace_name ) a,
(select con_id,tablespace_name, sum(bytes) tot_used from cdb_segments group by con_id,tablespace_name ) b,
(select name,con_id from v$containers) c
where a.con_id= b.con_id and a.con_id = c.con_id and a.tablespace_name = b.tablespace_name (+)
order by 1,3;

 检查 CDB 和 PDB 数据库中临时表空间的大小

select a.con_id,c.name,a.tablespace_name,a.bytes_alloc/(1024*1024) "MAXSIZE (MB)"
,nvl(a.physical_bytes,0)/(1024*1024) "ALLOC (MB)"
from
(select con_id,tablespace_name, sum(bytes) physical_bytes,sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
from cdb_temp_files group by con_id,tablespace_name ) a,
(select name,con_id from v$containers) c
where a.con_id = c.con_id
order by 1,3;

查看 undo 表空间总、空闲和已用空间 

SELECT a.tablespace_name,
SIZEMB,
USAGEMB,
(SIZEMB - USAGEMB) FREEMB
FROM ( SELECT SUM (bytes) / 1024 / 1024 SIZEMB, b.tablespace_name
FROM dba_data_files a, dba_tablespaces b
WHERE a.tablespace_name = b.tablespace_name AND b.contents like 'UNDO'
GROUP BY b.tablespace_name) a,
( SELECT c.tablespace_name, SUM (bytes) / 1024 / 1024 USAGEMB
FROM DBA_UNDO_EXTENTS c
WHERE status <> 'EXPIRED'
GROUP BY c.tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name;

 检查撤消表空间中的活动、过期和未过期事务空间使用情况

select tablespace_name tablespace, status, sum(bytes)/1024/1024 sum_in_mb, count(*) counts

from dba_undo_extents

group by tablespace_name, status order by 1,2;

表空间利用率

SELECT d.status "Status", d.tablespace_name "Name", 
 TO_CHAR(NVL(a.bytes / 1024 / 1024 /1024, 0),'99,999,990.90') "Size (GB)", 
 TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024 /1024,'99999999.99') "Used (GB)", 
 TO_CHAR(NVL(f.bytes / 1024 / 1024 /1024, 0),'99,999,990.90') "Free (GB)", 
 TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "(Used) %"
 FROM sys.dba_tablespaces d, 
 (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a, 
 (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f WHERE 
 d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT 
 (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY') 
UNION ALL 
SELECT d.status 
 "Status", d.tablespace_name "Name", 
 TO_CHAR(NVL(a.bytes / 1024 / 1024 /1024, 0),'99,999,990.90') "Size (GB)", 
 TO_CHAR(NVL(t.bytes,0)/1024/1024 /1024,'99999999.99') "Used (GB)",
 TO_CHAR(NVL((a.bytes -NVL(t.bytes, 0)) / 1024 / 1024 /1024, 0),'99,999,990.90') "Free (GB)", 
 TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "(Used) %" 
 FROM sys.dba_tablespaces d, 
 (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a, 
 (select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t 
 WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND 
 d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY';

查找表空间的数据文件

select file_name,bytes/1024/1024 from dba_data_files

where tablespace_name='&biaokongjian'