오라클 계정 권한 확인 방법

Oracle 11g 기준으로 작성된 글입니다.

oracle 11g 설치를 다 하신 후에 cmd와 sql developer에서 oracle에 접근해 계정 설정이 가능합니다.

1. cmd에서 계정 설정하기

1) cmd에서 접속하기

  sqlplus 로 접속하시면 user-name과 password가 나오는데 

  user-name은 system, password는 oracle 설치 시 입력하신 비밀번호로 입력해주시면 됩니다.

  (password 입력할 때, cmd창에서 표시가 안되는게 정상입니다.)

sqlplus

2) 관리자권한으로 접속

  정상적으로 접속을 하시면, 관리자 권한으로 접속해야합니다.

conn/as sysdba

3) 계정 생성

  [id] [pw]는 자신이 생각하는 id와 pw로 입력하시면 됩니다.

  저는 sampleid와 samplepw로 설정해봤습니다.

create user [id] identified by [pw];

4) 권한 부여

  계정을 생성 한 후에 권한을 줘야합니다.

  connect(접속 권한), resource(객체 및 데이터 조작 권한), dba를 설정했습니다.

grant [권한] to [id];

grant connect, resource, dba to [id];

5) commit

  지금까지의 변경 사항들을 적용합니다.

commit;

6) 계정 생성 확인

  아래 사진을 확인하면 SAMPLEID가 생성되어있는 걸 확인 할 수 있습니다.

select * from all_users;

+ 계정 권한 취소

  여기서는 아까 준 권한들을 취소해보겠습니다.

revoke [권한] from [id];

revoke connect, resource, dba from sampleid;

+ 계정 삭제

drop user [id] cascade;

2. sql developer를 이용하기

1) sql developer 에서 접속

  첫 실행 시, 아래 사진에 표시된 + 버튼을 클릭합니다.

2) system으로 접속

  사용자 이름에 system을 입력하고, 비밀번호에 oracle을 설치할 때 설정한 pw를 입력합니다.

  다 입력하신 후에 테스트 버튼을 눌러 상태가 성공이라고 뜨면 정상입니다.

  저장 및 접속을 눌러 진행하시면 됩니다.

2) sql 워크시트에서 쿼리들을 입력합니다.

select * from all_users; 	-- 모든 계정 정보 조회

create user [id] identified by [pw];	-- 계정 생성

grant [권한] to [id]; 		-- 권한 부여

commit;			-- 변경사항 저장

이상 계정 및 권한 설정하는 방법을 알아보았습니다.

이제 생성하신 계정으로 접속하셔서 db작업 하시면 됩니다~

[ORACLE] 사용자및 권한 확인 하기
Study/Oracle 2012/11/16 09:56 Posted by JHLAB
1. 사용자에게 부여된 시스템 권한 확인
SELECT * FROM DBA_SYS_PRIVS
WHERE GRANTEE = '사용자명' ;

-- 2. 사용자에게 부여된 롤 확인(시스템 권한이 롤에 포함됨)
SELECT * FROM DBA_ROLE_PRIVS
WHERE GRANTEE = '사용자명' ;

-- 3. 사용자에게 부여된 롤에 부여된 시스템 권한 확인
SELECT * FROM DBA_SYS_PRIVS
WHERE GRANTEE = '롤명' ;

-- 4. 타 사용자에게 부여한 객체(테이블등) 권한 확인
SELECT * FROM DBA_TAB_PRIVS
WHERE OWNER = '테이블소유자명' ;
또는,
SELECT * FROM DBA_TAB_PRIVS
WHERE GRANTEE = '권한부여자명' ;

DDL 권한 : 기본적으로 CREATE TABLE 권한(1, 2번 SQL로 확인 가능)이 있으면, 자기가 생성한 테이블에 대해서는
ALTER, DROP, TRUNCATE 가능
 타 사용자에 대한 테이블 생성 권한은 DBA_SYS_PRIVS에서 확인 가능(CREATE ANY TABLE)
