助け合いフォーラム
Oracle Master Silver SQL 2019(1Z0-071)
問題ID : 26990
問題を開く
PINGTユーザーとして次のSQL文を実行しました。
CREATE SEQUENCE seq_id
MAXVALUE 100;
CREATE TABLE list
(
id NUMBER(3) DEFAULT seq_id.NEXTVAL PRIMARY KEY,
name VARCHAR2(10)
);
GRANT SELECT, INSERT ON list TO userA;
この後、userAユーザーとしてLIST表にデータを挿入する際の説明として正しいものはどれですか。
CREATE SEQUENCE seq_id
MAXVALUE 100;
CREATE TABLE list
(
id NUMBER(3) DEFAULT seq_id.NEXTVAL PRIMARY KEY,
name VARCHAR2(10)
);
GRANT SELECT, INSERT ON list TO userA;
この後、userAユーザーとしてLIST表にデータを挿入する際の説明として正しいものはどれですか。
正解
ID列の値を省略する場合は、順序SEQ_IDを参照する権限が必要である
解説
設問のSQL文では順序SEQ_IDを作成し、LIST表のID列のデフォルト値にNEXTVAL疑似列を指定しています。データの追加時にID列への値の入力が省略された場合に、順序SEQ_IDの一意な順序値が列に格納されます。
GRANT文では、userAユーザーにLIST表へのデータの参照と挿入に必要なオブジェクト権限を付与しています。これでuserAユーザーはPINGTユーザーのLIST表にデータを挿入することは可能です。ただし、ID列の値を省略する場合は順序SEQ_IDが使用されるので、順序SEQ_IDを参照するためのSELECTオブジェクト権限かSELECT ANY SEQUENCE権限が必要になります。
以上より、
・ID列の値を省略する場合は、順序SEQ_IDを参照する権限が必要である
が正解です。
以下は実行例です。
その他の選択肢については以下のとおりです。
・このまま、ID列の値を省略してLIST表へデータを挿入できる
userAユーザーには順序SEQ_IDを参照するための権限（SELECTオブジェクト権限かSELECT ANY SEQUENCE権限）が付与されていないので、このままではID列の値を省略してデータを追加することはできません。よって誤りです。
・他ユーザーの順序を使用できないので、LIST表へデータを挿入できない
適切な権限があれば順序は複数のユーザーで共有可能なので、誤りです。また、ID列の値を省略しなければLIST表への挿入は可能です。
・他セッションの順序を使用すると順序値が重複するので、ID列の値を省略できない
順序はデータベース内で一意な順序値を生成するので、誤りです。
GRANT文では、userAユーザーにLIST表へのデータの参照と挿入に必要なオブジェクト権限を付与しています。これでuserAユーザーはPINGTユーザーのLIST表にデータを挿入することは可能です。ただし、ID列の値を省略する場合は順序SEQ_IDが使用されるので、順序SEQ_IDを参照するためのSELECTオブジェクト権限かSELECT ANY SEQUENCE権限が必要になります。
以上より、
・ID列の値を省略する場合は、順序SEQ_IDを参照する権限が必要である
が正解です。
以下は実行例です。
その他の選択肢については以下のとおりです。
・このまま、ID列の値を省略してLIST表へデータを挿入できる
userAユーザーには順序SEQ_IDを参照するための権限（SELECTオブジェクト権限かSELECT ANY SEQUENCE権限）が付与されていないので、このままではID列の値を省略してデータを追加することはできません。よって誤りです。
・他ユーザーの順序を使用できないので、LIST表へデータを挿入できない
適切な権限があれば順序は複数のユーザーで共有可能なので、誤りです。また、ID列の値を省略しなければLIST表への挿入は可能です。
・他セッションの順序を使用すると順序値が重複するので、ID列の値を省略できない
順序はデータベース内で一意な順序値を生成するので、誤りです。
参考
【順序】
順序は、指定した規則に従って一意の番号を自動的に生成するスキーマ・オブジェクトです。順序は主に重複したデータを受け付けない、PRIMARY KEY制約が定義された列の値を生成する際に利用されます。
順序は複数のユーザーで共有可能なため、複数のユーザーが同じ順序で順序値を生成した場合、常に全体を通して一意な順序値(※)が生成されます。また、順序は表と関連付けられるものではないので、複数の表で1つの順序を使用することもできます。
※ただし、順序の作成時にCYCLEオプション(順序値が最大値または最小値に達した場合、初期値に戻り繰り返し順序を生成する)を指定した場合は、重複した順序値が生成されることもあります。
順序は指定された規則に従って一意な順序値を生成しますが、ロールバックが発生した時などには欠番が生じることもあり、連番が保証されているわけではありません。
以下で順序値が欠番になるケースを解説します。
[ロールバックが発生した時]
順序で値を生成したINSERT文がロールバックされると、表の状態はロールバックされますが、生成した順序値はロールバックされないので、欠番が生じる場合があります。
[システムがクラッシュした時]
順序をキャッシュする設定で作成すると、メモリ上に順序値がキャッシュされ、データディクショナリ（オブジェクトの情報を格納する表）に次に生成する順序値が書き込まれます(順序値を20個生成する設定の場合、メモリ上に1から20の順序値が生成され、データディクショナリに21が書き込まれる)。
順序値がキャッシュされている状態の時にシステムがクラッシュすると、メモリ上にキャッシュされている順序値が失われてしまいます。
Oracleサーバーには、データディクショナリに書き込まれた次に生成する順序値（この場合は21）しか残されていませんので、システムを再起動した時には、次に生成する順序値から順序値が生成され、クラッシュ前にメモリ上にキャッシュされていた順序値は欠番となります。
[複数の表で同じ順序を使用]
複数の表で同じ順序を使用している場合も、それら複数の表全体で一意な順序値が生成されるので、1つの表だけを見ると連番とならない場合があります。
【順序の作成】
順序の作成はCREATE SEQUENCE権限を持つユーザーによって行われます。
CREATE SEQUENCE 順序名
[INCREMENT BY n]
[START WITH n]
[MAXVALUE n | NOMAXVALUE]
[MINVALUE n | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE n | NOCACHE];
CREATE SEQUENCE文のオプションは次の通りです。
順序から新しい順序値を取得したり、現在の順序値を確認するためには、NEXTVAL疑似列、CURRVAL疑似列を参照します。
それぞれの参照方法は次の通りです。
順序名.NEXTVAL
順序名.CURRVAL
[NEXTVAL疑似列]
NEXTVAL疑似列を参照すると、新しい順序値が生成されて返されます。
[CURRVAL疑似列]
CURRVAL疑似列を参照すると、最後に生成された順序値が返されます。
NEXTVAL疑似列、CURRVAL疑似列は以下の箇所で参照することができます。
・主問合せのSELECT句
・INSERT文の副問合せのSELECT句
・INSERT文のVALUES句
・UPDATE文のSET句
・CREATE TABLE文またはALTER TABLE文の列のDEFAULT値
※Oracle 12cより、CREATE TABLE文やALTER TABLE文のデフォルト値にNEXTVAL疑似列とCURRVAL疑似列を指定できるようになりました。
また、NEXTVAL疑似列、CURRVAL疑似列は以下では使用できません。
・SELECT文、UPDATE文、DELETE文内の副問合せ
・ビューのSELECT句
・DISTINCTキーワードが指定されたSELECT文
・GROUP BY句、ORDER BY句、HAVING句を持つSELECT文
・集合演算子UNION、INTERSECT、MINUSによって別のSELECT文と結合されているSELECT文
・SELECT文のWHERE句
・CHECK制約の条件
なお、現在のセッションで一度も順序値が生成されていない(一度もNEXTVAL疑似列を参照していない)ときにCURRVAL疑似列を参照すると、エラーとなります
順序は、指定した規則に従って一意の番号を自動的に生成するスキーマ・オブジェクトです。順序は主に重複したデータを受け付けない、PRIMARY KEY制約が定義された列の値を生成する際に利用されます。
順序は複数のユーザーで共有可能なため、複数のユーザーが同じ順序で順序値を生成した場合、常に全体を通して一意な順序値(※)が生成されます。また、順序は表と関連付けられるものではないので、複数の表で1つの順序を使用することもできます。
※ただし、順序の作成時にCYCLEオプション(順序値が最大値または最小値に達した場合、初期値に戻り繰り返し順序を生成する)を指定した場合は、重複した順序値が生成されることもあります。
順序は指定された規則に従って一意な順序値を生成しますが、ロールバックが発生した時などには欠番が生じることもあり、連番が保証されているわけではありません。
以下で順序値が欠番になるケースを解説します。
[ロールバックが発生した時]
順序で値を生成したINSERT文がロールバックされると、表の状態はロールバックされますが、生成した順序値はロールバックされないので、欠番が生じる場合があります。
SQLを表示
CREATE TABLE departments6
AS
SELECT * FROM departments
WHERE 1 = 0;
CREATE sequence seq_dept;
INSERT INTO departments6
VALUES (seq_dept.NEXTVAL, 'Sales', 1001);
COMMIT;
INSERT INTO departments6
VALUES (seq_dept.NEXTVAL, 'Dev', 1002);
SELECT * FROM departments6;
ROLLBACK;
INSERT INTO departments6
VALUES (seq_dept.NEXTVAL, 'Dev', 1002);
SELECT * FROM departments6;
AS
SELECT * FROM departments
WHERE 1 = 0;
CREATE sequence seq_dept;
INSERT INTO departments6
VALUES (seq_dept.NEXTVAL, 'Sales', 1001);
COMMIT;
INSERT INTO departments6
VALUES (seq_dept.NEXTVAL, 'Dev', 1002);
SELECT * FROM departments6;
ROLLBACK;
INSERT INTO departments6
VALUES (seq_dept.NEXTVAL, 'Dev', 1002);
SELECT * FROM departments6;
[システムがクラッシュした時]
順序をキャッシュする設定で作成すると、メモリ上に順序値がキャッシュされ、データディクショナリ（オブジェクトの情報を格納する表）に次に生成する順序値が書き込まれます(順序値を20個生成する設定の場合、メモリ上に1から20の順序値が生成され、データディクショナリに21が書き込まれる)。
順序値がキャッシュされている状態の時にシステムがクラッシュすると、メモリ上にキャッシュされている順序値が失われてしまいます。
Oracleサーバーには、データディクショナリに書き込まれた次に生成する順序値（この場合は21）しか残されていませんので、システムを再起動した時には、次に生成する順序値から順序値が生成され、クラッシュ前にメモリ上にキャッシュされていた順序値は欠番となります。
[複数の表で同じ順序を使用]
複数の表で同じ順序を使用している場合も、それら複数の表全体で一意な順序値が生成されるので、1つの表だけを見ると連番とならない場合があります。
【順序の作成】
順序の作成はCREATE SEQUENCE権限を持つユーザーによって行われます。
CREATE SEQUENCE 順序名
[INCREMENT BY n]
[START WITH n]
[MAXVALUE n | NOMAXVALUE]
[MINVALUE n | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE n | NOCACHE];
SQLを表示
CREATE SEQUENCE s_id
INCREMENT BY 1
START WITH 1
MAXVALUE 100
MINVALUE 1
CYCLE
CACHE 20;
INCREMENT BY 1
START WITH 1
MAXVALUE 100
MINVALUE 1
CYCLE
CACHE 20;
CREATE SEQUENCE文のオプションは次の通りです。
順序から新しい順序値を取得したり、現在の順序値を確認するためには、NEXTVAL疑似列、CURRVAL疑似列を参照します。
それぞれの参照方法は次の通りです。
順序名.NEXTVAL
順序名.CURRVAL
[NEXTVAL疑似列]
NEXTVAL疑似列を参照すると、新しい順序値が生成されて返されます。
SQLを表示
CREATE SEQUENCE seq_id;
SELECT seq_id.NEXTVAL FROM dual;
SELECT seq_id.NEXTVAL FROM dual;
SELECT seq_id.NEXTVAL FROM dual;
SELECT seq_id.NEXTVAL FROM dual;
[CURRVAL疑似列]
CURRVAL疑似列を参照すると、最後に生成された順序値が返されます。
SQLを表示
CREATE SEQUENCE seq_id;
SELECT seq_id.NEXTVAL FROM dual;
SELECT seq_id.NEXTVAL FROM dual;
SELECT seq_id.CURRVAL FROM dual;
SELECT seq_id.NEXTVAL FROM dual;
SELECT seq_id.NEXTVAL FROM dual;
SELECT seq_id.CURRVAL FROM dual;
NEXTVAL疑似列、CURRVAL疑似列は以下の箇所で参照することができます。
・主問合せのSELECT句
・INSERT文の副問合せのSELECT句
・INSERT文のVALUES句
・UPDATE文のSET句
・CREATE TABLE文またはALTER TABLE文の列のDEFAULT値
※Oracle 12cより、CREATE TABLE文やALTER TABLE文のデフォルト値にNEXTVAL疑似列とCURRVAL疑似列を指定できるようになりました。
また、NEXTVAL疑似列、CURRVAL疑似列は以下では使用できません。
・SELECT文、UPDATE文、DELETE文内の副問合せ
・ビューのSELECT句
・DISTINCTキーワードが指定されたSELECT文
・GROUP BY句、ORDER BY句、HAVING句を持つSELECT文
・集合演算子UNION、INTERSECT、MINUSによって別のSELECT文と結合されているSELECT文
・SELECT文のWHERE句
・CHECK制約の条件
なお、現在のセッションで一度も順序値が生成されていない(一度もNEXTVAL疑似列を参照していない)ときにCURRVAL疑似列を参照すると、エラーとなります
SQLを表示
CREATE SEQUENCE seq_id;
SELECT seq_id.CURRVAL FROM dual;
SELECT seq_id.CURRVAL FROM dual;
シーケンスの参照について
投稿日 2024/01/24
他のスキーマのシーケンスを参照するには、こちらの回答には
順序SEQ_IDを参照するためのSELECTオブジェクト権限かSELECT ANY SEQUENCE権限が必要になります。
と記述がありますが、これは
SELECT ON pingt.SEQ_ID
権限でも参照可能ですか？
r
rink_rew
2024/01/24 16:00
ping-tさん提供の検証用データがあります。スクリプト形式で提供されているので、問題集で実行されているSQLはすぐに試せますよ。
https://ping-t.notion.site/Ping-t-Silver-SQL-6f833fb434994343bcc338233e058bb2
本件も、実際に実行してみれば一発でわかります。
SQL> CREATE SEQUENCE seq_id MAXVALUE 100;
順序が作成されました。
SQL> CREATE TABLE list (id NUMBER(3) DEFAULT seq_id.NEXTVAL PRIMARY KEY, name VARCHAR2(10));
表が作成されました。
SQL> GRANT SELECT, INSERT ON list TO userA;
権限付与が成功しました。
SQL> conn userA
パスワードを入力してください:
接続されました。
SQL> SELECT * FROM pingt.list;
レコードが選択されませんでした。
SQL> INSERT INTO pingt.list (name) VALUES ('Sato');
INSERT INTO pingt.list (name) VALUES ('Sato')
*
行1でエラーが発生しました。:
ORA-01031: 権限が不足しています
SQL> conn pingt
パスワードを入力してください:
接続されました。
SQL> GRANT SELECT ON pingt.SEQ_ID to userA;
権限付与が成功しました。
SQL> conn userA
パスワードを入力してください:
接続されました。
SQL> INSERT INTO pingt.list (name) VALUES ('Sato');
1行が作成されました。
SQL> SELECT * FROM pingt.list;
ID NAME
---------- ----------
1 Sato
SQL>
コメント
この投稿に対して返信しませんか？