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:\oracle\product\10.2.0\oradata\Test\xyrj_temp.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
2：创建数据表空间
create tablespace user_data
logging
datafile 'Q:\oracle\product\10.2.0\oradata\Test\xyrj_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;
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#,
select * from v$session t1,v$locked_object t2 where t1.sid = t2.SESSION_ID;
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;
select * from all_objects t where to_char(t.created,'yyyy-MM-dd')='2015-12-02' and owner='aaa' and t.object_type='TABLE'
