{"id":412,"date":"2013-10-07T10:00:32","date_gmt":"2013-10-07T19:00:32","guid":{"rendered":"\/blog\/?p=412"},"modified":"2023-09-21T09:38:51","modified_gmt":"2023-09-21T00:38:51","slug":"oracle-sql-tip","status":"publish","type":"post","link":"https:\/\/hasu0707.duckdns.org\/blog\/?p=412","title":{"rendered":"Oracle SQL Tip"},"content":{"rendered":"\n

\ucd9c\ucc98 : http:\/\/blog.naver.com\/sasimy18?Redirect=Log&logNo=120026510934<\/a><\/span>

\u25c6 DB Open<\/span>
alter database open;<\/span>
alter database open resetlogs;<\/span>

\u25c6 DB \ub9c8\uc6b4\ud2b8<\/span>
 alter database mount;<\/span>

\u25c6 DB\uc5d0 \uc788\ub294 \ubaa8\ub4e0 Table \uc774\ub984\ubcf4\uae30<\/span>
select table_name from user_tables<\/span>

\u25c6 Table\uc758 Primary Key \ucc3e\uae30<\/span>
select * from user_ind_columns where table_name = ''CodeTable''<\/span>

\u25c6 \uc778\uc218\uc804\ub2ec<\/span>
select * from user_ind_columns where table_name = ''&1''<\/span>
===>; save key<\/span>
SQL> start key CodeTable<\/span>

\u25c6 \uc11c\ub85c \uc5f0\uacb0\ub418\ub294 Key\ub97c \ucc3e\ub294 \ubc29\ubc95<\/span>
select constraint_name, constraint_type, r_constraint_name<\/span>
from user_constraints where table_name = ''TABLE_NAME<\/span>

\u25c6 TABLE \uad6c\uc870 \ubcf4\uae30<\/span>
DESC TABLE_NAME<\/span>

\u25c6 Constraint \ud655\uc778<\/span>
select table_name, constraint_name, constraint_type<\/span>
from user_constraints<\/span>
where table_name in (''DEPARTMENT'',''EMPLOYEE'');<\/span>

\u25c6 \ud14c\uc774\ube14 COPY \ud558\uae30<\/span>
create table emp_41<\/span>
as<\/span>
select id, last_name, userid, start_date from s_emp<\/span>
where dept_id = 41;<\/span>
===> where\uc808\uc5d0 \uc5c9\ub6b1\ud55c \uc870\uac74\uc744 \uc8fc\uba74 emp_41\uc774\ub780 \uc774\ub984\uc73c\ub85c \ud14c\uc774\ube14\uc774 \ub9cc\ub4e4\uc5b4\uc9c4\ub2e4.<\/span>

\u25c6 \uc120\ud0dd\ud55c Row\ub9cc\ud07c\ub9cc \ubcf4\uc5ec\uc8fc\uae30<\/span>
select * from tmp_table<\/span>
where rownum <= 100<\/span>
---> \uc774\ub807\uac8c \ud558\uba74 \ub370\uc774\ud130\uac00 10000\uac74\uc774 \uc788\ub354\ub77c\ub3c4, 1~100\uac74\ub9cc \ubcf4\uc5ec\uc8fc\uac8c \ub41c\ub2e4.<\/span>

\u25c6 \uc624\ub77c\ud074\uc758 \ubaa8\ub4e0 \uc720\uc800 \ubcf4\uae30<\/span>
select * from all_users<\/span>

\u25c6 Parameter\uc815\ubcf4 \ubcf4\uae30 (\ud55c\/\uc601\ucf54\ub4dc\uac12, \ubc84\uc83c\uc815\ubcf4\ub4f1\uc744 \ubcfc\uc218\uc788\ub2e4.)<\/span>
select * from nls_database_parameters<\/span>
---> \uc774\ub54c, NLS_CHARACTERSET\uc758 Value\uac00 KO16KSC5601 \uc774\uba74 \ud55c\uae00...<\/span>
US7ASCII \uc774\uba74 \uc601\ubb38\uc774\ub2e4. ---> regedit\uc5d0\uc11c \ud3b8\uc9d1\ud558\uba74 \uac04\ub2e8\ud788 \ud574\uacb0.<\/span>

\u25c6 Space \uc788\ub294 \uac12\uc744 Null\ub85c \ube44\uad50<\/span>
RTRIM(a.ymd_myun) IS NULL<\/span>

\u25c6 Desc\uba85\ub839\uc73c\ub85c Table\uad6c\uc870 \ubcf4\ub294 \ud6a8\uacfc\uc640 \uac19\uc740 \ubc29\ubc95<\/span>
SELECT column_name, data_type, data_length, nullable FROM cols<\/span>
WHERE table_name = ''YTB_CARCOM''<\/span>
---> \ubc18\ub4dc\uc2dc \ud14c\uc774\ube14\uba85\uc740 \ub300\ubb38\uc790 \uc774\uc5b4\uc57c \ud55c\ub2e4.<\/span>

\u25c6 Function Script \ubcf4\ub294 \ubc29\ubc95.<\/span>
select text from user_source where name = ''FUNCTION_NAME''<\/span>

\u25c6 \uc694\uc77c \ucc3e\ub294 \ubc29\ubc95.<\/span>
select TO_CHAR(sysdate,''D'') from dual<\/span>


\uc624\ub77c\ud074: \uad00\ub9ac \uba85\ub839 \ubaa8\uc74c....<\/span>
\uc880 \uc608\uc804\uc5d0 \uc815\ub9ac\ud55c \uac83\uc774\ub77c\uc11c\ub9ac...\uca5d.<\/span>
7.x\ub54c... \u314b\u314b<\/span>
\uc9c0\uae08\ub3c4 \uc0ac\uc6a9\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4. \ud234\ub9cc \uc0ac\uc6a9\ud558\uc2dc\ub358 \ubd84\ub4e4\uc5d0\uac8c\ub3c4 \ub3c4\uc6c0\uc774 \ub418\uc5c8\uc73c\uba74 \ud569\ub2c8\ub2e4.<\/span>

          \/********************************************\/<\/span>
          \/******* table space (cityinsa )create ******\/<\/span>
          \/********************************************\/<\/span>
          ORACLE#] sqldba lmode=y<\/span>
          SQLDBA> connect internal<\/span>
          SQLDBA> drop tablespace cityinsa;<\/span>
          SQLDBA> create tablespace cityinsa datafile<\/span>
                  'usr2\/oracle\/dbs\/cityinsa.dbf' size 150M<\/span>
                  default storage(<\/span>
                       initial 1M<\/span>
                       next 1M<\/span>
                       pctincrease 0<\/span>
                  );<\/span>

          \/********************************************\/<\/span>
          \/**** temp table( cityts ) space create *****\/<\/span>
          \/********************************************\/<\/span>
          SQLDBA> drop tablespace cityts;<\/span>
          SQLDBA> create tablespace cityts datafile<\/span>
                  'usr2\/oracle\/dbs\/cityts.dbf' size 100M<\/span>
                  default storage(<\/span>
                       initial 1M<\/span>
                       next 1M<\/span>
                       pctincrease 0<\/span>
                  );<\/span>

          \/********************************************\/<\/span>
          \/****** user (cityinsa\/insa) create ******\/<\/span>
          \/********************************************\/<\/span>
          SQLDBA> drop user cityinsa;<\/span>
          SQLDBA> create user cityinsa identified by insa default tablespace<\/span>
                  cityinsa temporary tablespace cityts;<\/span>
          \/***** grant \uc2dc\ud0a4\ub294 \ubc29\ubc95 ****************\/<\/span>
          SQLDBA> grant connect, resource to cityinsa;<\/span>

          \/********************************************\/<\/span>
          \/************* ctl file ********************\/<\/span>
          \/********************************************\/<\/span>
          load data<\/span>
          infile 'fgibon.dat'<\/span>
          into table fgibon<\/span>
          (gb_jumin_no position( 01:13) char,<\/span>
          gb_kikwan position( 14:23) char,<\/span>
          gb_name position(24:31) char,<\/span>
          gb_num position(32:35) integer external)<\/span>

          \/********************************************\/<\/span>
          \/******* oracle error message display *******\/<\/span>
          \/********************************************\/<\/span>
          [\uc778\uc0ac#] oerr ora sqlca.sqlcode<\/span>

          \/********************************************\/<\/span>
          \/****** oracle data 'load' command *******\/<\/span>
          \/********************************************\/<\/span>
          sqlload userid=cityinsa\/insa control=$1.ctl log=$1.log<\/span>

          \/********************************************\/<\/span>
          \/******** db export ************************\/<\/span>
          \/********************************************\/<\/span>
          [\uc778\uc0ac#] exp cityinsa\/insa<\/span>
          [\uc778\uc0ac#] exp system\/manager owner=cityinsa file=cityinsa.dmp<\/span>
                  log=cityinsa.log<\/span>
                  \uc5ec\uae30\uc11c log\uc640 full\ub294 \uc5c6\uc5b4\ub3c4 \ub428<\/span>
                  index=y\ub97c \ub123\uc5b4\uc8fc\uba74 \uc778\ub371\uc2a4 \ubc1b\uc544\uc90c(default\ub85c 'y').<\/span>

          \/********************************************\/<\/span>
          \/********** db import **********************\/<\/span>
          \/********************************************\/<\/span>
          [\uc778\uc0ac#] imp cityinsa\/insa<\/span>
          [\uc778\uc0ac#] imp system\/manager file=cityinsa.dmp full=y<\/span>

          \/********************************************\/<\/span>
          \/* table space\uc5d0 \ub0b4\uc6a9\uc774 \uc788\uc744\uacbd\uc6b0 \uac15\uc81c\ub85c drop*\/<\/span>
          \/* table space name: cityinsa *\/<\/span>
          \/********************************************\/<\/span>
          SQLDBA> drop tablespace cityinsa including contents;<\/span>

          \/********************************************\/<\/span>
          \/***** user drop \uc2dc\ud0a4\ub294 \ubc29\ubc95 *****\/<\/span>
          \/***** db name: cityinsa *****\/<\/span>
          \/********************************************\/<\/span>
          SQLDBA> drop user cityinsa ;<\/span>

          \/******************************************************\/<\/span>
          \/**** db\uc758 dbf file\uc744 \uadf8\ub0e5 rm \uba85\ub839\uc73c\ub85c \uc9c0\uc6e0\uc744 \uacbd\uc6b0 ****\/<\/span>
          \/******************************************************\/<\/span>
          SQLDBA> startup mount;<\/span>
          SQLDBA> alter database datafile '\/users\/INSA\/dbs\/cityinsa.dbf'<\/span>
                  offline drop;<\/span>
          SQLDBA> alter database open;<\/span>
          SQLDBA> drop tablespace cityinsa including contents;<\/span>

          \/********************************************\/<\/span>
          \/***** database tablespace \uc815\ubcf4 \ubcf4\uae30 ********\/<\/span>
          \/********************************************\/<\/span>
          Sqlplus> select * from v$datafile;<\/span>
                   select * from sys.dba_data_files;<\/span>

          \/********************************************\/<\/span>
          \/***** .dbf file name change \ubc29\ubc95 ********\/<\/span>
          \/********************************************\/<\/span>
          1. \ub370\uc774\ud130\ubca0\uc774\uc2a4 backup\uc744 \uc2dc\ud589\ud558\uace0 \ub370\uc774\ud130\ubca0\uc774\uc2a4\uc758 \ubaa8\ub4e0 \uc791\uc5c5\uc744<\/span>
             \uc911\ub2e8\ud558\uace0 \uc791\uc5c5\uc744 \uc2e4\uc2dc\ud55c\ub2e4.<\/span>

          2. SQLDBA> alter tablespace cityinsa offline;<\/span>

          3. os copy \uba85\ub839\uc744 \uc0ac\uc6a9\ud558\uc5ec \uc0c8\ub85c\uc6b4 directory\uc5d0 file\uc744 copy\ud55c\ub2e4.<\/span>
             [\uc778\uc0ac#] cp \/users\/INSA\/dbs\/cityinsa.dbf \/usr2\/INSA\/dbs\/cityinsa.dbf<\/span>

          4. SQLDBA> alter tablespace cityinsa rename datafile<\/span>
              '\/users\/INSA\/dbs\/cityinsa.dbf' to '\/usr\/INSA\/dbs\/cityinsa.dbf';<\/span>

          5. SQLDBA> alter tablespace cityinsa online;<\/span>

          6. os rm \uba85\ub839\uc744 \uc0ac\uc6a9\ud558\uc5ec \uc608\uc804 file\uc744 \uc0ad\uc81c\ud55c\ub2e4.<\/span>

          < \uc704\uc758 \uc791\uc5c5\uc744 \ud558\uae30\uc704\ud574\uc11c\ub294 old file\uacfc new file\uc774 \uc874\uc7ac\ud574\uc57c\ud55c\ub2e4. \ub610\ud55c \ub370\uc774\ud130 \ud30c\uc77c\uc758 \uc774<\/span>
\ub984\uc774 \ubcc0\uacbd \ub418\uc5c8\uc73c\ubbc0\ub85c SQLDBA> alter database backup controlfile to 'filename' ><\/span>

          \/********************************************\/<\/span>
          \/***** rollback segment space \ubcf4\ub294\ubc95 ***\/<\/span>
          \/********************************************\/<\/span>
            SQLDBA> select * from dba_free_space<\/span>
                    where tablespace_name='RBS';<\/span>

          \/********************************************\/<\/span>
          \/***** system segment space \ubcf4\ub294\ubc95 ***\/<\/span>
          \/********************************************\/<\/span>
            SQLDBA> select * from dba_free_space<\/span>
                    where tablespace_name='SYSTEM';<\/span>
            SQLDBA> select segment_name, initial_extent, next_extent,<\/span>
                    max_extents<\/span>
                    from dba_rollback_segs;<\/span>

          \/********************************************\/<\/span>
          \/***** rollback segment (RBS) \ubd80\uc871\ud560 \uacbd\uc6b0 ***\/<\/span>
          \/********************************************\/<\/span>
           SQLDBA>alter tablespace rbs add datafile<\/span>
                   '\/ORACLE\/oracle\/dbs\/rbs_1.dbf' size 10M;<\/span>

           \ucc38\uace0: rbs\ub294 rollback tablespace \uc774\ub984\uc774\uace0<\/span>
                 rbs_1.dbf\ub294 \uc0c8\ub85c \ub298\ub824\uc8fc\ub294 tablespace\uc774\ub984.<\/span>

          \/********************************************\/<\/span>
          \/***** database tablespace\uac00 recover\uc77c\ub54c ***\/<\/span>
          \/********************************************\/<\/span>
           SQLDBA>alter database recover automatic tablespace cityinsa;<\/span>
           SQLDBA>alter tablespace cityinsa online;<\/span>

          \/********************************************\/<\/span>
          \/***** database link \ub9cc\ub4e4\uae30 ***\/<\/span>
          \/********************************************\/<\/span>
           ECRM> create database link ebiz_db_ecrm.ebiz_dev<\/span>
                 connect to ecrm identified by akwldrk0<\/span>
                 using 'ebiz_db'<\/span>

           \ucc38\uace0: test ecrm \uc5d0\uc11c \uba54\uc778 ebiz_db\uc758 ecrm \uc720\uc800\uc758 db\ub97c \uc0ac\uc6a9\ud558\uace0\uc790 \ud568.<\/span>



[SELECT INTO \uc640 \uac19\uc740 \uad6c\ubb38]<\/span>
create table bar [UNRECOVERABLE] as select * from \uc6d0\ubcf8\ud14c\uc774\ube14<\/span>
---------------------------------------------------------------<\/span>
DESC \ud14c\uc774\ube14\uba85<\/span>
---------------------------------------------------------------<\/span>
[\ub3d9\uc77c\ud55c \ud14c\uc774\ube14\uc5d0 \uc788\ub294 \ub0b4\uc6a9\uc744 \ub3d9\uc77c\ud55c \uceec\ub7fc\uc5d0 \uc785\ub825\ud560 \uacbd\uc6b0]<\/span>

UPDATE TABLE_NAME SET REGDATE = APPDATE<\/span>
---------------------------------------------------------------<\/span>
[\ucffc\ub9ac\ubb38]<\/span>
alert table supply modify (name varchar(10) null)<\/span>
alert table supply add (name varchar(10) null)<\/span>
---------------------------------------------------------------<\/span>

[INSERT SELECT]<\/span>
insert into wavy_clubMember (memberid, clubid, membername, email, company, grade)<\/span>
select R.memberid, R.clubID, R.memberName, M.email, M.company, 'M'<\/span>
from wavy_clubRecommend R, wavy_member M<\/span>
where R.memberID = M.mid and R.CLUBID = 16<\/span>
---------------------------------------------------------------<\/span>

[\uc2dc\ud000\uc2a4 \uc0ac\uc6a9]<\/span>
create sequence aa_seq<\/span>
  start with 1<\/span>
  increment by 1<\/span>
  minvalue 1<\/span>
  nocycle<\/span>
  order;<\/span>

insert into tableName (seq) values (aa_seq.nextval)<\/span>
---------------------------------------------------------------<\/span>

[\ub0a0\uc9dc \ucc98\ub9ac]<\/span>
select regDate from member where substr(to_char(regDate),0,10) = substr(to_char( add_months(sysdate, -24) ), 0, 10)<\/span>

select to_char(sysdate, 'yyyymmdd') from dual<\/span>

add_months(str, -24) \ub294 str\uc5d0\uc11c 24\uac1c\uc6d4\uc744 \ube80 \uac83\uacfc \uac19\ub2e4.<\/span>
---------------------------------------------------------------<\/span>

[\uc77c\ubc18 \ud568\uc218]<\/span>
substr(str, 0, 10) \uc740 left(str, 10)\uacfc \ub2e4\ub978 \uc758\ubbf8<\/span>

select nvl(max(item_no), 0) from club_item where club_id='aaaa';<\/span><\/p>


\u25a0 \uc5f0\uc0b0\uc790  \uc124\uba85<\/span>
AND  \ub450 \uc870\uac74\uc774 \ubaa8\ub450 \ucda9\uc871\ub418\uc5b4\uc57c \ud569\ub2c8\ub2e4.<\/span>
OR  \ucd5c\uc18c\ud55c \ud55c \uac00\uc9c0 \uc870\uac74\uc774 \ucda9\uc871\ub418\uc5b4\uc57c \ud569\ub2c8\ub2e4.<\/span>
NOT  \ub2e4\uc74c\uc5d0 \uc624\ub294 \uc870\uac74 \uc81c\uc678<\/span>
LIKE  \ud328\ud134\uacfc \uc77c\uce58<\/span>
IN  \uac12 \ubaa9\ub85d\uacfc \uc77c\uce58<\/span>
BETWEEN  \uac12 \ubc94\uc704\uc640 \uc77c\uce58<\/span>
= \uac19\uc74c<\/span>
<> \uac19\uc9c0 \uc54a\uc74c<\/span>
< \ubcf4\ub2e4 \uc791\uc74c<\/span>
> \ubcf4\ub2e4 \ud07c<\/span>
<= \ubcf4\ub2e4 \uc791\uac70\ub098 \uac19\uc74c<\/span>
>= \ubcf4\ub2e4 \ud06c\uac70\ub098 \uac19\uc74c<\/span>
+ \ub354\ud558\uae30<\/span>
- \ube7c\uae30<\/span>
\/ \ub098\ub204\uae30<\/span>
* \uacf1\ud558\uae30<\/span>
<\/p>\n","protected":false},"excerpt":{"rendered":"

\ucd9c\ucc98 : http:\/\/blog.naver.com\/sasimy18?Redirect=Log&logNo=120026510934 \u25c6 DB Openalter database open;alter database open resetlogs; \u25c6 DB \ub9c8\uc6b4\ud2b8 alter database mount; \u25c6 DB\uc5d0 \uc788\ub294 \ubaa8\ub4e0 Table \uc774\ub984\ubcf4\uae30select table_name from user_tables \u25c6 Table\uc758 Primary Key \ucc3e\uae30select * from user_ind_columns where table_name = ”CodeTable” \u25c6 \uc778\uc218\uc804\ub2ecselect * from user_ind_columns where table_name = ”&1”===>; save keySQL> start key CodeTable \u25c6 \uc11c\ub85c \uc5f0\uacb0\ub418\ub294 Key\ub97c […]<\/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":"","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":"default","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-412","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\/412","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=412"}],"version-history":[{"count":0,"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=\/wp\/v2\/posts\/412\/revisions"}],"wp:attachment":[{"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=412"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=412"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=412"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}