Oracle生产日常开发维护总结

• oracle log

oracle 11g以后alert.log隐藏很深，例如：/u01/app/oracle/diag/rdbms/test/test/trace/alerttest.log

• 表空间

1、查看表空间的名称及大小 
SQl > SELECT t.tablespace_name,round(SUM(bytes / (1024 * 1024)),0) ts_size FROM dba_tablespaces t,dba_data_files d WHERE t.tablespace_name = d.tablespace_name GROUP BY t.tablespace_name; 
2、查看表空间物理文件的名称及大小 
SQl > SELECT tablespace_name,file_id,file_name,round(bytes / (1024 * 1024),0) total_space FROM dba_data_files ORDER BY tablespace_name; 
3、查看回滚段名称及大小 
SQl > SELECT segment_name,tablespace_name,r.status,(initial_extent / 1024) initialextent,(next_extent / 1024) nextextent,max_extents,v.curext curextent FROM dba_rollback_segs r,v$rollstat v WHERE r.segment_id = v.usn(+) ORDER BY segment_name; 
4、查看控制文件 
SQl > SELECT NAME FROM v$controlfile; 
5、查看日志文件 
SELECT MEMBER FROM v$logfile; 
6、查看表空间的使用情况 
SQl > SELECT SUM(bytes) / (1024 * 1024) AS free_space,tablespace_name FROM dba_free_space GROUP BY tablespace_name; 
SQl > SELECT a.tablespace_name,a.bytes total,b.bytes used,c.bytes free,(b.bytes * 100) / a.bytes "% USED ",(c.bytes * 100) / a.bytes "% FREE " 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; 
7、查看数据库库对象 
SQl > SELECT owner,object_type,status,COUNT(*) count# FROM all_objects GROUP BY owner,status; 
8、查看数据库的版本　 
SQl > SELECT version FROM product_component_version WHERE substr(product,1,6) = 'Oracle'; 
9、查看数据库的创建日期和归档方式 
SQl > SELECT created,log_mode,log_mode FROM v$database;

• 数据表空间使用率查询

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,5) 利用率
 from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.bytes
order by b.tablespace_name;

  --临时表空间查询
SELECT TABLESPACE_NAME,FILE_ID,FILE_NAME,BYTES/1024/1024 AS "SPACE(M)"
FROM DBA_TEMP_FILES
WHERE TABLESPACE_NAME = 'aaa';

• 表空间数据文件存放位置

select t1.name,t2.name from v$tablespace t1,v$datafile t2 where t1.ts# = t2.ts#;

• 创建表空间及添加数据文件

CREATE TABLESPACE TS_IMAGEDATA
DATAFILE 'E:"ORACLE"ORADATA"DATA_01.DBF' SIZE 2000M REUSE 
 AUTOEXTEND ON 
 NEXT 51200K MAXSIZE 3900M,'E:"ORACLE"ORADATA"XL"DATA_02.DBF' SIZE 2000M REUSE 
 AUTOEXTEND ON 
 NEXT 51200K MAXSIZE 3900M,'E:"ORACLE"ORADATA"XL"DATA_03.DBF' SIZE 2000M REUSE 
 AUTOEXTEND ON 
 NEXT 51200K MAXSIZE 3900M,'E:"ORACLE"ORADATA"XL"DATA_04.DBF' SIZE 2000M REUSE 
 AUTOEXTEND ON 
 NEXT 51200K MAXSIZE 3900M 
EXTENT MANAGEMENT LOCAL 
SEGMENT SPACE MANAGEMENT AUTO; 
1：创建临时表空间
create temporary tablespace user_temp 
tempfile 'Q:oracleproduct10.2.0oradataTestxyrj_temp.dbf' 
size 50m 
autoextend on 
next 50m maxsize 20480m 
extent management local; 
2：创建数据表空间
create tablespace user_data 
logging 
datafile 'Q:oracleproduct10.2.0oradataTestxyrj_data.dbf' 
size 50m 
autoextend on 
next 50m maxsize 20480m 
extent management local; 
第一种方式：表空间增加数据文件
1、alter tablespace spacess1 add datafile 'G:spacess01.DBF' size 30000M;
第二种方式：表空间增加数据文件，设置自增长，限制最大值
2、alter tablespace spacess1 add datafile 'G:spacess01.DBF' size 500M autoextend on maxsize 3072M; 
临时表空间添加文件
ALTER TABLESPACE &tablespace_name ADD TEMPFILE '&datafile_name' SIZE 2G;
删除表空间
drop tablespace xxx including contents and datafiles;

• oracle结束死锁进程

select sess.sid,sess.serial#,lo.oracle_username,lo.os_user_name,ao.object_name,lo.locked_mode
 from v$locked_object lo,dba_objects ao,v$session sess
where ao.object_id = lo.object_id and lo.session_id = sess.sid; 
select saddr,sid,serial#,paddr,username,status from v$session where username =upper('sys');
alter system kill session '46,53';
---解决锁表
1、运行sql： select session_id from v$locked_object; 查出锁表的session，可能很多，正常是没有的
2、SELECT sid,osuser FROM v$session where sid = 162; 将162换成你查出来的session_id
3、kill掉session
ALTER SYSTEM KILL SESSION '162,4294'; 4294为第二步查出来的serial#,

• oracle结束死锁sql

select * from v$session t1,v$locked_object t2 where t1.sid = t2.SESSION_ID;

• oracle导入导出虚拟目录

create directory db_bak as 'd:backup';
select * from dba_directories;
grant read,write on directory db_bak to system;
--1，创建虚拟目录
create or replace directory exp_dir as '/tmp';
--2，授权虚拟目录
grant read,write on directory exp_dir to eygle;
--3，查询虚拟目录
select * from dba_directories;
--4，删除虚拟目录
drop directory exp_dir;

• oracle最近sql执行情况

select * from all_objects t where to_char(t.created,'yyyy-MM-dd')='2015-12-02' and owner='aaa' and t.object_type='TABLE'

（编辑：李大同）

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