三、查看
数据库的
SQL51Testing软件测试网1d
dP|oqb1、查看表空间的名称及大小
!M XP
A1_2c
S
F(m051Testing软件测试网wo3FyLWZjselect t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
_:zzFx_1]%\0from dba_tablespaces t, dba_data_files d
51Testing软件测试网p-`jSW;fci
cO#{where t.tablespace_name = d.tablespace_name
51Testing软件测试网.RJ#U`9NGgroup by t.tablespace_name;
/_9ne
t GS
T0cU1X}#J`F%p~g02、查看表空间物理文件的名称及大小
V{
C;Bk\P
U%W051Testing软件测试网/G4H,G P
uEcxselect tablespace_name, file_id, file_name,
51Testing软件测试网*A:cDbHround(bytes/(1024*1024),0) total_space
'I8Q0]!Z8u"E+}C4[oy0from dba_data_files
k;Wo8HQ{Q8`0order by tablespace_name;
51Testing软件测试网.vM,o+|.tz(o$|F*p3T B03、查看回滚段名称及大小
51Testing软件测试网X1t,UZ+l@51Testing软件测试网9B!RZZ#G/v Z7qselect segment_name, tablespace_name, r.status,
51Testing软件测试网 `+J c|mc(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,
51Testing软件测试网's:~.J.f[5@+M tmax_extents, v.curext CurExtent
51Testing软件测试网.Cf\7n U8q;Y\R%b(AFrom dba_rollback_segs r, v$rollstat v
VW0iWc^1e+KD0Where r.segment_id = v.usn( )
$W5O2ueRS0order by segment_name ;
51Testing软件测试网9~METD4r m51Testing软件测试网,mh,P'G4UK}4、查看控制文件
U{5I(^U'yo0M`j7C QH_0select name from v$controlfile;
y.q'q c9P051Testing软件测试网r
cR
om[ZEK5、查看
日志文件
51Testing软件测试网){})|f#F*o*A51Testing软件测试网Y,M
C0|P3[W ^select member from v$logfile;
%] RfP u7e6A2S0/V(y.T(JC;g(uP3u06、查看表空间的使用情况
51Testing软件测试网|o5~ IV:B X51Testing软件测试网PY-E;k2V6fLselect sum(bytes)/(1024*1024) as free_space,tablespace_name
{
P%dO8B0from dba_free_space
"hU R1z5_w
uO9a/u0group by tablespace_name;
51Testing软件测试网KSw~K9n,j$Rtp;W5\9se0SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,
51Testing软件测试网
]vwt'b&Rl,Y(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"
d1J|8dm*b6B0FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
C@8kn d5{:U@0WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;
51Testing软件测试网W4B
i)a3k g51Testing软件测试网n
oC#lT$bO;hDv8U7、查看数据库库对象
51Testing软件测试网)v$o(\w0K51Testing软件测试网6CQ#Vx|k [yRselect owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;
+uI0k1h*}'\.~D051Testing软件测试网,t:FWy%Ed
[8、查看数据库的版本
51Testing软件测试网E/tg?HjDnH"UG%[ENyc2xv/Tf0Select version FROM Product_component_version
\PL
xx$iq0Where SUBSTR(PRODUCT,1,6)='Oracle';
51Testing软件测试网,Ua+} B:~0ld
e&D(S2az0tN;U)}7U09、查看数据库的创建日期和归档方式
51Testing软件测试网:R
Abup.K51Testing软件测试网u8z9CH8K.k l
rMSelect Created, Log_Mode, Log_Mode From V$Database;
51Testing软件测试网.J"\C4kTD)zKK四、
ORACLE用户连接的管理
2o$l%F4Gh*MOA06eMb1Z&S-\I9fnd0用系统管理员,查看当前数据库有几个用户连接:
Nm3J H~a0KZH1Ifr0SQL> select username,sid,serial# from v$session;
51Testing软件测试网7|~1\'s,fC
H"m/j51Testing软件测试网9R,_E;} J%A+y)M-S如果要停某个连接用
["n^ ws0T.k051Testing软件测试网 `K ax:PSSQL> alter system kill session 'sid,serial#';
!yV t2F*WI051Testing软件测试网 Jtf.Vr ~f如果这命令不行,找它UNIX的进程数
sIk(FZz-j0M#^)?3krq
g0SQL> select pro.spid from v$session ses,v$process pro where ses.sid=21 and ses.paddr=pro.addr;
6_ A:o~(av0(c ? {hG1D0\-hs0说明:21是某个连接的sid数
X3m?
y'v&v09B!@3Oi{~Nu0然后用 kill 命令杀此进程号。