Oracle数据库相关SQL操作总结

DBA_SQL

查询表空间使用情况

SELECT A.TABLESPACE_NAME TABLENAME,
    ROUND(A.BYTES/1024/1024/1024,2) "TOTAL(G)",
    ROUND(B.BYTES/1024/1024/1024,2) "USED(G)",
    ROUND(C.BYTES/1024/1024/1024,2) "FREE(G)"
FROM
    SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
AND A.TABLESPACE_NAME = C.TABLESPACE_NAME
AND A.TABLESPACE_NAME NOT IN('USED','SYSTEM', 'SYSAUX','UNDOTBS1')

数据表占用空间大小情况

select segment_name, tablespace_name, bytes, blocks
from user_segments
where segment_type = 'TABLE'
ORDER BY bytes DESC, blocks DESC;

select tablespace_name,round(sqrt(max(blocks)/sum(blocks))*
           (100/sqrt(sqrt(count(blocks)))),2) FSFI
from dba_free_space
group by tablespace_name order by 1;

Automatic Workload Report报告打印过程

1.sqlplus / as sysdba
2.exec dbms_workload_repository.create_snapshot();
3.exec dbms_workload_repository.create_snapshot();
4.@?/rdbms/admin/awrrpt

用户解锁、修改密码、监听、开机、关机、数据库连接

1.sqlplus / as sysdba --登录SYS用户模式
2.alter user scott account unlock; --解锁用户
3.alter user scott identified by tiger;  --修改密码
4.shutdown immediate;    --关机
5.startup;         --开机
查看状态命令：lsnrctl status
启动监听：lsnrctl start
关闭监听:lsnrctl stop
sqlplus / as sysdba
sqlplus 用户名/密码
sqlplus username/password@//ip:port/SID_name

测试Oracle磁盘IOPS性能

DECLARE 
lat INTEGER;
iops INTEGER;
mbps INTEGER;
BEGIN
    DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 10, iops, mbps, lat);
DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
DBMS_OUTPUT.PUT_LINE ('latency  = ' || lat);
dbms_output.put_line ('max_mbps = ' || mbps);
END;

查询redo日志切换频率

SELECT b.SEQUENCE#,  b.FIRST_TIME,  a.SEQUENCE#, a.FIRST_TIME,
    ROUND(((a.FIRST_TIME-b.FIRST_TIME)*24)*60,2) 切换时间
FROM v$log_history a, v$log_history b
WHERE a.SEQUENCE#=b.SEQUENCE#+1 AND b.THREAD#=1
ORDER BY  a.SEQUENCE# DESC;

查询数据库锁情况

SELECT object_name AS 对象名称,  s.sid,  s.serial#,  p.spid AS 系统进程号
FROM  v$locked_object l , dba_objects o ,  v$session s , v$process p
WHERE l.object_id=o.object_id AND l.session_id=s.sid AND s.paddr=p.addr;
清除锁对象:
alter system kill session 'sid,serial#';

多表数据统计显示查询语句

SELECT 'STUDENT' TABLE_NAME,  COUNT(*)  TABLE_COUNT 
FROM STUDENT
WHERE JYRQ IN (TRUNC(SYSDATE))
UNION ALL
SELECT 'TEACHER' TABLE_NAME,  COUNT(*)  TABLE_COUNT 
FROM TEACHER
WHERE JYRQ IN (TRUNC(SYSDATE))

报表数据实时统计

WITH
    OGG_CNT_TPS AS
    (
        SELECT
            CUR_DT,
            LAG(CUR_DT,1,CUR_DT) OVER(PARTITION BY TN ORDER BY CUR_DT ASC) CUR_DT_LAST,
            TN,
            CNT,
            LAG(CNT,1,CNT) OVER(PARTITION BY TN ORDER BY CUR_DT ASC) CNT_LAST
        FROM
            OGG_CNT
        WHERE
            CUR_DT BETWEEN TO_DATE('2020-04-23 11:22:00','YYYY-MM-DD HH24:MI:SS')AND SYSDATE
    )
SELECT
    TN "计算传输的表",
    CUR_DT "当前统计时间",
    CUR_DT_LAST "上次统计时间",
    CNT "当前统计量",
    CNT_LAST "上次统计量",
    CASE
        WHEN CNT>CNT_LAST
        THEN(DECODE(CUR_DT-CUR_DT_LAST,0,0,ROUND(CNT-CNT_LAST)/((CUR_DT-CUR_DT_LAST)*24*3600),2))
        ELSE 0
    END "TPS"
FROM
    OGG_CNT_TPS

建立分区语句

按天建立分区：
create table test_part
(
	ID NUMBER(20) not null,
	REMARK VARCHAR2(1000),
	create_time DATE
)
PARTITION BY RANGE (CREATE_TIME) INTERVAL (NUMTODSINTERVAL(1, 'day'))
(
    partition part_t01 values less than(to_date('2018-11-12', 'yyyy-mm-dd'))
);
创建主键：
alter table test_part add constraint test_part_pk primary key (ID) using INDEX;
查询当前表有多少分区：
select table_name,partition_name from user_tab_partitions where table_name='TEST_PART';
查询表某个分区(SYS_P21)数据：
select * from TEST_PART partition(SYS_P21);
手动添加分区：
alter table t_test add partition t_test_2015 VALUESLESS THAN (TO_DATE('2015-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACETS_MISPS ;
删除分区：
alter table t_test drop partition t_test_2014 

数据库造数

declare
  i    int := 1;
  yearVARCHAR2(20);
begin
  loop
    year := CASEmod(i, 3)
             WHEN 0 THEN
              '2012-01-14 12:00:00'
             WHEN 1 THEN
              '2013-01-14 12:00:00'
             ELSE
              '2014-01-14 12:00:00'
            END;
           insert into t_test values(i, to_date(year, 'yyyy-mm-dd hh24:mi:ss'));
    exit when i= 1000000;
    i := i + 1;
  end loop;
end;