Database

출처 : http://technet.oracle.co.kr/forum/forum_open_view.jsp?forum_seqno=20060215111159&forum_cate=ORCL

sample schema 에서 scott, sh, hr 유저를 수동으로 만드는 방법은
아래와 같습니다. 참고하세요.


1. scott 유저 샘플 만들기


dbms#oracle:/data/oracle/app/oracle/product/9.2.0/dbs> sqlplus ''/as sysdba''

SQL*Plus: Release 9.2.0.6.0 - Production on Wed Feb 15 17:45:28 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL>
SQL> set pages 100
SQL> column tn format a20 heading ''TableSpace|Name''
SQL> column Tot format 999,999,999 heading ''Total|(Mb)''
SQL> column Free format 999,999,999 heading ''Free|(Mb)''
SQL> column Used format 999,999,999 heading ''Used|(Mb)''
SQL> column Pct format 999,999,999 heading ''Pct|(%)''
SQL>
SQL> ttitle ''DataSize|Tot/Used/Pct/Free''
SQL>
SQL> SELECT /*+ use_hash(f,t) */
2 t.tn,
3 t.sizes Tot,
4 nvl(trunc((t.sizes - f.sizes) /t.sizes * 100),100) Pct,
5 trunc(t.sizes - nvl(f.sizes,1) ) Used,
6 trunc(nvl(f.sizes,1)) Free
7 FROM ( SELECT tablespace_name tn,
8 sum(bytes)/1024/1024 Sizes
9 FROM dba_data_files
10 GROUP BY tablespace_name) t,
11 ( SELECT /*+ full(a) parallel(a 5) */
12 a.tablespace_name tn,
13 sum(a.bytes)/1024/1024 sizes
14 FROM dba_free_space a
15 GROUP BY a.tablespace_name) f
16 WHERE t.tn = f.tn(+)
17 ORDER BY t.tn
18 /

clear columns
ttitle off

Wed Feb 15
page 1
DataSize
Tot/Used/Pct/Free

TableSpace Total Pct Used Free
Name (Mb) (%) (Mb) (Mb)
-------------------- ------------ ------------ ------------ ------------
SYSTEM 300 33 101 198
TEST 10 0 0 9
TEST2 10 0 0 9
UNDOTBS 100 29 29 70
UNDOTBS2 10 10 1 8
USERS 1,000 2 21 978

6 rows selected.


SQL>
SQL> set pages 40
SQL> set line 132
SQL> col tbs_name format a15
SQL> col Used_mega format a15
SQL> col Used_PCT format 999.99
SQL> col Cache_PCT format 999.99
SQL>
SQL> SELECT d.tablespace_name tbs_name, d.status Status, d.CONTENTS Type,
2 d.extent_management Ext_manage,
3 NVL(a.BYTES / 1024 / 1024, 0) Total_mega,
4 NVL(t.BYTES, 1)/1024/1024 ||'' / ''|| NVL(a.BYTES / 1024 / 1024,
1) Used_mega,
5 NVL(t.BYTES / a.BYTES * 100, 1) Used_PCT,
6 NVL(t.curnt_byte/1024/1024, 1) Cache_mega,
7 (NVL(t.curnt_byte/1024/1024, 1)/NVL(a.BYTES / 1024 / 1024, 0)
*100) Cache_PCT
8 FROM SYS.dba_tablespaces d,
9 (SELECT tablespace_name, SUM (BYTES) BYTES
10 FROM dba_temp_files
11 GROUP BY tablespace_name) a,
12 (SELECT tablespace_name, SUM (BYTES_USED) curnt_byte, sum
(BYTES_CACHED) BYTES
13 FROM v$temp_extent_pool
14 GROUP BY tablespace_name) t
15 WHERE d.tablespace_name = a.tablespace_name(+)
16 AND d.tablespace_name = t.tablespace_name(+)
17 AND d.extent_management LIKE ''LOCAL''
18 AND d.CONTENTS LIKE ''TEMPORARY'';

