Oracle/TIPS½¸
¤ò¥Æ¥ó¥×¥ì¡¼¥È¤Ë¤·¤ÆºîÀ®
[
¥È¥Ã¥×
] [
¿·µ¬
|
°ìÍ÷
|
ñ¸ì¸¡º÷
|
ºÇ½ª¹¹¿·
|
¥Ø¥ë¥×
|
¥í¥°¥¤¥ó
]
³«»Ï¹Ô:
// ²¼³¬ÁØÍѥƥó¥×¥ì¡¼¥È
#topicpath
----
//¤³¤³¤Ë¥³¥ó¥Æ¥ó¥Ä¤òµ½Ò¤·¤Þ¤¹¡£
#contents
Oracle¤Ï¤Û¤È¤ó¤ÉÃΤé¤Ê¤¤¤Î¤Ç¡¢¤È¤ê¤¢¤¨¤ºÈ÷˺¡£
**¥æ¡¼¥¶°ìÍ÷¤ò¼èÆÀ [#s01ef732]
-bash-3.2$ sqlplus / as sysdba
SQL> SELECT username FROM dba_users;
USERNAME
------------------------------
SYS
XDB
¡Á ³ä°¦
x¹Ô¤¬ÁªÂò¤µ¤ì¤Þ¤·¤¿¡£
SQL>
**³°Éô¥¡¼¤Î°ìÍ÷ [#maee463b]
SQL Plus¤Ê¤É¤Ç³ºÅö¥¹¥¡¼¥Þ¤Î¥æ¡¼¥¶¤Ç¥í¥°¥¤¥ó¤·¤Æ°Ê²¼¤ÎSQL...
select CONSTRAINT_NAME,TABLE_NAME,STATUS from
USER_CONSTRAINTS where CONSTRAINT_TYPE = 'R'
¥Æ¡¼¥Ö¥ë̾¤ÈFK̾¡¢¤½¤ì¤¬Í¸ú¾õÂÖ¤«¤ò³Îǧ¤Ç¤¤Þ¤¹¡£
SQL> select count(*) from USER_CONSTRAINTS where
CONSTRAINT_TYPE = 'R' and status='ENABLED';
COUNT(*)
----------
0
SQL> select count(*) from USER_CONSTRAINTS where
CONSTRAINT_TYPE = 'R' and status='DISABLED';
COUNT(*)
----------
39
¤³¤ó¤Ê´¶¤¸¤Ç¤¹¡£
¤Á¤Ê¤ß¤Ë
Unique Key
select CONSTRAINT_NAME,TABLE_NAME,STATUS from
USER_CONSTRAINTS where CONSTRAINT_TYPE = 'U'
Primary Key
select CONSTRAINT_NAME,TABLE_NAME,STATUS from
USER_CONSTRAINTS where CONSTRAINT_TYPE = 'P'
Foreign Key
select CONSTRAINT_NAME,TABLE_NAME,STATUS from
USER_CONSTRAINTS where CONSTRAINT_TYPE = 'R'
-[[Bronze SQLÊÔ¡¡Âè10²ó¡¡¥Ç¡¼¥¿¡¦¥Ç¥£¥¯¥·¥ç¥Ê¥ê¡¦¥Ó¥å¡¼¤Î...
**¥´¥ß¥Æ¡¼¥Ö¥ë¤Îºï½ü [#p044b50d]
purge user_recyclebin;
**SQL*Plus¤ÎTIPS [#e895e9d8]
***ÊÌ¥µ¡¼¥Ð¤ÎOracle¤ËÀܳ¤¹¤ë [#aab6e086]
-bash-3.2$ sqlplus hoge/pass@192.168.10.100:1521/xe
¤Ê¤É¤ÇOK!
***³°Éô¥Õ¥¡¥¤¥ë¤«¤éSQL¼Â¹Ô [#g41782bf]
sqlplus user/pass @hogehoge.sql
¤Çhogehoge.sql¤ÎSQL¤¬¼Â¹Ô¤Ç¤¤ë
***hogehoge.sql¤Îʸˡ [#jef18876]
-SQL*Plus¤Ï¤É¤¦¤â°ì¥«¥é¥à¤¢¤¿¤ê¤ÎÉý¤¬¶¹¤¯¡¢¤¹¤°¤ËÀÞ¤êÊÖ¤·...
set linesize 9999
¤Ç²£¤Ë¤É¡¼¤ó¤È¹¤¤É½¼¨¤Ë¤Ê¤ë¡£¡£
-SQL*Plus¤Ï¤¹¤°¤Ë¥Ø¥Ã¥À(¥«¥é¥à̾)¤òÁÞÆþ¤·¤Á¤ã¤¦¡£
set pagesize 10000
¤È¤«¤Ç¥Ø¥Ã¥À¤òÁÞÆþ¤·¤Ê¤¤¤è¤¦¤Ë¤¹¤ë
-¥Õ¥¡¥¤¥ë¤Ë½ñ¤½Ð¤¹ÊýË¡¡£¤É¤¦¤âhogehoge.sql¤Ç
spool result.txt
SQLʸ¡£
spool off
exit
¤È¤¹¤ë¤È½ÐÎÏ·ë²Ì¤ò¥Õ¥¡¥¤¥ë¤Ë½ñ¤½Ð¤¹¤ß¤¿¤¤¡£
***SQL*Plus¤ÈTeraTerm¤Çüâ¤Ê¤É¤¬Ê¸»ú²½¤±¤¹¤ë [#mb64a834]
DB¤Îʸ»ú¥³¡¼¥É¤òJA16SJISTILDE¤È¤·¤Æ¤¤¤ë¤È¤¡¢TerraTerm¤Çs...
export NLS_LANG=Japanese_Japan.JA16SJISTILDE
¤È¤·¤Æ´Ä¶ÊÑ¿ô¤òÀßÄꤷ¡¢¤µ¤é¤ËTeraTerm¤òSJIS¤Ë¤·¤Æ¤ª¤±¤ÐO...
***¥æ¡¼¥¶¤Îºï½ü [#i8c0fe81]
SQL> drop user hogehoge cascade;
**DB¤ÎExport,Import [#jdecb75d]
DB¥µ¡¼¥Ð¤Ëoracle¥æ¡¼¥¶¤Ç¥í¥°¥¤¥ó¤·¡¢²¼µ¥³¥Þ¥ó¥É¤ò¼Â¹Ô
exp hoge/fuga@xe owner=hoge file=hoge.20070709.dmp stat...
°ÕÌ£¤Ï¡Öhoge¥æ¡¼¥¶¤Ç¥¹¥¡¼¥Þhoge¤Î¥Æ¡¼¥Ö¥ë¤òÁ´¤Æ¥À¥ó¥×((...
exp userid=hoge/fuga tables=TABLE1 file=hoge_TABLE1.20...
¤Æ¥Æ¡¼¥Ö¥ë»ØÄê¤â²Äǽ¤Ç¤¹¡£
import¤Ï°Ê²¼¤ÎÄ̤ê
imp userid=hoge2/fuga2 tables=TABLE1 fromuser=hoge touse...
fromuser:¸µ¤Î¥æ¡¼¥¶ touser:¤Ê¤ó¤Æ¥¹¥¡¼¥Þ¤ËÆþ¤ì¤ë¤«¡£
**DDL¤ò¼èÆÀ¤¹¤ë¡£ [#n551e299]
[[£Ï£Ò£Á£Ã£Ì£Å¡¿¥ª¥é¥¯¥ë£Ó£Ñ£Ì¥ê¥Õ¥¡¥ì¥ó¥¹¡ÊDBMS_METADATA...
----
¤³¤Îµ»ö¤Ï
#vote(¤ª¤â¤·¤í¤«¤Ã¤¿[18],¤½¤¦¤Ç¤â¤Ê¤¤[4])
- linesize¤Ï¹Ô¤¹¤Ù¤Æ¤¬´Ö±ä¤Ó¤·¤Þ¤¹¡£ÆÃÄêÎó¤Î¤ß¤Î¥µ¥¤¥ºÊÑ...
#comment
#topicpath
SIZE(10){¸½ºß¤Î¥¢¥¯¥»¥¹:&counter;}
½ªÎ»¹Ô:
// ²¼³¬ÁØÍѥƥó¥×¥ì¡¼¥È
#topicpath
----
//¤³¤³¤Ë¥³¥ó¥Æ¥ó¥Ä¤òµ½Ò¤·¤Þ¤¹¡£
#contents
Oracle¤Ï¤Û¤È¤ó¤ÉÃΤé¤Ê¤¤¤Î¤Ç¡¢¤È¤ê¤¢¤¨¤ºÈ÷˺¡£
**¥æ¡¼¥¶°ìÍ÷¤ò¼èÆÀ [#s01ef732]
-bash-3.2$ sqlplus / as sysdba
SQL> SELECT username FROM dba_users;
USERNAME
------------------------------
SYS
XDB
¡Á ³ä°¦
x¹Ô¤¬ÁªÂò¤µ¤ì¤Þ¤·¤¿¡£
SQL>
**³°Éô¥¡¼¤Î°ìÍ÷ [#maee463b]
SQL Plus¤Ê¤É¤Ç³ºÅö¥¹¥¡¼¥Þ¤Î¥æ¡¼¥¶¤Ç¥í¥°¥¤¥ó¤·¤Æ°Ê²¼¤ÎSQL...
select CONSTRAINT_NAME,TABLE_NAME,STATUS from
USER_CONSTRAINTS where CONSTRAINT_TYPE = 'R'
¥Æ¡¼¥Ö¥ë̾¤ÈFK̾¡¢¤½¤ì¤¬Í¸ú¾õÂÖ¤«¤ò³Îǧ¤Ç¤¤Þ¤¹¡£
SQL> select count(*) from USER_CONSTRAINTS where
CONSTRAINT_TYPE = 'R' and status='ENABLED';
COUNT(*)
----------
0
SQL> select count(*) from USER_CONSTRAINTS where
CONSTRAINT_TYPE = 'R' and status='DISABLED';
COUNT(*)
----------
39
¤³¤ó¤Ê´¶¤¸¤Ç¤¹¡£
¤Á¤Ê¤ß¤Ë
Unique Key
select CONSTRAINT_NAME,TABLE_NAME,STATUS from
USER_CONSTRAINTS where CONSTRAINT_TYPE = 'U'
Primary Key
select CONSTRAINT_NAME,TABLE_NAME,STATUS from
USER_CONSTRAINTS where CONSTRAINT_TYPE = 'P'
Foreign Key
select CONSTRAINT_NAME,TABLE_NAME,STATUS from
USER_CONSTRAINTS where CONSTRAINT_TYPE = 'R'
-[[Bronze SQLÊÔ¡¡Âè10²ó¡¡¥Ç¡¼¥¿¡¦¥Ç¥£¥¯¥·¥ç¥Ê¥ê¡¦¥Ó¥å¡¼¤Î...
**¥´¥ß¥Æ¡¼¥Ö¥ë¤Îºï½ü [#p044b50d]
purge user_recyclebin;
**SQL*Plus¤ÎTIPS [#e895e9d8]
***ÊÌ¥µ¡¼¥Ð¤ÎOracle¤ËÀܳ¤¹¤ë [#aab6e086]
-bash-3.2$ sqlplus hoge/pass@192.168.10.100:1521/xe
¤Ê¤É¤ÇOK!
***³°Éô¥Õ¥¡¥¤¥ë¤«¤éSQL¼Â¹Ô [#g41782bf]
sqlplus user/pass @hogehoge.sql
¤Çhogehoge.sql¤ÎSQL¤¬¼Â¹Ô¤Ç¤¤ë
***hogehoge.sql¤Îʸˡ [#jef18876]
-SQL*Plus¤Ï¤É¤¦¤â°ì¥«¥é¥à¤¢¤¿¤ê¤ÎÉý¤¬¶¹¤¯¡¢¤¹¤°¤ËÀÞ¤êÊÖ¤·...
set linesize 9999
¤Ç²£¤Ë¤É¡¼¤ó¤È¹¤¤É½¼¨¤Ë¤Ê¤ë¡£¡£
-SQL*Plus¤Ï¤¹¤°¤Ë¥Ø¥Ã¥À(¥«¥é¥à̾)¤òÁÞÆþ¤·¤Á¤ã¤¦¡£
set pagesize 10000
¤È¤«¤Ç¥Ø¥Ã¥À¤òÁÞÆþ¤·¤Ê¤¤¤è¤¦¤Ë¤¹¤ë
-¥Õ¥¡¥¤¥ë¤Ë½ñ¤½Ð¤¹ÊýË¡¡£¤É¤¦¤âhogehoge.sql¤Ç
spool result.txt
SQLʸ¡£
spool off
exit
¤È¤¹¤ë¤È½ÐÎÏ·ë²Ì¤ò¥Õ¥¡¥¤¥ë¤Ë½ñ¤½Ð¤¹¤ß¤¿¤¤¡£
***SQL*Plus¤ÈTeraTerm¤Çüâ¤Ê¤É¤¬Ê¸»ú²½¤±¤¹¤ë [#mb64a834]
DB¤Îʸ»ú¥³¡¼¥É¤òJA16SJISTILDE¤È¤·¤Æ¤¤¤ë¤È¤¡¢TerraTerm¤Çs...
export NLS_LANG=Japanese_Japan.JA16SJISTILDE
¤È¤·¤Æ´Ä¶ÊÑ¿ô¤òÀßÄꤷ¡¢¤µ¤é¤ËTeraTerm¤òSJIS¤Ë¤·¤Æ¤ª¤±¤ÐO...
***¥æ¡¼¥¶¤Îºï½ü [#i8c0fe81]
SQL> drop user hogehoge cascade;
**DB¤ÎExport,Import [#jdecb75d]
DB¥µ¡¼¥Ð¤Ëoracle¥æ¡¼¥¶¤Ç¥í¥°¥¤¥ó¤·¡¢²¼µ¥³¥Þ¥ó¥É¤ò¼Â¹Ô
exp hoge/fuga@xe owner=hoge file=hoge.20070709.dmp stat...
°ÕÌ£¤Ï¡Öhoge¥æ¡¼¥¶¤Ç¥¹¥¡¼¥Þhoge¤Î¥Æ¡¼¥Ö¥ë¤òÁ´¤Æ¥À¥ó¥×((...
exp userid=hoge/fuga tables=TABLE1 file=hoge_TABLE1.20...
¤Æ¥Æ¡¼¥Ö¥ë»ØÄê¤â²Äǽ¤Ç¤¹¡£
import¤Ï°Ê²¼¤ÎÄ̤ê
imp userid=hoge2/fuga2 tables=TABLE1 fromuser=hoge touse...
fromuser:¸µ¤Î¥æ¡¼¥¶ touser:¤Ê¤ó¤Æ¥¹¥¡¼¥Þ¤ËÆþ¤ì¤ë¤«¡£
**DDL¤ò¼èÆÀ¤¹¤ë¡£ [#n551e299]
[[£Ï£Ò£Á£Ã£Ì£Å¡¿¥ª¥é¥¯¥ë£Ó£Ñ£Ì¥ê¥Õ¥¡¥ì¥ó¥¹¡ÊDBMS_METADATA...
----
¤³¤Îµ»ö¤Ï
#vote(¤ª¤â¤·¤í¤«¤Ã¤¿[18],¤½¤¦¤Ç¤â¤Ê¤¤[4])
- linesize¤Ï¹Ô¤¹¤Ù¤Æ¤¬´Ö±ä¤Ó¤·¤Þ¤¹¡£ÆÃÄêÎó¤Î¤ß¤Î¥µ¥¤¥ºÊÑ...
#comment
#topicpath
SIZE(10){¸½ºß¤Î¥¢¥¯¥»¥¹:&counter;}
¥Ú¡¼¥¸Ì¾: