{"id":617,"date":"2016-03-22T16:19:50","date_gmt":"2016-03-23T01:19:50","guid":{"rendered":"\/blog\/?p=617"},"modified":"2025-01-07T15:33:12","modified_gmt":"2025-01-07T06:33:12","slug":"oracle-%ec%9c%a0%ec%9a%a9%ed%95%9c-%ed%8c%81%eb%93%a4","status":"publish","type":"post","link":"https:\/\/hasu0707.duckdns.org\/blog\/?p=617","title":{"rendered":"Oracle \uc720\uc6a9\ud55c \ud301\ub4e4"},"content":{"rendered":"\n

ORACLE_OWNER=oracle
ORACLE_SID=orcl

0. \uad00\ub9ac\uc790 \uc811\uc18d

sqlplus \"system\/manager as sysdba\"


1. oracle \uacc4\uc815\uc758 \ud658\uacbd\ubcc0\uc218 \uc14b\ud305

export TMP=\/tmp
export TMPDIR=${TMP}
export ORACLE_BASE=\/opt\/oracle
export ORACLE_HOME=${ORACLE_BASE}\/12.1.0
export ORACLE_HOME_LISTNER=${ORACLE_HOME}
export ORACLE_HOSTNAME=localhost.localdomain
export ORACLE_SID=orcl
export PATH=\/usr\/sbin:${ORACLE_HOME}\/bin:${PATH}:\/usr\/local\/bin
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:${ORACLE_HOME}\/lib
export LANG=\"ko_KR.UTF-8″


2. \uc2a4\ud06c\ub9bd\ud2b8 \ud5c8\uc6a9

$ sqlplus \"system\/manager as sysdba\"

ALTER SESSION SET \"_ORACLE_SCRIPT\"=TRUE;


3. Oracle \ud14c\uc774\ube14\uc2a4\ud398\uc774\uc2a4 \ub9cc\ub4e4\uae30

\/\/ \uc0dd\uc131 (\ucd5c\ucd08 2MB\uc5d0\uc11c \uc790\ub3d9\ud655\uc7a5)
CREATE TABLESPACE ts_user_name DATAFILE '\/oradata\/orcl\/user_name.dbf' SIZE 2M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

\/\/ \uc0ad\uc81c
DROP TABLESPACE ts_user_name INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;


4. \uacc4\uc815 \ub9cc\ub4e4\uae30

CREATE USER user_name IDENTIFIED BY \"\ube44\ubc00\ubc88\ud638\";         \/\/ \uacc4\uc815 \uc0dd\uc131
ALTER USER user_name DEFAULT TABLESPACE ts_user_name;   \/\/ \ud14c\uc774\ube14 \uc2a4\ud398\uc774\uc2a4 \ud560\ub2f9
ALTER USER user_name TEMPORARY TABLESPACE TEMP;         \/\/ temp \ud14c\uc774\ube14 \uc2a4\ud398\uc774\uc2a4 \ud560\ub2f9
ALTER USER user_name IDENTIFIED BY \"\ube44\ubc00\ubc88\ud638\";          \/\/ \ube44\ubc00\ubc88\ud638 \uc218\uc815


5. \uacc4\uc815 \uad8c\ud55c \uc8fc\uae30

\/\/ \ubaa8\ub4e0 \uad8c\ud55c (\ud55c\ubc29\uc5d0 \ub05d)
GRANT CONNECT, DBA, RESOURCE TO user_name;

\/\/ \uc0c1\uc138 \uad8c\ud55c
GRANT CREATE SESSION TO \uc720\uc800\uba85          \/\/ \ub370\uc774\ud130\ubca0\uc774\uc2a4\uc5d0 \uc811\uadfc\ud560 \uc218 \uc788\ub294 \uad8c\ud55c
GRANT CREATE DATABASE LINK TO \uc720\uc800\uba85
GRANT CREATE MATERIALIZED VIEW TO \uc720\uc800\uba85
GRANT CREATE PROCEDURE TO \uc720\uc800\uba85
GRANT CREATE PUBLIC SYNONYM TO \uc720\uc800\uba85
GRANT CREATE ROLE TO \uc720\uc800\uba85
GRANT CREATE SEQUENCE TO \uc720\uc800\uba85
GRANT CREATE SYNONYM TO \uc720\uc800\uba85
GRANT CREATE TABLE TO \uc720\uc800\uba85            \/\/ \ud14c\uc774\ube14\uc744 \uc0dd\uc131\ud560 \uc218 \uc788\ub294 \uad8c\ud55c
GRANT DROP ANY TABLE TO \uc720\uc800\uba85          \/\/ \ud14c\uc774\ube14\uc744 \uc81c\uac70\ud560 \uc218 \uc788\ub294 \uad8c\ud55c
GRANT CREATE TRIGGER TO \uc720\uc800\uba85
GRANT CREATE TYPE TO \uc720\uc800\uba85
GRANT CREATE VIEW TO \uc720\uc800\uba85


6. \uc0dd\uc131\ud55c \uacc4\uc815\uc73c\ub85c sqlplus \uc811\uc18d\ud558\uae30

sqlplus user_name\/mypasswd@orcl


7. \uacc4\uc815 \uc0ad\uc81c\ud558\uae30

ALTER SESSION SET \"_ORACLE_SCRIPT\"=TRUE;
DROP USER user_name CASCADE;


8. \uae30\ud0c0 \uc720\uc6a9\ud55c \ucffc\ub9ac\ub4e4

— \ud14c\uc774\ube14 \uc2a4\ud398\uc774\uc2a4 \ud06c\uae30 \ud655\uc7a5\ud574\uc8fc\ub294 \ucffc\ub9ac\ubb38
ALTER DATABASE DATAFILE 'D:\\oracle\\oradata\\XE\\ADMIN.DBF' RESIZE 900M;

–\ud14c\uc774\ube14 \uc2a4\ud398\uc774\uc2a4 \uc815\ubcf4 \ubcf4\ub294 \ucffc\ub9ac\ubb38
SELECT file_name, tablespace_name, bytes, status FROM  DBA_DATA_FILES;

–\ud14c\uc774\ube14 \uba85\uc2dc\uc801 \uc778\ub371\uc2a4 \uc0dd\uc131
–1\ubc88\uc9f8 \ubc29\ubc95
CREATE INDEX MSID_IDX1 ON TEST(MSID)
–2\ubc88\uc9f8 \ubc29\ubc95
create index test1_test on test1(test)
tablespace users
storage
(
initial 10k
next     10k
pctincrease 0)
pctfree 10

–\ud14c\uc774\ube14 \uc815\ubcf4 \ubcf4\ub294 \ucffc\ub9ac
SELECT * FROM USER_CONSTRAINTS– WHERE TABLE_NAME = UPPER('TEST1');

–ORA-00054: \uc790\uc6d0\uc774 \uc0ac\uc6a9\uc911\uc774\uace0, NOWAIT\uac00 \uc9c0\uc815\ub418\uc5b4 \uc788\uc2b5\ub2c8\ub2e4. \ud574\uacb0 \ubc29\ubc95
select a.sid, a.serial#
from v$session a, v$lock b, dba_objects c
where a.sid=b.sid and
b.id1=c.object_id and
b.type='TM' and
c.object_name='CAR_INFO';

alter system kill session '12, 27846′;

— CAR_INFO\uc5d0\ub294 \uc788\ub294 \uac12\uc744 DASH_BOARD\uc5d0 \ub123\uae30
INSERT INTO DASH_BOARD(CAR_LICEN_NUM)
SELECT CAR_LICEN_NUM FROM CAR_INFO
MINUS
SELECT CAR_LICEN_NUM FROM DASH_BOARD


SYSTEM \uacc4\uc815 \ud328\uc2a4\uc6cc\ub4dc \ubcc0\uacbd
\uc0ac\uc6a9\uc790\uacc4\uc815 : \/as sysdba

ALTER USER SYSTEM IDENTIFIED BY \"\uc554\ud638\";
\uc77c\ubc18 \uc2a4\ud2b8\ub9c1\uc740 \uad00\uacc4\uc5c6\uc9c0\ub9cc \ud2b9\uc218\ubb38\uc790\uac00 \uc788\uc744\uacbd\uc6b0 \ubc18\ub4dc\uc2dc \"\" \ub530\uc634\ud45c\ub85c \uac10\uc2f8\uc900\ub2e4.


9. \ud14c\uc774\ube14\uc2a4\ud398\uc774\uc2a4 \uc870\ud68c

SELECT Substr(df.tablespace_name,1,20) \"Tablespace Name\",
       Substr(df.file_name,1,40) \"File Name\",
       Round(df.bytes\/1024\/1024,2) \"Size (M)\",
       Round(e.used_bytes\/1024\/1024,2) \"Used (M)\",
       Round(f.free_bytes\/1024\/1024,2) \"Free (M)\",
       Rpad(' '|| Rpad ('X',Round(e.used_bytes*10\/df.bytes,0), 'X'),11,'-') \"% Used\"
FROM DBA_DATA_FILES DF, (SELECT file_id,
       Sum(Decode(bytes,NULL,0,bytes)) used_bytes
       FROM dba_extents
       GROUP by file_id) E,
      (SELECT Max(bytes) free_bytes, file_id
       FROM dba_free_space
       GROUP BY file_id) f
WHERE  e.file_id (+) = df.file_id
AND    df.file_id  = f.file_id (+)
ORDER BY df.tablespace_name, df.file_name;


10. \uacc4\uc815 \uc870\ud68c \ubc0f Lock \ud480\uae30

-\uacc4\uc815 \uc870\ud68c
SELECT USERNAME FROM DBA_USERS;

-Lock \uac78\ub9b0 \uacc4\uc815 \uc870\ud68c
SELECT USERNAME, ACCOUNT_STATUS, TO_CHAR(LOCK_DATE, 'YYYY.MM.DD HH24:MI') ROCK_DATE FROM DBA_USERS;

-Lock \ud480\uae30
ALTER USER \uc0ac\uc6a9\uc790 ACCOUNT UNLOCK;
<\/span><\/p>\n

- \ube44\ubc00\ubc88\ud638 \uc7a0\uae08 \ubc0f \uc720\ud6a8\uae30\uac04 \ud574\uc81c
  ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
  ALTER USER user_name ACCOUNT UNLOCK;
  ALTER USER user_name IDENTIFIED BY \"<PASSWORD>\";

11. \ub370\uc774\ud130\ubca0\uc774\uc2a4 \ubaa9\ub85d \uc870\ud68c

SELECT * FROM V$DATABASE;
or
SELECT ORA_DATABASE_NAME FROM DUAL;

12. \ud328\uc2a4\uc6cc\ub4dc \uc0ac\uc6a9\uae30\uac04 \ub9cc\ub8cc \ube44\ud65c\uc131\ud654 (\uae30\ubcf8\uc801\uc73c\ub85c 180\uc77c \ud6c4 \ub9cc\ub8cc\ub418\uace0 \uc811\uc18d \ubd88\uac00\ub428)

ALTER PROFILE default LIMIT FAILED_LOGIN_ATTEMPTS 999 PASSWORD_LIFE_TIME UNLIMITED PASSWORD_GRACE_TIME 10;
<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

ORACLE_OWNER=oracleORACLE_SID=orcl 0. \uad00\ub9ac\uc790 \uc811\uc18d sqlplus “system\/manager as sysdba” 1. oracle \uacc4\uc815\uc758 \ud658\uacbd\ubcc0\uc218 \uc14b\ud305 export TMP=\/tmpexport TMPDIR=${TMP}export ORACLE_BASE=\/opt\/oracleexport ORACLE_HOME=${ORACLE_BASE}\/12.1.0export ORACLE_HOME_LISTNER=${ORACLE_HOME}export ORACLE_HOSTNAME=localhost.localdomainexport ORACLE_SID=orclexport PATH=\/usr\/sbin:${ORACLE_HOME}\/bin:${PATH}:\/usr\/local\/binexport LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:${ORACLE_HOME}\/libexport LANG=”ko_KR.UTF-8″ 2. \uc2a4\ud06c\ub9bd\ud2b8 \ud5c8\uc6a9 $ sqlplus “system\/manager as sysdba” ALTER SESSION SET “_ORACLE_SCRIPT”=TRUE; 3. Oracle \ud14c\uc774\ube14\uc2a4\ud398\uc774\uc2a4 \ub9cc\ub4e4\uae30 \/\/ \uc0dd\uc131 (\ucd5c\ucd08 2MB\uc5d0\uc11c \uc790\ub3d9\ud655\uc7a5)CREATE TABLESPACE ts_user_name DATAFILE ‘\/oradata\/orcl\/user_name.dbf’ SIZE 2M AUTOEXTEND ON EXTENT MANAGEMENT […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_import_markdown_pro_load_document_selector":0,"_import_markdown_pro_submit_text_textarea":"","site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"set","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[10],"tags":[],"class_list":["post-617","post","type-post","status-publish","format-standard","hentry","category-computing_database"],"_links":{"self":[{"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=\/wp\/v2\/posts\/617","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=617"}],"version-history":[{"count":0,"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=\/wp\/v2\/posts\/617\/revisions"}],"wp:attachment":[{"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=617"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=617"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=617"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}