Oracle扩展表空间

沃克斯

于 2023-04-08 00:21:55 发布

阅读量287

点赞数

文章标签： oracle 数据库

本文链接：https://blog.csdn.net/zhaolei742/article/details/130023151

版权

1. 看表空间使用状况,根据百分比查看使用率判定是否需要扩

Select f.Tablespace_Name Tablespace_Name, Round((d.Sumbytes / 1024 / 1024 / 1024), 2) Total_g,
       Round(f.Sumbytes / 1024 / 1024 / 1024, 2) Free_g, Round((d.Sumbytes - f.Sumbytes) / 1024 / 1024 / 1024, 2) Used_g,
       Round((d.Sumbytes - f.Sumbytes) * 100 / d.Sumbytes, 2) Used_Percent
From (Select Tablespace_Name, Sum(Bytes) Sumbytes From Dba_Free_Space Group By Tablespace_Name) f,
     (Select Tablespace_Name, Sum(Bytes) Sumbytes From Dba_Data_Files Group By Tablespace_Name) d
Where f.Tablespace_Name = d.Tablespace_Name
Order By d.Tablespace_Name;

2. 看原有表空间的位置

select tablespace_name,file_name,round(bytes/(1024*1024), 0) total_space from dba_data_files where tablespace_name = '表空间名'

3. 扩表空间的语句，地址从上面的查询中获取到，并递增数字，空间大小也与以前一致

alter tablespace 表空间名 add datafile 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\XX_02.ORA' size 32760M

4. 收集表统计信息

exec dbms_stats.gather_table_stats(ownname=>'XX',tabname => 'XXX');

5. 确定碎片程度

select table_name,round((blocks * 8),2) "高水位空间(K)",
round((num_rows * avg_row_len /1024),2) "真实使用空间(K)",
round((blocks * 10 /100)*8,2) "预留空间(K)",
round((blocks * 8 -(num_rows * avg_row_len /1024) -blocks * 10 /100),2) "浪费空间(K)"
from dba_tables
where table_name='XXXX';

6. 整理表，不影响dml操作

alter table XXXX enable row movement;
alter table XXXX shrink space compact;

7. 重置高水位，不能有dml操作

alter table XXXX shrink space;
alter table XXXX disable row movement;

8. 查看oracle 正在执行的任务

select a.program,b.spid,c.sql_text,c.SQL_ID,a.*
from v$session a, v$process b, v$sqlarea c
where a.paddr= b.addr
and a.sql_hash_value = c.hash_value
and a.username ='XX' AND a.CLIENT_INFO='XX.XX.XX.XXX';

9. 查询慢的sql

select *
from (select sa.SQL_TEXT,
sa.SQL_FULLTEXT,
sa.EXECUTIONS "执行次数",
round(sa.ELAPSED_TIME/1000000,2) "总执行时间",
round(sa.ELAPSED_TIME/1000000/sa.EXECUTIONS,2) "平均执行时间",
sa.COMMAND_TYPE,
sa.PARSING_USER_ID "用户ID",
u.username "用户名",
sa.HASH_VALUE
from v$sqlarea sa
left join all_users u 
on sa.PARSING_USER_ID = u.user_id
where sa.EXECUTIONS >0
order by (sa.ELAPSED_TIME/sa.EXECUTIONS) desc)
where rownum <=50;