SQL/Oracle の変更点 luckey!!!! - memo random 
#author("2018-08-21T10:37:50+09:00","ldap:pandora"," pandora")
#author("2020-09-25T16:22:11+09:00","ldap:pandora"," pandora")
[[SQL]]
#nofollow
#norelated
* ORACLE [#bd3f3620]

#contents

* ''コマンド集'' [#z8a85dab]
- スキーマ作成+権限付与
#highlighter(MySQL,number){{
 CREATE USER DEV_DEST
IDENTIFIED BY DEV_DEST
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
QUOTA UNLIMITED ON USERS;

 GRANT EXECUTE ON SYS.UTL_FILE TO PUBLIC;

 GRANT ALTER SESSION TO DEV_DEST;

 GRANT CREATE PROCEDURE TO DEV_DEST;

 GRANT CREATE SEQUENCE TO DEV_DEST;

 GRANT CREATE SESSION TO DEV_DEST;

 GRANT CREATE SYNONYM TO DEV_DEST;

 GRANT CREATE TABLE TO DEV_DEST;

 GRANT CREATE TRIGGER TO DEV_DEST;

 GRANT CREATE VIEW TO DEV_DEST;

 GRANT SELECT ON SYS.V_$SESSION TO DEV_DEST;

 GRANT SELECT ON SYS.V_$PROCESS TO DEV_DEST;

 GRANT ALTER SYSTEM TO DEV_DEST;

 GRANT DBA TO DEV_DEST;

 WHENEVER SQLERROR CONTINUE
 GRANT CREATE JOB TO DEV_DEST;
}}
- ディレクトリ作成+権限付与
#highlighter(MySQL,number){{
 CREATE DIRECTORY ORG_PUMP_DIR as 'C:\TEMP\DUMP'; 
 GRANT READ, WRITE ON DIRECTORY ORG_PUMP_DIR TO DEV_DEST;
}}
- DataDumpインポート
#highlighter(MySQL,number){{
 IMPDP system/admin@XE DIRECTORY=ORG_DUMP_DIR DUMPFILE=DEV_SRC_20991231.PMP REMAP_SCHEMA=DEV_SRC:DEV_DEST
}}
- DataDumpエクスポート
#highlighter(MySQL,number){{
 EXPDP DEV_DEST/DEV_DEST@XE directory=ORG_PUMP_DIR dumpfile=DEV_DEST_21000101.dmp SCHEMAS=DEV_DEST
VERSION=11.2.0
}}
※インポートを想定するOracleのversionが古い場合のみ指定VERSION=11.2.0
- Dumpエクスポート+インポート
#highlighter(MySQL,number){{
imp DEV_DEST/DEV_DEST@XE file=C:\TEMP\DUMP\DEV_SRC_20991231.dmp fromuser=DEV_SRC touser=DEV_DEST ignore=y
exp DEV_DEST/DEV_DEST@XE file=C:\TEMP\DUMP\DEV_DEST_21000101.dmp owner=DEV_DEST
}}
- アカウントロック解除
#highlighter(MySQL,number){{
alter user DEV_DEST account unlock;
}}
- プロシージャー実行
#highlighter(MySQL,number){{
declare
begin
	プロシージャー名;
end;
/
}}
* Tips [#ce7e418f]
- ディレクトリ権限確認
#highlighter(MySQL,number){{
SELECT NAME, VALUE FROM V$PARAMETER2 WHERE NAME='utl_file_dir' ;
}}
- DB内文字コード調査
#highlighter(MySQL,number){{
SELECT VALUE FROM NLS_DATABASE_PARAMETERS 
WHERE PARAMETER='NLS_CHARACTERSET'
}}
- 階層問い合わせ
#highlighter(MySQL,number){{
SELECT 子キー, 親キー ,　LEVEL
FROM   テーブル
WHERE 0 = 0
START WITH 親ルート  = 'XXX'
CONNECT BY PRIOR 親キー = 子キー 
}}
※LEVELはシステム変数 階層レベルが表示される。
- ソースの検索
#highlighter(MySQL,number){{
SELECT * FROM USER_SOURCE
WHERE UPPER(TEXT) LIKE '%EXECUTE%';
}}
※動的SQLでEXECUTEを使っている部分を検索
- テーブルコメント取得
#highlighter(MySQL,number){{
SELECT 
 TRIM(SUBSTR(B.COMMENTS,1,INSTR(B.COMMENTS,'//')-1)) AS COLUMN_COMMENTS
 ,X.TABLE_NAME
 ,X.COMMENTS
 ,B.COLUMN_NAME
FROM
  USER_TAB_COMMENTS X
    INNER JOIN USER_TAB_COLUMNS A
      ON X.TABLE_NAME = A.TABLE_NAME
    INNER JOIN USER_COL_COMMENTS B
      ON  A.TABLE_NAME = B.TABLE_NAME
      AND A.COLUMN_NAME = B.COLUMN_NAME
WHERE 0 = 0
 AND X.COMMENTS IS NOT NULL
 AND B.COMMENTS IS NOT NULL
 AND X.TABLE_NAME NOT LIKE '%_LCL_ALL'
 AND X.TABLE_NAME LIKE '%_ALL'
-- AND X.COMMENTS LIKE :NAME || '%'
ORDER BY
 A.TABLE_NAME
 ,A.COLUMN_ID;
}}
* 不具合 [#o32eef6c]
- 最大カラム数の不具合修正
#highlighter(MySQL,number){{
alter system set "_fix_control"='17376322:OFF'; 
}}
* 関連 [#t67667e8]
-[[SQL/MySQL]]
-[[SQL/Oracle/PLSQL]]
トップ   編集 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS