SQL 만들기

 

 

 

 

 

 

 

 

 

권한주기 DCL(제어) : grant

 

 

이거 최신 이걸로하면됨

Microsoft Windows [Version 10.0.22000.2057]
(c) Microsoft Corporation. All rights reserved.

C:\Users\LG>sqlplus sys/java@localhost:1521 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 14 00:18:38 2023

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> create tablespace ts_202303M
        datafile 'C:\oraclexe\app\oracle\oradata\XE\ts_202303M.dbf'
        size 200M;

Tablespace created.

SQL> create user jspexam identified by java
        default tablespace ts_202303M
        quota unlimited on ts_202303M;

User created.

SQL> grant connect,resource to jspexam;

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

C:\Users\LG>sqlplus jspexam/java@localhost:1521 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 14 00:19:25 2023

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> grant connect,resource to jspexam;

Grant succeeded.

SQL> grant create procedure to jspexam;

Grant succeeded.

SQL> grant create view to jspexam;

Grant succeeded.

SQL>  grant create sequence to jspexam;

Grant succeeded.

SQL>

 

 

예전거 안되서 수정함... 혹시몰라서 삭제안함

Microsoft Windows [Version 10.0.19045.3086]
(c) Microsoft Corporation. All rights reserved.

C:\Users\402Sem-PC>sqlplus sys/java@localhost:1521 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 월 6월 26 11:50:10 2023

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


다음에 접속됨:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> create tablespace ts_202303M
    datafile 'C:\oraclexe\app\oracle\oradata\XE\ts_202303M.dbf'
    size 200M;

테이블스페이스가 생성되었습니다.

SQL> create user jspexam identified by java
    default tablespace ts_202303M
    quota unlimited on ts_202303M;

사용자가 생성되었습니다.

SQL> grant connect,resource to jspexam;

권한이 부여되었습니다.

SQL> grant sysdba to jspexam;

권한이 부여되었습니다.

SQL> grant create procedure to jspexam;

권한이 부여되었습니다.

SQL> grant create view to jspexam;

권한이 부여되었습니다.

SQL> grant create sequence to jspexam;

권한이 부여되었습니다.

SQL>

 

이건 혼자연습하다 삭제할일 생겨서 써놈

--테이블 스페이스 삭제

DROP TABLESPACE tablespace-name
INCLUDING CONTENTS AND DATAFILES
CASCADE CONSTRAINTS;
------------------------ex)-----------------------
DROP TABLESPACE ts_202303M
INCLUDING CONTENTS AND DATAFILES
CASCADE CONSTRAINTS;


--유저 삭제
DROP USER jspexam CASCADE;

 

 

서비스에서 두개가 실행중이어야 함(자동으로 실행되고 있음)