DML 권한 : 본인 소유의 테이블은 모든 작업 수행 가능
타인 소유의 테이블에 대한 작업 수행은 4번 SQL로 확인 가능
DCL 권한 : 본인 소유의 테이블에 대한 타 사용자에게의 GRANT는 항상 가능
타인 소유의 테이블에 대한 타 사용자에게의 GRANT 권한은,
해당 테이블에 대한 권한을 DBA_TAB_PRIVS.GRANTABLE 컬럼에 YES 로 받은 경우 가능
GRANT ANY OBJECT PRIVILEGE 시스템 권한(1, 2번 SQL로 확인 가능)을 가지고 있는 경우
타 사용자의 모든 객체에 대한 권한 부여 가능(DBA도 해당 권한이 있음)

-- 오라클 내부 사용자 목록 : 버전에 따라 상이
SELECT USERNAME FROM DBA_USERS
ORDER BY 1;

ANONYMOUS
APEX_030200
APEX_PUBLIC_USER
APPQOSSYS
CTXSYS
DBSNMP
DIP
EXFSYS
FLOWS_FILES
MDDATA
MDSYS
MGMT_VIEW
NMSVIEW
OLAPSYS
ORACLE_OCM
ORDDATA
ORDPLUGINS
ORDSYS
OUTLN
OWBSYS
OWBSYS_AUDIT
SI_INFORMTN_SCHEMA
SPATIAL_CSW_ADMIN_USR
SPATIAL_WFS_ADMIN_USR
SYS
SYSMAN
SYSTEM
WMSYS
XDB
XS$NULL

SCOTT, HR, OE, SH, PM 등은 테스트 계정으로 오라클 설치 시 만들어진 계정임

http://javafactory.tistory.com/entry/%EC%98%A4%EB%9D%BC%ED%81%B4-%ED%85%8C%EC%9D%B4%EB%B8%94%EC%8A%A4%ED%8E%98%EC%9D%B4%EC%8A%A4-%EC%9A%A9%EB%9F%89-%EC%A1%B0%ED%9A%8C-oracle-tablespace-%EC%9A%A9%EB%9F%89-%EC%A1%B0%ED%9A%8C

오라클 테이블스페이스 용량 조회, oracle tablespace 용량 조회
SQL/ORACLE 2014/02/03 11:13
출처 - http://kalipso.tistory.com/80

출처 - http://mentor75.tistory.com/entry/ORACLE-TABLE-SPACE-%EC%82%AC%EC%9A%A9%EB%9F%89-%ED%99%95%EC%9D%B8%EC%BF%BC%EB%A6%AC

출처 - https://kldp.org/node/34801

출처 - http://stackoverflow.com/questions/264914/how-do-i-calculate-tables-size-in-oracle

1. 테이블스페이스 정보 조회

SELECT * FROM DBA_TABLESPACES;

2. 테이블스페이스별 용량 확인 쿼리문(MB 단위)

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(a.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) sum1,max(bytes) MAXB,count(bytes) cnt
          from     dba_free_space
          group by tablespace_name
          union
          select   tablespace_name,sum(bytes) total1,0,0,0
          from     dba_data_files
          group by tablespace_name) a
group by a.tablespace_name
order by tablespace;

조회결과를 다음과 같이 살펴볼 수 있다.

TABLESPACE명 총용량(TotalMB) 사용용량(UsedMB) 여유용량(FreeMB) 사용율(Used%)
SYSAUX 1024 514.4 509.6 50.23
SYSTEM 1024 390 634 38.09
TS_SEND_DATA 70960 59890.6 11069.4 84.4
TS_SEND_TEMP 1024 0.1 1023.9 0.01
UNDOTBS1 4518 55.7 4462.3 1.23
USERS 100 0.1 99.9 0.1

3. 테이블스페이스별 현황 확인 쿼리문(MB 단위)

