SQL/Oracle
をテンプレートにして作成
[
トップ
] [
新規
|
一覧
|
単語検索
|
最終更新
|
ヘルプ
|
ログイン
]
開始行:
[[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=DE...
}}
- DataDumpエクスポート
#highlighter(MySQL,number){{
EXPDP DEV_DEST/DEV_DEST@XE directory=ORG_PUMP_DIR dumpfi...
VERSION=11.2.0
}}
※インポートを想定するOracleのversionが古い場合のみ指定VER...
- Dumpエクスポート+インポート
#highlighter(MySQL,number){{
imp DEV_DEST/DEV_DEST@XE file=C:\TEMP\DUMP\DEV_SRC_209912...
exp DEV_DEST/DEV_DEST@XE file=C:\TEMP\DUMP\DEV_DEST_21000...
}}
- アカウントロック解除
#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...
}}
- 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 C...
,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]]
終了行:
[[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=DE...
}}
- DataDumpエクスポート
#highlighter(MySQL,number){{
EXPDP DEV_DEST/DEV_DEST@XE directory=ORG_PUMP_DIR dumpfi...
VERSION=11.2.0
}}
※インポートを想定するOracleのversionが古い場合のみ指定VER...
- Dumpエクスポート+インポート
#highlighter(MySQL,number){{
imp DEV_DEST/DEV_DEST@XE file=C:\TEMP\DUMP\DEV_SRC_209912...
exp DEV_DEST/DEV_DEST@XE file=C:\TEMP\DUMP\DEV_DEST_21000...
}}
- アカウントロック解除
#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...
}}
- 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 C...
,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]]
ページ名:
![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
