約 2,086,142 件
https://w.atwiki.jp/adsl243/pages/13.html
バッファ関連 表の結合 表の作成/削除 シノニムの作成 権限 ビュー関連 トランザクション関連 関数 索引 お役立ち情報 SQL分の基礎 まずログインします。 solaris(SPARC)にインストールされたoracle10g(ver.10.2.0.1)に対して クライアント側からOracleInstantClientでログインします。 http //www24.atwiki.jp/adsl243/editx/13.html 今回はoracle10g(R10.2)がトライアル版の為OracleInstantClientを使用しています。 ダウンロードはOTNのtopページから 「ダウンロード⇒ページ上のDatabaseの中にあるInstant Client」で 該当のOSを選んでダウンロードして下さい。 OracleInstantClientのインストール方法はここを参考にしてください。 OracleInstantClientからのログイン手順 1.[Windows+R]でコマンドラインを起動しcmdとタイプします。 2.以下コマンドを入力しログインします。 書式 | sqlplus system/passwd@oracleサーバーのURL ポート番号/Oracle_SID 例) sqlplus system/passwd@192.168.150.42 1521/orcl 正常にログインされればプロンプトの表示が SQL になります。 必ずsystemユーザーでログインしてください。 一般ユーザーはデフォルトだとロックが掛かっている可能性が高いです。 ログインできない場合のエラーに対しての対処 ①リスナーが起動しているか下記コマンドで確認する。 lsnrctl status ステータスが停止だった場合は下記コマンドでリスナーを起動してください。 lsnrctl start ②$ORACLE_HOME/network/admin/tnsnames.oraの記述が間違っていないか確認する select文 表から列を選択するのに使います。 if.EMP表を全て出力したい場合。 例) select * from emp; 書式 | select 列名 from 表名 *は全ての列を指定しています。 式の終わりには「;」セミコロンが必要です。 ユーザーの作成 書式 | create user ユーザー名 idetified by password 例)create user hanako identified by neko デフォルト表領域と一時表領域を追加 書式 | create user ユーザー名 idetified by password 書式 | default tablespace デフォルト表領域 temporary tablespace 一時表領域 ; 例) create user yamada identified by taro default tablespace users temporary tablespace temp; 割り当て制限の設定 書式 | quota 割り当てサイズ on 表領域 例) quota 10m on users; ※割り当てサイズを無制限にする場合はunlimitedを指定する。 例) quota unlimited on users; ユーザー設定の変更 書式 | ater user 作成するユーザー名 identyfied by 新しいパスワード default_tablespace 新しいデフォルト表領域 temporary_tablespace 一時表領域 quota 割当て制限のサイズ on 割り当てを与える表領域 例) 山田のパスワードをtigerに変更 alter user yamada identified by tiger 例) 山田のデフォルト表領域を20mに変更 alter user yamada quota 20m on users 表の作成 書式 | create table 表名 ( 列の名前 列のデータ型 列の制約 , 列の名前 列のデータ型 列の制約 constraint 表の制約 ); 例) create table department( deptno NUMBER(2) NOT NULL, dname VARCHAR2(14), loc VARCHAR2(13), constraint dept_primary_key primary key (deptno) ); データの入力 insert into department( 入力する列名 ) values(入力するデータ); 例) insert into department(deptno, dname, loc) values(10, 開発 , 赤坂 ); ※すべての列に値を入れる場合。 insert into 表名 values(入力するデータ); 例) insedrt into department values(70, 開発 , 池袋 ); データの修正 update 変更する表 set 変更する列 = 変更する値 where 変更する行の条件 ※whereを省略すると表の全体を表示する。 例) update department set loc = 上野 where dname = 開発 ※開発部の場所が上野に変更になった。 すべての列を削除 truncate table 削除する表名 truncate table department deleteとの主な違いは列を切り捨てた後に自動でコミットされる。 よって文字通り元に戻せない。 対象の行にロックをかける 書式 | select * from 検索する表名 where 対象の列名 = 列の値 for update; 例) select * from department where deptno = 20 for update ロックをかけられなかった場合エラーを返す。 書式 | select * from 検索する表名 where 対象の列名 = 列の値 for update nowait; 例) select * from department where deptno = 30 for update nowait ※エラーの出力例 行1でエラーが発生しました。 ORA-00054 リソース・ビジー、NOWAITが指定されていました。 ユーザー削除(DROP USER権限が必要) systemユーザーでログインする。 他のユーザーが削除するユーザーでログインしている場合削除できない。 drop user ユーザー名 cascade 例) drop user yamada cascade 誰がデータベースにアクセスしているか確認 select sid, serial#, username, machine, program from v$session; データベースの停止/開始 shutdown immediate ※強制停止(DBに接続しているユーザーがいても有無を言わさず停止) shutdown normal ※通常停止(DBに接続しているユーザが接続を切るまで待機し切ったら停止) データベースの起動 startup データディクショナリのリストを取得 select table_name from dict where table_name like %TABLE% データディクショナリuser_tablesの簡単な説明 select * from dict where table_name= USER_TABLES ユーザー山田の表領域確認 select username, default_tablespace, temporary_tablespace from dba_users where username = YAMADA 表割り当て領域確認 select username, tablespace_name, max_bytes from dba_ts_quotas where username = YAMADA ユーザーの権限確認 select * from user_sys_privs; 一度与えたユーザー権限の取り消し 書式 | revoke システム権限の名前 from ユーザーの名前 例) ユーザー山田のCREATE USER権限取り消し revoke create user from yamada 例)全てのユーザーでscottのemp表を検索されないようにする。 revoke all on scott.emp from public; ロールの作成 例) create role ap1_user ロールの削除 書式 | drop role 削除するロールの名前 例) drop role ap1_user 表の追加 書式 | alter table 表の名前 add ( 名前 データ型 ); 例) alter table del_me add ( col timestamp(3) with time zone ); 列の削除 書式 | alter table dell_me drop (削除対象名) CASCADE CONSTRAINTS; 例) alter table dell_me drop (id) CASCADE CONSTRAINTS;
https://w.atwiki.jp/atyou/pages/40.html
UNIXでSQLPLUSを起動し繰り返しSQLを実行 $ sh xxx.sh ↓ xxx.shの中身 ------------------------ sqlplus user/pass jobstop_c set pagesize 9999 set echo on ho echo"実行するsql文を表示する" select * from XXXXX; ←実際のコマンド1 ho echo"実行するsql文を表示する" select * from XXXXX; ←実際のコマンド2 ho echo"実行するsql文を表示する" select * from XXXXX; ←実際のコマンド3 exit; jobstop_c [EOF]
https://w.atwiki.jp/sunbalcan/pages/20.html
sql*plus環境変数 ◆環境設定 デフォルト値 appinfoはOFFであり、設定先は "SQL*Plus"です。 arraysize 15 autocommit OFF insertやupdateやdeleteなどによるデータベースの変更を、 commitするかどうか autoprint OFF autorecovery OFF autotrace OFF blockterminator "." (hex 2e) btitle OFF cmdsep OFF 1行中に複数のSQL*Plusコマンドを使用できるかどうかを指定する colsep " " compatibility version NATIVE Oracle SQLのバージョンを指定する concat "." (hex 2e) copycommit 0 COPYTYPECHECKは、ON define " " (hex 26) describe DEPTH 1 LINENUM OFF INDENT ON echo OFF コマンドファイル実行時に、 コマンドファイル中の各コマンドを画面に表示 editfile "afiedt.buf" embedded OFF escape OFF 6 行以上に対するFEEDBACKがONです。 flagger OFF flush ON heading ON 問い合わせ結果表示する時に列名を 表示するかどうかを指定する headsep "|" (hex 7c) instance "local" linesize 80 1行に表示する文字のサイズを指定する lno 14 loboffset 1 logsource "" long 80 longchunksize 80 newpage 1 null "" numformat numwidth 10 pagesize 14 pause off 一画面ごとにユーザがEnterを押すまで画面表示がとまるにする pno 0 recsep WRAP recsepchar " " (hex 20) release 902000600 repfooter OFF であり、NULLです repheader OFF であり、NULLです serveroutput OFF shiftinout INVISIBLE showmode OFF setコマンドを使用してシステム変数を変更するときに、 変更前と変更後のシステム変数を表示するかどうかを指定します。 spool OFF sqlblanklines OFF sqlcase MIXED SQLコマンド実行前にSQLまたはPL/SQLブロックを大文字、小文字に変換 set sqlc[ase] [mix]ed /up[per]/ lo[wer]" sqlcode 0 sqlcontinue sqlnumber ON sql*plus上の行番号の表示/非表示切り替え sqlpluscompatibility 8.1.7 sqlprefix (hex 23) sqlprompt "SQL " プロンプト表示を変更 set sqlp[rompt] 新プロンプト名 sqlterminator ";" (hex 3b) suffix "sql" SQL*Plusが使用するファイルの、デフォルトのファイル拡張子を格納 tab ON SQL*Plusが空白をどのように端末に出力するかを設定 termout ON time OFF SQLプロンプトに時間を表示 set ti[me] on timing OFF 経過時間を表示するかどうかを指定 set timi[ng] on trimout ON 行の終わりにある空白を入れるかどうかを指定する trimspool OFF ttitle OFF underline "-" (hex 2d) verify ON wrap 表示しきれない行は折り返されて表示
https://w.atwiki.jp/kane2008/pages/25.html
コマンドプロンプトによるSQLplus使用時の列幅変更 コマンドプロンプトでSQLplusを起動後、 col 列名 format a## ##に数値を入力(例 a10)
https://w.atwiki.jp/tak_is/pages/39.html
2011-10-11 15 44 00 (Tue); ストアドパッケージ パッケージ名の修飾を忘れるとはまる。 sqlplus scott/tiger@db set serveroutput on declare ret_code VARCHAR2(1); ret_msg VARCHAR2(256); begin -- パッケージ実行 scott.pkg.pkg_proc( 10000 , 1 , 999 ,ret_code,ret_msg); -- 戻り値出力 dbms_output.put_line(ret_code); dbms_output.put_line(ret_msg); end; /
https://w.atwiki.jp/tak_is/pages/42.html
2011-10-20 16 10 28 (Thu); 起動方法 ファイル名を指定して実行 ‥ sqlplus /nolog ‥ sqlplus system/manager@db as sysdba ※SQL*Plus(sqlplusw.exe)を使うか、DOS窓(sqlplus.exe)で使うかは好みによるが、 DOS窓(DOSの機能を利用できる)方が個人的には便利だと思う。 DOS窓(CUIのsqlplus.exe)の方が便利な理由 ‥ カーソル↑↓キーで実行履歴が表示される ‥ F7キーでコマンド実行履歴が表示される ‥ ホイールマウスでスクロールできる 弱点としては ‥ ちょっと見づらい ‥ コピペがちょっと面倒
https://w.atwiki.jp/ora_tips/pages/32.html
①実行ファイルの内容を画面に表示する set echo on 例) SQL set echo on SQL @a.sql SQL begin SQL dbms_output.put_line( OK ); SQL end; SQL / PL/SQLプロシジャが正常に完了しました
https://w.atwiki.jp/ishiwiki/pages/12.html
■コマンドプロンプトで、sqlplusに接続する sqlplus id/pw@dbname sqlplus wiki
https://w.atwiki.jp/wiki8_m2/pages/14.html
13.1 SQL*Plus 【SQL*Plusの主な特徴】 SQLを対話形式で簡単に実行するためのアプリケーション 入力されたSQLをOracleサーバーへ送り、oRACLEサーバーから戻されたデータを編集(列見出しを付けるなど)して表示する。 【SQL*Plusコマンドの主な特徴】 データベース内のデータを直接操作できない(SQLだけがデータを操作できる) SQLを実行するための環境の設定、ファイル処理、SQLバッファの編集などを行う。 キーワードの短縮形がサポートされている。 Oracle独自の技術である。 13.2 SQLコマンドについて(一例) ◆SAVE・・・SQLバッファ内のSQL文をファイルに保存する →SAVE ファイル名 APPEND | REPRACE ◆DESCRIBE・・・表の定義を確認する。 →DESCRIBE 表名 ◆GET・・・ファイル内の内容をSQLバッファにロードする。 →GET ファイル名 13.3 バッファ内SQL文の編集 SQL*Plusは、入力したSQLコマンドをSQLバッファと呼ばれる領域に格納する。 新たなSQLコマンドが入力されるまで保持する。 SQL*Plusで最後に実行されたSQL文が保存される。 SQL*PlusコマンドはSQLバッファに保存されない。 【SQL*Plusの編集コマンド】 ◆A (文字列)・・・原稿行の最後に文字列を追加する。 ◆CHANGE/(変更前の文字列)/(変更後の文字列)・・・原稿行の文字列を変更する。 →セパレータ("/"の部分)は他の文字を使用することもできる。 (例)C#HOGE#HOGE/HOGE・・・HOGEをHOGE/HOGEに変更 ◆DEL・・・原稿行を削除する。 ◆CLEAR BUFFER・・・バッファの保存されている内容を全て削除する。 ◆INPUT [(文字列)]・・・行を挿入する。 ◆LIST [m] [n]・・・バッファの内容を表示する。 ◆RUN・・・バッファ内のSQL文を実行する。 ◆n・・・現行行をn行目とする。 ◆/・・・バッファ内のSQL文を実行する。 13.5 SQL*Plusのファイルコマンド SQL*Plusには、コマンドファイル(スクリプトファイル)の作成と実行を行う機能がある。 【SQL*Plusのファイルコマンド】 ◆@(ファイル名)・・・ファイルの内容を実行する。 ◆START (ファイル名)・・・ファイルの内容を実行する。 ◆SAVE (ファイル名)・・・1SQLバッファの内容をファイルに保存する。 ◆GET (ファイル名)・・・ファイルの内容をSQLバッファにロードする。 ◆EDIT (ファイル名)・・・OSエディタを使用して、SQLバッファまたはファイルの内容を編集する。 13.6 SQL*Plusのスプール機能 SQL*Plusのスプール機能を使い、出力結果をファイルに記録できる。 【SPOOLコマンドの使用方法】 ◆SPOOL (ファイル名)・・・スプールを開始する。 ◆SPOOL OFF・・・スプールを停止する。 ◆SPOOL OUT・・・スプールを停止し、デフォルトプリンタで印刷する。 ◆SPOOL・・・現行行のスプール状態を表示する。
https://w.atwiki.jp/sunbalcan/pages/6.html
sql*plusコマンド ◆SQL*Plusアルファベット順索引 @ 指定したファイルに記述されているSQL*Plusコマンドや SQLコマンドを実行 @file_name.sql / -Buffer操作- 現在SQLバッファに格納されているSQLコマンドまたは PL/SQLブロックを実行 accept ユーザ変数を作成し、そのユーザ変数にキーボードからの 入力を格納 acc[ept] a append -Buffer操作- バッファ内のカレント行の終わりに文字列を追加する a[ppend] 追加したい文字列 change -Buffer操作- バッファ内のカレント行の文字列を変更する change /変更前文字列/変更後文字列 clear buffer -Buffer操作- 現在のバッファをクリア cl[ear] buff[er] clear column columnコマンドを使用して設定した全ての列の表示属性を クリア cl[ear] col[umns] clear screen 画面をクリア cl[ear] scr[een] clear sql -Buffer操作- 現在のバッファをクリア cl[ear] sql clear timing 全てのタイマーを削除 cl[ear] timi[ng] column col[umn] 列名 for[mat] 書式 heading 別名 connect connectコマンドは、現在変更中のデータベースに 対してcommitし、現在ログインしているユーザーを Oracle から切断し、新たに指定したユーザーで Oracleに接続します。 define defineコマンドを使用してユーザ変数を定義 def[ine] ユーザ変数 = 定義したい文字列 del -Buffer操作- バッファ内の行を削除する del 2 ⇒行指定 del n m ⇒行指定(from to) del last ⇒バッファ内の最終行を削除する。 del n last ⇒バッファ内のn行目から最終行までを 削除する。但し、n lastはエラー。 del n * ⇒バッファ内のn行目からカレント行までを 削除する。但し、n *はエラー。 del * n ⇒バッファ内のカレント行からn行目を削除する。 但し、* nはエラー。 del n last ⇒バッファ内のn行目から最終行までを削除する。 但し、n lastはエラー。 describe テーブル定義を表示する disconnect SQL*Plusを終了せずにユーザーログアウト disc[onnect] edit(バッファ編集) -Buffer操作- 現在のSQLバッファの内容をシステムエディタで編集する ed[it] edit(ファイル編集) ホストOSのファイルをホストOSのエディタで編集する ed[it] ファイル名 exit SQL*Plusを終了する get -Buffer操作- ホストOSのファイルをSQLバッファ内に保存する get ファイル名 [list|nolist] help help コマンド名 host SQL*Plusログイン中にホストOSのコマンドを実行する ex)host ls or !ls input SQLバッファ内のカレント行の後に、1行の文字列を追加 input 挿入したい文字列 input list -Buffer操作- 現在のバッファを表示する password 指定したユーザのパスワードを変更する passw[ord] ユーザ名 passw[ord] pause 画面一時停止 一時停止を解除するにはユーザがEnterを押す必要がある pau[se] pau[se] 表示したいメッセージ ※pauseコマンドは通常、コマンドファイル内で使用される prompt promptコマンドは、指定したメッセージを画面に表示する pro[mpt] pro[mpt] 画面に表示したいメッセージ quit SQL*Plusを終了する remark コマンドファイルの中でコメントを入れる rem[ark] コメント run -Buffer操作- バッファ内容を表示してから問い合わせ結果を表示 save 現在のSQLバッファをホストOSのファイルとして保存する sav[e] ファイル名 sav[e] ファイル名 replace set 現在のセッションにおけるシステム変数の値を変更する set システム変数名 設定値 show システム変数の値を表示する sho[w] システム変数 Oracle のリリース番号を表示する sho[w] rel[ease] 現在Oracleにログインしているユーザ名を表示する sho[w] user 最新の操作のSQLリターン・コードsql.sqlcodeの値を表示する sho[w] sqlcode 全てのシステム変数の値やOracleログインユーザ名や sqlcodeなど全てを表示する sho[w] all start 指定したファイルに記述されているSQL*Plusコマンドや SQLコマンドを実行 start file_name.sql store 現在のSQL*Plus環境を、ホストOSのファイルとして保存 store set env.ini store set ファイル名 cre[ate] store set ファイル名 rep[lace] store set ファイル名 app[end] timing タイマーを設定 timi[ng] start timer1 timi[ng] show undefine defineコマンドを使用して定義したユーザー変数を削除 undef[ine] ユーザ変数 [ユーザ変数 ・・・] whenever oserror OSエラーが発生した場合、そのエラーを契機に処理を行う whenever sqlerror SQL、PL/SQLエラーが発生した場合、そのエラーを契機に 処理を行う