SELECT TABLESPACE_NAME, FILE_NAME, BYTES/1024 AS MBytes, RESULT/1024 AS USE_MBytes FROM
  (
  SELECT E.TABLESPACE_NAME,E.FILE_NAME,E.BYTES, (E.BYTES-SUM(F.BYTES)) RESULT
  FROM DBA_DATA_FILES E, DBA_FREE_SPACE F
  WHERE E.FILE_ID = F.FILE_ID
  GROUP BY E.TABLESPACE_NAME, E.FILE_NAME, E.BYTES
  ) A;

TABLESPACE_NAME FILE_NAME MBYTES USE_MBYTES
SYSTEM C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSTEM01.DBF 1464320 1458816
SYSAUX C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSAUX01.DBF 471040 450048
USERS C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS01.DBF 5120 3328
TEST C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\TEST 20733952 18843264
EXAMPLE C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\EXAMPLE01.DBF 102400 79552
UNDOTBS1 C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\UNDOTBS01.DBF 6917120 17856
ORCL C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\ORCL 1048576 1600

4.  테이블스페이스별, 파일별 현황 확인 쿼리문(바이트 단위)

SELECT    A.TABLESPACE_NAME "테이블스페이스명",
          A.FILE_NAME "파일경로",
           (A.BYTES - B.FREE)    "사용공간",
            B.FREE                 "여유 공간",
            A.BYTES                "총크기",
            TO_CHAR( (B.FREE / A.BYTES * 100) , '999.99')||'%' "여유공간"
      FROM
       (
         SELECT FILE_ID,
                TABLESPACE_NAME,
                FILE_NAME,
                SUBSTR(FILE_NAME,1,200) FILE_NM,
                SUM(BYTES) BYTES
           FROM DBA_DATA_FILES
         GROUP BY FILE_ID,TABLESPACE_NAME,FILE_NAME,SUBSTR(FILE_NAME,1,200)
       ) A,
       (
         SELECT TABLESPACE_NAME,
                FILE_ID,
                SUM(NVL(BYTES,0)) FREE
           FROM DBA_FREE_SPACE
        GROUP BY TABLESPACE_NAME,FILE_ID
       ) B
      WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME
         AND A.FILE_ID = B.FILE_ID;

테이블스페이스명 파일경로 사용공간 여유공간 총크기 여유공간
TS_TEST_DATA /oradata/TEST/ts_test_data02.dbf 16148332544 5326503936 21474836480   24.80%
TS_TEST_DATA /oradata/TEST/ts_test_data03.dbf 16073621504 5401214976 21474836480   25.15%
TS_TEST_DATA /oradata/TEST/ts_test_data.dbf 30577852416 879427584 31457280000    2.80%
SYSAUX /oradata/TEST/sysaux01.dbf 539361280 534380544 1073741824   49.77%
USERS /oradata/TEST/users01.dbf 65536 104792064 104857600   99.94%
SYSTEM /oradata/TEST/system01.dbf 408944640 664797184 1073741824   61.91%
UNDOTBS1 /oradata/TEST/undotbs01.dbf 53149696 4684316672 4737466368   98.88%
TS_TEST_TEMP /oradata/TEST/ts_test_temp.dbf 65536 1073676288 1073741824   99.99%

5. 테이블 용량 조회

SELECT
   owner, table_name, TRUNC(sum(bytes)/1024/1024/1024) GB
FROM
(SELECT segment_name table_name, owner, bytes
FROM dba_segments
WHERE segment_type in  ('TABLE','TABLE PARTITION')
UNION ALL
SELECT i.table_name, i.owner, s.bytes
FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name
AND   s.owner = i.owner
AND   s.segment_type in ('INDEX','INDEX PARTITION')
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND   s.owner = l.owner
AND   s.segment_type IN ('LOBSEGMENT','LOB PARTITION')
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND   s.owner = l.owner
AND   s.segment_type = 'LOBINDEX')
---WHERE owner in UPPER('&owner')
GROUP BY table_name, owner
HAVING SUM(bytes)/1024/1024 > 10  /* Ignore really small tables */
ORDER BY SUM(bytes) desc