常用命令

最新推荐文章于 2024-09-15 08:03:51 发布

go_away_gui

最新推荐文章于 2024-09-15 08:03:51 发布

阅读量424

点赞数

文章标签：数据库

本文链接：https://blog.csdn.net/go_away_gui/article/details/48003287

版权

-- 表空间查询

select a.tablespace_name,a.m total, b.m free, trunc(b.m*100/a.m, 2) rate from (
select tablespace_name, sum(bytes)/1024/1024 M from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 M from dba_free_space group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by 1;

检查数据库坏块

rman target sys/oracle
RMAN> backup validate check logical database;

查询数据变化时的SCN号
select versions_xid,col1 from testb versions between scn minvalue and maxvalue;

select operation,undo_sql,start_scn from flashback_transaction_query where xid=hextoraw('实际的versions_xid');
根据SCN号查询时间，数据必须存在undo中
select to_char(scn_to_timestamp(26018109), 'yyyy-mm-dd hh24:mi:ss') from dual;
根据时间查询SCN号，数据必须存在undo中
select timestamp_to_scn(to_timestamp('20141224 135959', 'yyyymmdd hh24miss')) from dual;

查询系统当前SCN号
select dbms_flashback.get_system_change_number from dual;
select current_scn from v$database;

查询undo中，表数据的变化，数据不在undo时，查询为空
select t.*,versions_xid,VERSIONS_STARTSCN,VERSIONS_STARTTIME,VERSIONS_ENDSCN,VERSIONS_ENDTIME,VERSIONS_OPERATION
from testb versions between scn minvalue and maxvalue t;

查询系统最小SCN
SELECT SCN_WRP*4294967296+SCN_BAS FROM SYS.SMON_SCN_TIME WHERE TIME_MP=(SELECT MAX(TIME_MP) FROM SYS.SMON_SCN_TIME);

闪回表数据
flashback table testb to scn 1234566;

查询闪回数据库日志
select * from v$flashback_database_logfile;

查看数据库归档模式和闪回状态
select name,flashback_on,log_mode from v$database;

查询回收站信息
select * from user_recyclebin;

查询性能消耗最高的SQL，v.elapsed_time单位微妙
select *
　　from （select v.sql_id,
　　v.child_number,
　　v.sql_text,
　　v.elapsed_time,
　　v.cpu_time,
　　v.disk_reads,
　　rank（） over（order by v.elapsed_time desc） elapsed_rank
　　from v$sql v） a
　　where elapsed_rank <= 10;

查询一段时间内运行的SQL
select * from v$sql where sql_id in(
select sql_id from v$sqlarea where last_active_time > to_date('2015011510','yyyymmddhh24'));

AWR报告中手动添加snap id
begin
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
end;
/

观察oracle自动分析任务
select owner,job_name,program_name from dba_scheduler_jobs;
SELECT * FROM DBA_SCHEDULER_PROGRAMS;

-----------------------
数据库11.0.2.0.2之后，默认创建分区表分区大小8M，修改session，创建分区大小0.0625M

alter session set "_index_partition_large_extents"=false;

_partition_large_extents

alter table test modify subpartition P1_SP3 deallocate unused keep 1K;
进行回收，回收后，空间被释放，并且INITIAL的值也会改小。

对于分区
alter table test modify partition P3 storage (initial 1K)

另外，其实在分区表上执行
alter table test deallocate unused keep 1K;
可以对该表所有的分区/子分区都进行回收，语句收回所有表的未使用表空间
begin
for v in (select table_name from user_tables) loop
begin
execute immediate 'alter table '||v.table_name||' deallocate unused keep 1K';
exception
when others then
null;
end;

end loop;
end;
/

------------------------
临时表删除时，提示ORA-14450，临时表被使用，释放使用步骤如下
BEGIN

FOR V IN (SELECT 'ALTER SYSTEM KILL SESSION '''||A.SID||','||A.SERIAL#||'''' SQLS FROM V$SESSION A
WHERE SID IN (SELECT SID FROM V$ENQUEUE_LOCK T WHERE T.TYPE='TO')) LOOP
EXECUTE IMMEDIATE V.SQLS;
END LOOP;

END;
/

禁用触发器
declare
v_sql varchar2(1000);
begin
for v in (select table_name from user_tables) loop

--v_sql := 'alter table '||v.table_name||' disable all triggers';
v_sql := 'alter table '||v.table_name||' enable all triggers';
begin
execute immediate v_sql;
exception
when others then
dbms_output.put_line(v_sql);
end;
end loop;
end;

查询delete掉的表数据
SELECT * FROM xxx AS OF TIMESTAMP to_timestamp('2015-03-29 17:40:00','yyyy-mm-dd hh24:mi:ss');

审计某张表
alter system set audit_trail=db_extended scope=spfile;
startup force;
audit all on sa.emp by access;
观察审计信息
select * from sys.aud$;
操作失败的记录
select * from sys.aud$ where returncode>0;

停止某个用户下的所有进程
ps -fu users|awk '{print $2}'|grep -v PID |xargs kill -9

清理SGA，清除执行计划和缓冲区数据
alter system flush shared_pool;
alter system flush buffer_cache;