[DBA 권한 제거 전 확인] # SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = '[계정명]' ; # SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = '[계정명]' ; [DBA 권한 제거] # Revoke dba from [계정명] [resource 권한주기] # Grant connect, resource to [계정명]

[Tablespace file 찾기] # select file_name, bytes/1024/1024 MB from dba_data_files where tablespace_name='NTSS_IDX'; [Tablespace name 찾기] # select tablespace_name from dba_tablespaces; [Tablespace, size 확인] # select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name; [Tablespace size 변경] # alter database datafile 'E:\ORA92\MARS\NTSSDATA01.DBF' resize 2000m; [Tablespac..

[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 M..

[DB SID 확인] # select name from v$database; [DATA File 위치 확인] # select * from dba_data_files; [DB Instance 확인] # select * frm v$instance; [DB 프로세스 확인] # ps -ef | grep pmon # ps -ef | grep ora [리스너 프로세스 확인] # ps -ef | grep lsnr [Archive 모드 변경] 1. # shutdown immediate 2. # startup mount 3. # alter database archivelog; 4. # alter database open; [Archive 모드 확인] 1. # Select name, log_mode from v$datab..

CI계정 권한 확인 POSTPROD@ADT1> select * from user_sys_privs; USERNAME PRIVILEGE ADMIN_OPT -------------------- ------------------------------------------------------------------------------------------------------------------------ --------- POSTPROD CREATE VIEW NO POSTPROD CREATE SESSION NO POSTPROD UNLIMITED TABLESPACE NO POSTPROD@ADT1> select * from user_role_privs; USERNAME GRANTED_ROLE ADMIN_OPT..

[테이블스페이스 사용량 조회 최신쿼리] SELECT substr(a.tablespace_name,1,30) tablespace, round(sum(a.total1)/1024/1024,1) "TotalMB", round(sum(a.total1)/1024/1024,1)-round(sum(a.sum1)/1024/1024,1) "UsedMB", round(sum(a.sum1)/1024/1024,1) "FreeMB", round((round(sum(total1)/1024/1024,1)-round(sum(a.sum1)/1024/1024,1))/round(sum(a.total1)/1024/1024,1)*100,2) "Used%" FROM (select tablespace_name, 0 total1, sum(bytes..