データベース管理に関するFAQ
ロックテーブル
どうして時計をロックしますか?
DMLロックは、行ロック、表ロック、デッドロックに分けられます
≪行ロック｜Row Locks｜oem_src≫:トランザクションがデータベースの挿入、更新、削除操作を実行すると、そのトランザクションは自動的にアクション・テーブル内の操作行の排他ロックを取得します.
≪表レベル・ロック｜Table Level Locks｜oem_src≫:トランザクションがロー・ロックを取得すると、そのローの表ロック(共有ロック)も自動的に取得され、DDL文がレコード・ローの更新に影響を与えないようにします.トランザクションは、実行中に共有ロックまたは排他ロックを取得することもできます.トランザクションがLOCK TABLE文で表示された定義の排他ロックを表示する場合にのみ、テーブル上の排他ロックが取得されます.また、LOCK TABLEで表示された定義のテーブルレベルの共有ロックも使用できます(LOCK TABLEの具体的な使用方法は、関連ドキュメントを参照してください).
デッドロック:2つのトランザクションが競合するロックのセットを必要とし、トランザクションを続行できない場合、デッドロックが発生します.トランザクション1がテーブルA行レコード＃3にロックされ、トランザクション2がテーブルAにレコード＃4にロックされるのを待つと、トランザクション2がテーブルAレコード行＃4にロックされ、トランザクション1がテーブルAにレコード＃3にロックされるのを待つと、トランザクション1とトランザクション2は互いに待機するため、デッドロックが発生する.デッドロックは一般的に拙劣なトランザクション設計によって発生します.デッドロックはSQLの下でしか使用できません:alter system kill session“sid,serial#”;または、UNIXのkill-9 sidのようなオペレーティングシステムkillプロセスに関するコマンドを使用するか、他のツールを使用してデッドロックプロセスを殺します.
DDLロックはまた、DDLロック、共有DDLロック、分析ロックに分けることができます.
排他的DDLロック:データベース・オブジェクトのDDL文を作成、変更、削除して、オペランドの排他的ロックを取得します.alter table文を使用する場合、データの完了性、一貫性、正当性を維持するために、トランザクションはDDLロックの列を取得します.
≪共有DDLロック｜Shared DDL Locks｜oem_src≫:データベース・オブジェクト間で相互依存関係を確立するDDL文は、通常、共有してDDLロックを取得する必要があります.パッケージを作成すると、そのパッケージのプロシージャと関数が異なるデータベース・テーブルを参照し、パッケージをコンパイルすると、トランザクションは参照テーブルの共有DDLロックを取得します.
分析ロック:ORACLEは共有プールストレージ分析と最適化したSQL文とPL/SQLプログラムを使用して、同じ文を実行する応用速度をもっと速くする.共有プールでキャッシュされたオブジェクトが、参照されるデータベース・オブジェクトの分析ロックを取得します.分析ロックは独特のDDLロックタイプで、ORACLEは共有プールオブジェクトと参照されるデータベースオブジェクト間の依存関係を追跡します.トランザクションが共有プールで分析ロックを持つデータベース・オブジェクトを変更または削除した場合、ORACLEは共有プール内のオブジェクトを無効にし、次回このSQL/PLSQL文を参照すると、ORACLEはこの文を再分析してコンパイルします.
関連するテーブル
SELECT * FROM v l o c k ; S E L E C T ∗ F R O M v lock; SELECT * FROM v lock;SELECT∗FROMvsqlarea; SELECT * FROM v s e s s i o n ; S E L E C T ∗ F R O M v session; SELECT * FROM v session;SELECT∗FROMvprocess ; SELECT * FROM v l o c k e d o b j e c t ; S E L E C T ∗ F R O M a l l o b j e c t s ; S E L E C T ∗ F R O M v locked_object; SELECT * FROM all_objects; SELECT * FROM v lockedobject;SELECT∗FROMallobjects;SELECT∗FROMvsession_wait;
-ロックされたテーブルselect b.owner,b.object_の表示name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;
-そのユーザーのプロセスがデッドロックselect b.username,b.sid,b.serial#,logon_time from v l o c k e d o b j e c t a , v locked_object a,v lockedobjecta,vsession b where a.session_id = b.sid order by b.logon_time;
-接続プロセスSELECT sid,serial#,username,osuser FROM v$sessionを表示します.
-セッションで実行中のsql文を調べることで、トランザクションが終了しないなど、どの操作やコードに迅速にナビゲートできます.
SELECT sql_text FROM v s q l t e x t a W H E R E ( a . h a s h v a l u e , a . a d d r e s s ) I N ( S E L E C T D E C O D E ( s q l h a s h v a l u e , 0 , p r e v h a s h v a l u e , s q l h a s h v a l u e ) , D E C O D E ( s q l h a s h v a l u e , 0 , p r e v s q l a d d r , s q l a d d r e s s ) F R O M v sqltext a WHERE (a.hash_value, a.address) IN (SELECT DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value),DECODE(sql_hash_value,0,prev_sql_addr,sql_address)FROMv sqltextaWHERE(a.hashvalue,a.address)IN(SELECTDECODECODE(sqlh ashvalue,0,prevh ashvalue,sqlh ashvalue),DECODEDE(sqlh ashvalue,0,prevs qla ddr,sqla ddress)FROMvessisionb WHErb.sid=sid=DECOCODEDECODECODECODE(sql_hash_hash_hash_hash_value,0,prev_sq‘321’)/*ここではSID*/ORDER BY piece ASCである.
-ロックテーブルのsid,serial#,os_を検出user_name, machine_name,terminal,ロックのtype,mode SELECT s.sid,s.serial#,s.username,s.schemaname,s.osuser,s.process,s.machine,s.terminal,s.logon_time, l.type FROM v s e s s i o n s , v session s, v sessions,vlock l WHERE s.sid = l.sid AND s.username IS NOT NULL ORDER BY sid;
この文は、データベース内のすべてのDML文によって生成されたロックを検索します.また、どのDML文も、テーブルロックとローロックの2つのロックを生成していることがわかります.
-プロセスsid,serial#alter system kill session’11112305’を殺す.
誤削除データ復旧
テーブルデータの削除にはdelete、drop、truncateの3つの方法があります.
delete誤削除の解決方法の原理:
Oracleが提供するフラッシュバック方法では、データを削除した後も大量の操作をしていない場合(削除されたデータのブロックが上書きされていないことを保証すれば)、フラッシュバック方式で削除されたデータを直接取り戻すことができます.
例:
100個のデータを誤って削除した
削除文は次のとおりです.
delete fromテーブル名where kid=‘5’;
*データが削除された時刻を特定します(データが削除される前の時刻でいいですが、データが削除された時点が望ましいです)
*削除文が実行された時刻は、次の文で確認できます.
select r.FIRST_LOAD_TIME,r.* from v$sqlarea r order by r.FIRST_LOAD_TIME desc ;
*削除されたデータを次の文で検索します.
select*fromテーブル名as of timestamp to_timestamp(‘削除時点’,‘yyyy-mm-dd hh 24:mi:ss’)where kid=‘5’
select*fromテーブル名as of timestamp sysdate-3/1440 where kid=‘5’;3分前のデータ
*削除したデータを元のテーブルに再挿入します.
プライマリ・キーが重複しないことに注意してください.
Insert intoテーブル名(select*fromテーブル名as of timestamp sysdate-3/1440 where kid=‘5’);
テーブル構造が変更されていない場合は、テーブル全体をフラッシュバックする方法を直接使用してデータを復元することもできます.
具体的な手順は次のとおりです.
テーブルのフラッシュバックには、ユーザーにflash any table権限が必要です.
-行移動機能をオンにする
・alter tableテーブル名enable row movement
–リカバリテーブルデータ・flashback tableテーブル名to timestamp to_timestamp(削除時点’,‘yyyy-mm-dd hh 24:mi:ss’)
-行移動機能をオフにする(忘れないでください)
・alter tableテーブル名disable row movement
drop、trancate誤削除の解決方法
原理:oracleはテーブルを削除する際に、テーブルが占めるブロックを直接空にしないため、oracleはこれらの削除したテーブルの情報を仮想コンテナ「回収ステーション」に格納し、そのテーブルのデータブロックに上書き可能なフラグを付けただけで、ブロックが再使用されない前に復元することができる.
具体的な手順:
*この「ごみ箱」または「user」を問い合わせるtableビューを使用して、削除されたテーブルを検索します.
· select table_name,dropped from user_tables
· select object_name,original_name,type,droptime from user_recyclebin
以上の情報では、テーブル名はすべて名前が変更され、フィールドtable_nameまたはobject_nameは削除後のごみ箱に格納されているテーブル名です
*テーブル名も覚えている場合は、次の文で直接リカバリできます.
Flashback table原表名to before drop
覚えていない場合は、ごみ箱のテーブル名をそのまま使用してリカバリし、名前を変更することもできます.次の文を参照してください.
Flashback table「ごみ箱内のテーブル名(例:Bin$DSbdfd 4 rdfdfdfegdfsf=$0)」to before drop rename to新テーブル名
oracleのフラッシュバック機能は、上記の基本機能に加えて、データベース全体をフラッシュバックできます.
データベースのフラッシュバック機能を使用すると、データベースを過去の状態に戻すことができます.構文は次のとおりです.
SQL>alter database flashback on SQL>flashback database to scn SCNNO; SQL>flashback database to timestamp to_timestamp(‘2007-2-12 12:00:00’,‘yyyy-mm-dd hh24:mi:ss’);
エラー更新リカバリ
*削除文が実行された時刻は、次の文で確認できます.
select r.FIRST_LOAD_TIME,r.* from v$sqlarea r order by r.FIRST_LOAD_TIME desc ;
*取り出したバックアップデータを保存するテーブルを作成
create tableテーブル名_bak
as
select*fromテーブル名as of timestamp sysdate-5/1440;
操作に成功したら、新しいテーブルの中にあなたの前のデータがあるかどうかを見てください.もしそうなら、新しい時計のデータを元の時計に戻せばいいです.
どうして時計をロックしますか?
DMLロックは、行ロック、表ロック、デッドロックに分けられます
≪行ロック｜Row Locks｜oem_src≫:トランザクションがデータベースの挿入、更新、削除操作を実行すると、そのトランザクションは自動的にアクション・テーブル内の操作行の排他ロックを取得します.
≪表レベル・ロック｜Table Level Locks｜oem_src≫:トランザクションがロー・ロックを取得すると、そのローの表ロック(共有ロック)も自動的に取得され、DDL文がレコード・ローの更新に影響を与えないようにします.トランザクションは、実行中に共有ロックまたは排他ロックを取得することもできます.トランザクションがLOCK TABLE文で表示された定義の排他ロックを表示する場合にのみ、テーブル上の排他ロックが取得されます.また、LOCK TABLEで表示された定義のテーブルレベルの共有ロックも使用できます(LOCK TABLEの具体的な使用方法は、関連ドキュメントを参照してください).
デッドロック:2つのトランザクションが競合するロックのセットを必要とし、トランザクションを続行できない場合、デッドロックが発生します.トランザクション1がテーブルA行レコード＃3にロックされ、トランザクション2がテーブルAにレコード＃4にロックされるのを待つと、トランザクション2がテーブルAレコード行＃4にロックされ、トランザクション1がテーブルAにレコード＃3にロックされるのを待つと、トランザクション1とトランザクション2は互いに待機するため、デッドロックが発生する.デッドロックは一般的に拙劣なトランザクション設計によって発生します.デッドロックはSQLの下でしか使用できません:alter system kill session“sid,serial#”;または、UNIXのkill-9 sidのようなオペレーティングシステムkillプロセスに関するコマンドを使用するか、他のツールを使用してデッドロックプロセスを殺します.
DDLロックはまた、DDLロック、共有DDLロック、分析ロックに分けることができます.
排他的DDLロック:データベース・オブジェクトのDDL文を作成、変更、削除して、オペランドの排他的ロックを取得します.alter table文を使用する場合、データの完了性、一貫性、正当性を維持するために、トランザクションはDDLロックの列を取得します.
≪共有DDLロック｜Shared DDL Locks｜oem_src≫:データベース・オブジェクト間で相互依存関係を確立するDDL文は、通常、共有してDDLロックを取得する必要があります.パッケージを作成すると、そのパッケージのプロシージャと関数が異なるデータベース・テーブルを参照し、パッケージをコンパイルすると、トランザクションは参照テーブルの共有DDLロックを取得します.
分析ロック:ORACLEは共有プールストレージ分析と最適化したSQL文とPL/SQLプログラムを使用して、同じ文を実行する応用速度をもっと速くする.共有プールでキャッシュされたオブジェクトが、参照されるデータベース・オブジェクトの分析ロックを取得します.分析ロックは独特のDDLロックタイプで、ORACLEは共有プールオブジェクトと参照されるデータベースオブジェクト間の依存関係を追跡します.トランザクションが共有プールで分析ロックを持つデータベース・オブジェクトを変更または削除した場合、ORACLEは共有プール内のオブジェクトを無効にし、次回このSQL/PLSQL文を参照すると、ORACLEはこの文を再分析してコンパイルします.
関連するテーブル
SELECT * FROM v l o c k ; S E L E C T ∗ F R O M v lock; SELECT * FROM v lock;SELECT∗FROMvsqlarea; SELECT * FROM v s e s s i o n ; S E L E C T ∗ F R O M v session; SELECT * FROM v session;SELECT∗FROMvprocess ; SELECT * FROM v l o c k e d o b j e c t ; S E L E C T ∗ F R O M a l l o b j e c t s ; S E L E C T ∗ F R O M v locked_object; SELECT * FROM all_objects; SELECT * FROM v lockedobject;SELECT∗FROMallobjects;SELECT∗FROMvsession_wait;
-ロックされたテーブルselect b.owner,b.object_の表示name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;
-そのユーザーのプロセスがデッドロックselect b.username,b.sid,b.serial#,logon_time from v l o c k e d o b j e c t a , v locked_object a,v lockedobjecta,vsession b where a.session_id = b.sid order by b.logon_time;
-接続プロセスSELECT sid,serial#,username,osuser FROM v$sessionを表示します.
-セッションで実行中のsql文を調べることで、トランザクションが終了しないなど、どの操作やコードに迅速にナビゲートできます.
SELECT sql_text FROM v s q l t e x t a W H E R E ( a . h a s h v a l u e , a . a d d r e s s ) I N ( S E L E C T D E C O D E ( s q l h a s h v a l u e , 0 , p r e v h a s h v a l u e , s q l h a s h v a l u e ) , D E C O D E ( s q l h a s h v a l u e , 0 , p r e v s q l a d d r , s q l a d d r e s s ) F R O M v sqltext a WHERE (a.hash_value, a.address) IN (SELECT DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value),DECODE(sql_hash_value,0,prev_sql_addr,sql_address)FROMv sqltextaWHERE(a.hashvalue,a.address)IN(SELECTDECODECODE(sqlh ashvalue,0,prevh ashvalue,sqlh ashvalue),DECODEDE(sqlh ashvalue,0,prevs qla ddr,sqla ddress)FROMvessisionb WHErb.sid=sid=DECOCODEDECODECODECODE(sql_hash_hash_hash_hash_value,0,prev_sq‘321’)/*ここではSID*/ORDER BY piece ASCである.
-ロックテーブルのsid,serial#,os_を検出user_name, machine_name,terminal,ロックのtype,mode SELECT s.sid,s.serial#,s.username,s.schemaname,s.osuser,s.process,s.machine,s.terminal,s.logon_time, l.type FROM v s e s s i o n s , v session s, v sessions,vlock l WHERE s.sid = l.sid AND s.username IS NOT NULL ORDER BY sid;
この文は、データベース内のすべてのDML文によって生成されたロックを検索します.また、どのDML文も、テーブルロックとローロックの2つのロックを生成していることがわかります.
-プロセスsid,serial#alter system kill session’11112305’を殺す.
誤削除データ復旧
テーブルデータの削除にはdelete、drop、truncateの3つの方法があります.
delete誤削除の解決方法の原理:
Oracleが提供するフラッシュバック方法では、データを削除した後も大量の操作をしていない場合(削除されたデータのブロックが上書きされていないことを保証すれば)、フラッシュバック方式で削除されたデータを直接取り戻すことができます.
例:
100個のデータを誤って削除した
削除文は次のとおりです.
delete fromテーブル名where kid=‘5’;
*データが削除された時刻を特定します(データが削除される前の時刻でいいですが、データが削除された時点が望ましいです)
*削除文が実行された時刻は、次の文で確認できます.
select r.FIRST_LOAD_TIME,r.* from v$sqlarea r order by r.FIRST_LOAD_TIME desc ;
*削除されたデータを次の文で検索します.
select*fromテーブル名as of timestamp to_timestamp(‘削除時点’,‘yyyy-mm-dd hh 24:mi:ss’)where kid=‘5’
select*fromテーブル名as of timestamp sysdate-3/1440 where kid=‘5’;3分前のデータ
*削除したデータを元のテーブルに再挿入します.
プライマリ・キーが重複しないことに注意してください.
insert into (select * from as of timestamp to_timestamp(' ','yyyy-mm-dd hh24:mi:ss') where kid = '5');
Insert intoテーブル名(select*fromテーブル名as of timestamp sysdate-3/1440 where kid=‘5’);
テーブル構造が変更されていない場合は、テーブル全体をフラッシュバックする方法を直接使用してデータを復元することもできます.
具体的な手順は次のとおりです.
テーブルのフラッシュバックには、ユーザーにflash any table権限が必要です.
-行移動機能をオンにする
・alter tableテーブル名enable row movement
–リカバリテーブルデータ・flashback tableテーブル名to timestamp to_timestamp(削除時点’,‘yyyy-mm-dd hh 24:mi:ss’)
-行移動機能をオフにする(忘れないでください)
・alter tableテーブル名disable row movement
drop、trancate誤削除の解決方法
原理:oracleはテーブルを削除する際に、テーブルが占めるブロックを直接空にしないため、oracleはこれらの削除したテーブルの情報を仮想コンテナ「回収ステーション」に格納し、そのテーブルのデータブロックに上書き可能なフラグを付けただけで、ブロックが再使用されない前に復元することができる.
具体的な手順:
*この「ごみ箱」または「user」を問い合わせるtableビューを使用して、削除されたテーブルを検索します.
· select table_name,dropped from user_tables
· select object_name,original_name,type,droptime from user_recyclebin
以上の情報では、テーブル名はすべて名前が変更され、フィールドtable_nameまたはobject_nameは削除後のごみ箱に格納されているテーブル名です
*テーブル名も覚えている場合は、次の文で直接リカバリできます.
Flashback table原表名to before drop
覚えていない場合は、ごみ箱のテーブル名をそのまま使用してリカバリし、名前を変更することもできます.次の文を参照してください.
Flashback table「ごみ箱内のテーブル名(例:Bin$DSbdfd 4 rdfdfdfegdfsf=$0)」to before drop rename to新テーブル名
oracleのフラッシュバック機能は、上記の基本機能に加えて、データベース全体をフラッシュバックできます.
データベースのフラッシュバック機能を使用すると、データベースを過去の状態に戻すことができます.構文は次のとおりです.
SQL>alter database flashback on SQL>flashback database to scn SCNNO; SQL>flashback database to timestamp to_timestamp(‘2007-2-12 12:00:00’,‘yyyy-mm-dd hh24:mi:ss’);
エラー更新リカバリ
*削除文が実行された時刻は、次の文で確認できます.
select r.FIRST_LOAD_TIME,r.* from v$sqlarea r order by r.FIRST_LOAD_TIME desc ;
*取り出したバックアップデータを保存するテーブルを作成
create tableテーブル名_bak
as
select*fromテーブル名as of timestamp sysdate-5/1440;
操作に成功したら、新しいテーブルの中にあなたの前のデータがあるかどうかを見てください.もしそうなら、新しい時計のデータを元の時計に戻せばいいです.