set serveroutput on DECLARE CURSOR ids is select customer_id from customer_mst; customers ids%ROWTYPE; rnd NUMBER(2); BEGIN open corp2_ids; LOOP FETCH ids INTO customers; --50~95の整数ランダム値を返す rnd := trunc(DBMS_RANDOM.VALUE(50,95), 0); DBMS_OUTPUT.PUT_LINE(rnd); update customer_mst set score = rnd where customer_id = customers.customer_id; EXIT WHEN ids%NOTFOUND; END LOOP; commit; close ids; END; /
Category: ‘Oracle’
PL/SQLランダム値の生成
初めてのPL/SQL
set serveroutput on DECLARE CURSOR customer_ids is select customer_id from customer_mst where customer_id in (select customer_id from customers where customer_id in (select customer_id from customer_lst where customer_id = 126 )); customers customer_ids%ROWTYPE; BEGIN open customer_ids; LOOP FETCH customer_ids INTO customers; DBMS_OUTPUT.PUT_LINE(customers.customer_id); update customer_mst set exists_flg = 1 where customer_id = customers.customer_id; EXIT WHEN customer_ids%NOTFOUND; END LOOP; commit; close customer_ids; END; /
tnsnames.ora 記述方法
# tnsnames.ora Network Configuration File: C:oracleproduct11.1.0orahomenetworkadmin.ora # Generated by Oracle configuration tools. MYDB_LOCAL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = MYDB) ) ) MYDB_SERVER = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = server_name)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = MYDB) (SID = MYDB) ) )
Oracleインスタンス起動方法
今更ながら、Oracleインスタンス起動方法
[oracle@server ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on 水 12月 21 15:36:49 2011 Copyright (c) 1982, 2009, Oracle. All rights reserved. アイドル・インスタンスに接続しました。 SQL> startup ORACLEインスタンスが起動しました。 Total System Global Area 1.0088E+10 bytes Fixed Size 2215984 bytes Variable Size 6710890448 bytes Database Buffers 3355443200 bytes Redo Buffers 19640320 bytes データベースがマウントされました。 データベースがオープンされました。
SQLがロック若しくはフリーズした場合
ロックが掛っているセッションを確認する方法
SELECT object_name,oracle_username, S.sid, S.serial#, S.logon_time, sql_address FROM v$locked_object L,dba_objects D, v$session S WHERE L.OBJECT_ID = D.OBJECT_ID AND L.SESSION_ID = S.SID
実行されているSQLを確認する方法
SELECT sid, serial#, username, logon_time, SQL_TEXT FROM v$session s, v$sql q WHERE s.SQL_ADDRESS=q.ADDRESS
実行されているセッションをKILLする方法
ALTER SYSTEM kill session 'sid, serial#';
スキーマを超えたテーブルのコピー
system ユーザ(全schemaをまたげる)権限がある場合。
CREATE TABLE schema_b.to_table AS SELECT * FROM schema_a.from_table;
UPDATE SELECT について
とあるマスタテーブルから、データを移動するときに便利。
INSERT SELECT があるのは知っていたが、UPDATE SELECT があるのは気が付かなかった。
UPDATE emp E SET (dept_name, dept_kana ) = (SELECT dept_name, dept_kana FROM dept D WHERE E.dept_no = D.dept_no) WHERE EXISTS (SELECT 1 FROM dept D2 WHERE D2.dept_no = E.dept_no);
ALTER TABLE ADD COLUMNS時のエラー
SQLエラー: ORA-00904: : 無効な識別子です。
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
ALTER TABLE hoge ADD ( col1 NUMBER(1) DEFAULT 0, col2 NUMBER(1) DEFAULT 0, col3 NUMBER(1) DEFAULT 0, );
ハマった割には、大したことはなかった。
col3 の最終文字に “,” が入っていただけ・・・。
うっかり。
ちなみに、NOT NULL項目を追加する場合は、必ず DEFAULT を付加する必要あり。怒られます。
Oracle テーブル名の変更
滅多に変更しないが覚え書き。
SQL> ALTER TABLE old_table RENAME TO new_table;
Oracle 一括Drop table
一括してテーブル等を削除したいことがあるのだが、どうやらワイルドカード(*など)で指定することは不可能っぽい。PL/SQLで一括DROP可能であるらしいが・・・。
問題がなければ、下記にて所有するオブジェクト全て削除するのでスッキリする。
SQL> DROP USER user_name CASCADE;
system領域等に間違ってオブジェクトを作成してしまったりすると最悪です。