59.Oracle杂记——Oracle办公常用命令

1. 查看表空间利用率

SELECT D.TABLESPACE_NAME,

SPACE || 'M' "SUM_SPACE(M)",

BLOCKS "SUM_BLOCKS",

SPACE - NVL (FREE_SPACE,0) || 'M' "USED_SPACE(M)",

ROUND ( (1 - NVL (FREE_SPACE,0) / SPACE) * 100,2) || '%'

"USED_RATE(%)",

FREE_SPACE || 'M' "FREE_SPACE(M)"

FROM ( SELECT TABLESPACE_NAME,

ROUND (SUM (BYTES) / (1024 *1024),2) SPACE,

SUM (BLOCKS) BLOCKS

FROM DBA_DATA_FILES

GROUP BY TABLESPACE_NAME) D,

( SELECT TABLESPACE_NAME,2) FREE_SPACE

FROM DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME) F

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

UNION ALL

SELECT D.TABLESPACE_NAME,

BLOCKS SUM_BLOCKS,

USED_SPACE || 'M' "USED_SPACE(M)",

ROUND (NVL (USED_SPACE,0) / SPACE * 100,2) || '%'"USED_RATE(%)",

NVL (FREE_SPACE,0) || 'M' "FREE_SPACE(M)"

FROM ( SELECT TABLESPACE_NAME,

SUM (BLOCKS) BLOCKS

FROM DBA_TEMP_FILES

GROUP BY TABLESPACE_NAME) D,

ROUND (SUM (BYTES_USED) /(1024 * 1024),2) USED_SPACE,

ROUND (SUM (BYTES_FREE) /(1024 * 1024),2) FREE_SPACE

FROM V$TEMP_SPACE_HEADER

GROUP BY TABLESPACE_NAME) F

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

ORDER BY 1;

2. 数据文件使用率

select b.file_name,b.tablespace_name,b.bytes/1024/1024 M,(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 M,substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) from dba_free_space a,dba_data_files b wherea.file_id=b.file_id group by b.tablespace_name,b.file_name,b.bytes order byb.tablespace_name;

3. AWR策略

select * from dba_hist_wr_control;

修改策略：

execdbms_workload_repository.modify_snapshot_settings(interval=>30,retention=>9*24*60);

execdbms_workload_repository.modify_snapshot_settings(interval=>0);

3.1禁止快照

EXECdbms_workload_repository.modify_snapshot_settings(interval=>0);

3.2创建一个快照

execDBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

3.3查找快照

SELECT snap_id,INSTANCE_NUMBER,begin_interval_time,end_interval_time FROM dba_hist_snapshot ORDER BY 1;

3.4删除指定范围的快照

execWORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(low_snap_id => 22,high_snap_id=> 32,dbid => 3310949047);

3.5 产生AWR报告

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

4. 执行CHECKPOINT

alter system checkpoint;

5. 开启内存大页

vm.nr_hugepages = 262144

vm.hugetlb_shm_group = 501

或者

echo 501 >/proc/sys/vm/hugetlb_shm_group

6. 查看表中的列数量

SQL> select table_name,num_rows fromuser_tables;

7.查看索引

SQL>select index_name,index_type fromind;

8.查看表

SQL>select tname,tabtype from tab;

9. 查看过程对象

SQL> select object_name from user_procedures;

10. 查看 归档

archive log list

11. 增加一个asm磁盘

SQL> ALTER DISKGROUP DATA ADD DISK'/dev/raw/raw5';

12. 查看用户段大小

SQL> select sum(bytes)/1024/1024 as MBfrom user_segments;

13.小压力测试脚本

SET SERVEROUTPUT ON

SET TIMING ON

DECLARE

n NUMBER := 0;

BEGIN

FOR f IN 1..10000000

LOOP

n :=MOD (n,999999) + SQRT (f);

END LOOP;

DBMS_OUTPUT.PUT_LINE ('Res = '||TO_CHAR(n,'999999.99'));

END;

/

输出如下：

Res =873729.72

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.21

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!