clear columns
ttitle off
TBS_NAME STATUS TYPE EXT_MANAGE TOTAL_MEGA USED_MEGA
USED_PCT CACHE_MEGA CACHE_PCT
--------------- --------- --------- ---------- ---------- --------------- -
------- ---------- ---------
TEMP ONLINE TEMPORARY LOCAL 200 10 /
200 5.00 0 .00

SQL> create user scott identified by tiger
2 default tablespace users
3 temporary tablespace temp;

User created.

SQL> grant connect, resource to scott;

SQL> conn scott/tiger
Connected.

Grant succeeded.
SQL> @?/sqlplus/demo/demobld
Building demonstration tables. Please wait.
Demonstration table build is complete.
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 -
Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production


dbms#oracle:/data/oracle/app/oracle/product/9.2.0/dbs> sqlplus scott/tiger

SQL*Plus: Release 9.2.0.6.0 - Production on Wed Feb 15 17:52:36 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
DUMMY TABLE
EMP TABLE
SALGRADE TABLE

SQL>



2. hr 유저 만들기


SQL> conn /as sysdba
Connected.

SQL> @?/demo/schema/human_resources/hr_main

specify password for HR as parameter 1:
Enter value for 1: hr

specify default tablespeace for HR as parameter 2:
Enter value for 2: users

specify temporary tablespace for HR as parameter 3:
Enter value for 3: temp

specify password for SYS as parameter 4:
Enter value for 4: change_on_install

specify log path as parameter 5:
Enter value for 5: /u/home/oracle/LOG/

DROP USER hr CASCADE
*
ERROR at line 1:
ORA-01918: user ''HR'' does not exist


old 1: CREATE USER hr IDENTIFIED BY &pass
new 1: CREATE USER hr IDENTIFIED BY hr

User created.

old 1: ALTER USER hr DEFAULT TABLESPACE &tbs
new 1: ALTER USER hr DEFAULT TABLESPACE users
old 2: QUOTA UNLIMITED ON &tbs
new 2: QUOTA UNLIMITED ON users

User altered.

old 1: ALTER USER hr TEMPORARY TABLESPACE &ttbs
new 1: ALTER USER hr TEMPORARY TABLESPACE temp

User altered.


Grant succeeded.


Grant succeeded.

Connected.


3. sh 유저 만들기
- 오래 걸리고 300메가 공간이 있어야 합니다.
- ORACLE_HOME 디렉토리를 알아야 합니다.

SQL> show user
USER is "HR"
SQL> conn /as sysdba
Connected.

SQL> !echo $ORACLE_HOME
/data/oracle/app/oracle/product/9.2.0


SQL> @?/demo/schema/sales_history/sh_main

specify password for SH as parameter 1:
Enter value for 1: sh

specify default tablespeace for SH as parameter 2:
Enter value for 2: users

specify temporary tablespace for SH as parameter 3:
Enter value for 3: temp

specify password for SYS as parameter 4:
Enter value for 4: change_on_install

==> 아래에서 중요!!~~ 오라클 홈디렉토리에/demo/schema/sales_history/
를 붙여야 합니다. 마지막에 / 을 붙이지 않으면 찾지 못하니
마지막에 / 를 꼭 붙여주세요.
specify directory path for the data files as parameter 5:
Enter value for
5: /data/oracle/app/oracle/product/9.2.0/demo/schema/sales_history/

=> 아래는 sh유저를 생성한 로그를 남기는 곳인데.. / 를 마지막에
붙여줘야 그 해당 디렉토리에 만들어 집니다.

writeable directory path for the log files as parameter 6:
Enter value for 6: /u/home/oracle/LOG/

.... 생성 시작.. 시간이 많이 걸립니다.

새로운 티스토리의 오픈과 함께 급조한 블로그입니다.