#author("2017-11-22T10:50:13+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はシステム変数 階層レベルが表示される。 * 不具合 [#o32eef6c] - 最大カラム数の不具合修正 #highlighter(MySQL,number){{ alter system set "_fix_control"='17376322:OFF'; }} * 関連 [#t67667e8] -[[SQL/MySQL]] -[[SQL/Oracle/PLSQL]]