常用的oracle管理命令
1.查看表空间大小（包括总大小、使用大小等）SELECT D.TABLESPACE_NAME, D.TOT_GROOTTE_MB TOTAL_SPACE, F.TOTAL_BYTES UNUSED_SPACE, ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100)USER_RATIO
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
WHERE TABLESPACE_NAME NOT IN ('SYSTEM','SYSAUX','EXAMPLE')
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY TABLESPACE_NAME) D
where trim(D.tablespace_name) = trim(F.tablespace_name)
order by ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100) DESC;
2. 查看某表空间下表的占用
Select Segment_Name, Sum(bytes)/1024/1024 use_space
From User_Extents t
where t.tablespace_name='USERS'
Group By Segment_Name
order by use_space desc
3.查看表的大小
Select Segment_Name, Sum(bytes)/1024/1024
From User_Extents t
where Segment_Name='DW_DM_SEARCH_20110809'
Group By Segment_Name
4.查看对象的创建和修改时间
SELECT OBJECT_NAME, --对象名
OBJECT_TYPE, --对象类型
TO_CHAR(CREATED, 'YYYY-Mon-DD HH24:MI') CREATE_TIME, --创建时间
TO_CHAR(LAST_DDL_TIME, 'YYYY-Mon-DD HH24:MI') MOD_TIME, --修改时间
TIMESTAMP, --时间戳
STATUS --状态
FROM USER_OBJECTS t
WHERE to_char(LAST_DDL_TIME,'yyyymmdd')>'20120625'
and t.object_type='PACKAGE BODY'
5. 查看表空间对于的数据文件
select * from DBA_DATA_FILES t where t.tablespace_name='ODS1_09';
6. 修改表空间的大小
alter database datafile '/a8root/app/oracle/oradata/dmbi/tablespace/ODS1_09.tbl' resize 3000M;
页:
[1]