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

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,

                 ROUND (SUM (BYTES) / (1024 *1024), 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,

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

      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,

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

                 SUM (BLOCKS) BLOCKS

           FROM DBA_TEMP_FILES

       GROUP BY TABLESPACE_NAME) D,

      (  SELECT TABLESPACE_NAME,

                 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