DB에서 tablespace 를 사용하고 있는 유저 찾는 SQL

select distinct owner from dba_segments where tablespace_name='EXAMPLE';

OWNER
------------------------------
G7TW
GW653
REALSITE

이렇게 하면 example 테이블스페이스를 사용하는 유저가 나옵니다.
삭제 가능한 테스트 유저인지를 확인하고 유저를 drop 시킵니다.
여기서 꼭 삭제가능한 유저인지를 확인하세요.


가령 ORA-01940: 현재 접속되어 있는 사용자는 삭제할 수 없습니다 (혹여 다른 DB툴을 이용해 접속한 경우 삭제되지 않는다)

계정 삭제 시 위와 같은 메시지를 만났을 땐 아래 방법으로 접속된 계정을 강제로 끊을 수 있습니다.

--------------------------------------------------------------------------------------------------------
'TEST' 계정을 강제로 끊고 싶을 때

1. system 계정으로 로그인 한다.

2. 아래 명령으로 'TEST' 계정의 세션ID와 시리얼 번호를 확인한다.
SELECT SID, SERIAL#, USERNAME FROM V$SESSION WHERE SCHEMANAME = 'TEST';

SID              SERIAL#             USERNAME

---              --------             ------------
20                1929                   TEST

3. 위에서 조회된 정보와 를 활용해 'TEST' 계정을 강제로 끊는다.
    ALTER SYSTEM KILL SESSION 명령을 활용한다.

    ALTER SYSTEM KILL SESSION '20, 1929';

drop user 유저명 cascade;
(유저의 모든 객체를 삭제)

데이타파일 정보와 테이블스페이스 정보 조회
V$DATAFILE와 V$DATAFILE 이용

SQL> CONN / AS SYSDBA
SQL> SELECT status,enabled, t.name,d.name
     FROM  V$DATAFILE d, V$TABLESPACE t
     WHERE t.ts#=d.ts#;
 
STATUS   ENABLED     NAME            NAME
-------  ----------  ------------  -------------------------------------
SYSTEM   READ WRITE  SYSTEM        C:\ORACLE\ORADATA\ORACLE\SYSTEM01.DBF
ONLINE   READ WRITE  RBS           C:\ORACLE\ORADATA\ORACLE\RBS01.DBF
ONLINE   READ WRITE  USERS         C:\ORACLE\ORADATA\ORACLE\USERS01.DBF
ONLINE   READ WRITE  TEMP          C:\ORACLE\ORADATA\ORACLE\TEMP01.DBF
ONLINE   READ WRITE  TOOLS         C:\ORACLE\ORADATA\ORACLE\TOOLS01.DBF
ONLINE   READ WRITE  INDX          C:\ORACLE\ORADATA\ORACLE\INDX01.DBF
ONLINE   READ WRITE  DRSYS         C:\ORACLE\ORADATA\ORACLE\DR01.DBF
ONLINE   READ WRITE  STORM         C:\ORACLE\ORADATA\ORACLE\STORM.DBF

   
테이블스페이스의 데이터파일과 테이블스페이스 크기 확인
DBA_DATA_FILES 데이터 사전을 이용 하면 됩니다.

 
SQL> SELECT file_name, tablespace_name, bytes, status
     FROM  DBA_DATA_FILES;
 
FILE_NAME                              TABLESPACE_NAME  BYTES        STATUS
-------------------------------------  ---------------  -----------  ------------
C:\ORACLE\ORADATA\ORACLE\SYSTEM01.DBF  SYSTEM           248250368    AVAILABLE
C:\ORACLE\ORADATA\ORACLE\RBS01.DBF     RBS              545259520    AVAILABLE
C:\ORACLE\ORADATA\ORACLE\USERS01.DBF   USERS            113246208    AVAILABLE
C:\ORACLE\ORADATA\ORACLE\TEMP01.DBF    TEMP              75497472    AVAILABLE
C:\ORACLE\ORADATA\ORACLE\TOOLS01.DBF   TOOLS             12582912    AVAILABLE
C:\ORACLE\ORADATA\ORACLE\INDX01.DBF    INDX              60817408    AVAILABLE
C:\ORACLE\ORADATA\ORACLE\DR01.DBF      DRSYS             92274688    AVAILABLE

-- FILE_NAME : DATAFILE의 물리적인 위치와 파일명
-- TABLESPACE_NAME : 테이블스페이스의 이름
-- BYTES : 테이블스페이스의 크기
-- STATUS : 테이블스페이스의 이용가능 여부