[Command] Oracle Command 2
[DB lock 풀기]
# alter system kill session 'SID No,Serial#';
[DB lock 확인]
# select a.object_name name, b.session_id sid, c.serial#, c.machine
from dba_objects a, v$locked_object b, v$session c
where a.object_id=b.object_id and b.session_id=c.sid;
File name, tablespace 확인 select file_name, tablespace_name, bytes/1024/1024 from dba_data_files;
Flile name 및 size 확인 select file_name, bytes/1024/1024 MB from dba_data_files
where tablespace_name=[Tablespace 명];
[oracle 커넥션 유저수 확인]
# pss oraclevenus | wc -l
[Oracle SID 변경하기]
# set ORACLE_SID=GRPPJT
[Oradim 사용하기]
1. 서비스에서 database 삭제하기
# oradim -delete -sid MARS
2. 서비스에서 database 등록하기
# oradim -new -sid MARS -intpwd qkfka09 -startmode auto -PFILE D:\oracle\admin\MARS\pfile\initMARS.ora
[OS PID확인하기]
# select p.spid from v$process p, v$session s
where p.addr=s.paddr and s.sid=['sid명'] and s.serial#=['sid 시리얼명'];
[OS 파라미터 확인]
# lsattr -El sys0
# aioo -a
[Redo Log 확인 및 mirror 걸기]
1. redo log 확인하기
# select * from v$logfile;
2. redo log mirror 걸기
# alter database add logfile member 'E:\oracle\oradata2\MARS\REDO01_M.LOG' to group 1;
3. status 상태 변경하기
# archive log list;
# alter database archivelog;
[Session 별 사용 SGA 확인]
# select c.sid,b.name,a.value
from v$sesstat a,v$statname b, v$session c
where a.sid=c.sid
and a.statistic#=b.statistic#
and b.name in ('session pga memory','session pga memory max');
[SID, PID 확인하기]
select vs.sid, vs.username, vs.osuser, vs.process fg_pid,
vp.spid bg_bid
from v$session vs, v$process vp
where vs.paddr = vp.addr and vs.sid = ['SID명']
[sid로 serial#확인하기]
# select sid, SERIAL# from v$session where sid=['sid명'];
[sid로 session kill 하기]
# USERNAME SID SERIAL# EXTENTS SPACE
[spfile을 pfile 변경]
# create pfile from spfile;
[오라클 사용자 Memory 할당]
# orastack.exe oracle.exe 500000
[일부 데이터 파일만 확인하기]
# select a.file_name from dba_data_files a, dba_extents b where a.file_id=b.file_id and
b.segment_name='MST_DEV_STG' group by a.file_name;
[조회 권한만 주기]
# grant select any table to qnet;
[파일명 조회하기]
# select file_name from dba_temp_files;
# select file_name from dba_temp_files;
[PID로 Oracle SID 확인하기]
# select a.spid, b.sid, b.SERIAL# from v$process a, v$session b where a.spid='16422' and a.addr=b.paddr;
[INDX 및 Table 용량 확인(Datapump)]
# select sum(bytes/1024/1024/1024) from dba_segments where segment_type in ('INDEX','INDEX PARTITION','INDEX SUBPARTITION');
# select sum(bytes/1024/1024/1024) from dba_segments where segment_type in ('TABLE','TABLE PARTITION','TABLE SUBPARTITION');
# select tablespace_name,sum(bytes/1024/1024) from dba_segments where segment_type in ('INDEX','INDEX PARTITION','INDEX SUBPARTITION') group by tablespace_name;
select tablespace_name,sum(bytes/1024/1024) from dba_segments where segment_type in ('TABLE','TABLE PARTITION','TABLE SUBPARTITION') group by tablespace_name;
[extents 사용률 확인]
# select * from gv$sort_usage order by extents desc;
[index & table data 용량확인]
# select sum(bytes/1024/1024/1024) from dba_segments where segment_type in ('INDEX','INDEX PARTITION','INDEX SUBPARTITION');
# select sum(bytes/1024/1024/1024) from dba_segments where segment_type in ('TABLE','TABLE PARTITION','TABLE SUBPARTITION');
[Table 용량 확인]
# select owner, segment_name,partition_name,segment_type,bytes/1024/1024 from dba_segments where sgment_name='SDB_TN_SN_EXTSCN';
[segmnet 사용량 확인]
# select owner, segment_name, segment_type, bytes/1024/1024 MB from dba_segments where tablespace_name='SYSTEM' and bytes > 10000000 order by 4;
[Bad Block 체크]
# select * from v$database_block_corruption;