Oracleいろいろ集 - TNET日々のメモ

データファイル

データファイルの一覧を確認する。

select * from dba_data_files

FILE_NAME	FILE_ID	TABLESPACE_NAME	BYTES	BLOCKS	STATUS	RELATIVE_FNO	AUTOEXTENSIBLE	MAXBYTES	MAXBLOCKS	INCREMENT_BY	USER_BYTES	USER_BLOCKS	ONLINE_STATUS	LOST_WRITE_PROTECT
C:\APP\MASAFUMI\PRODUCT\18.0.0\ORADATA\XE\USERS01.DBF	7	USERS	5242880	640	AVAILABLE	7	YES	34359721984	4194302	160	4194304	512	ONLINE	OFF
C:\APP\MASAFUMI\PRODUCT\18.0.0\ORADATA\XE\UNDOTBS01.DBF	4	UNDOTBS1	68157440	8320	AVAILABLE	4	YES	34359721984	4194302	640	67108864	8192	ONLINE	OFF
C:\APP\MASAFUMI\PRODUCT\18.0.0\ORADATA\XE\SYSTEM01.DBF	1	SYSTEM	880803840	107520	AVAILABLE	1	YES	34359721984	4194302	1280	879755264	107392	SYSTEM	OFF
C:\APP\MASAFUMI\PRODUCT\18.0.0\ORADATA\XE\SYSAUX01.DBF	3	SYSAUX	566231040	69120	AVAILABLE	3	YES	34359721984	4194302	1280	565182464	68992	ONLINE	OFF

表領域の一覧を確認する。

select * from SYS.dba_tablespaces

TABLESPACE_NAME	BLOCK_SIZE	INITIAL_EXTENT	NEXT_EXTENT	MIN_EXTENTS	MAX_EXTENTS	MAX_SIZE	PCT_INCREASE	MIN_EXTLEN	STATUS	CONTENTS	LOGGING	FORCE_LOGGING	EXTENT_MANAGEMENT	ALLOCATION_TYPE	PLUGGED_IN	SEGMENT_SPACE_MANAGEMENT	DEF_TAB_COMPRESSION	RETENTION	BIGFILE	PREDICATE_EVALUATION	ENCRYPTED	DEF_INMEMORY	SHARED	DEF_INDEX_COMPRESSION	LOST_WRITE_PROTECT	CHUNK_TABLESPACE
SYSTEM	8192	65536		1	2147483645	2147483645		65536	ONLINE	PERMANENT	LOGGING	YES	LOCAL	SYSTEM	NO	MANUAL	DISABLED	NOT APPLY	NO	HOST	NO	DISABLED	SHARED	DISABLED	OFF	N
SYSAUX	8192	65536		1	2147483645	2147483645		65536	ONLINE	PERMANENT	LOGGING	YES	LOCAL	SYSTEM	NO	AUTO	DISABLED	NOT APPLY	NO	HOST	NO	DISABLED	SHARED	DISABLED	OFF	N
UNDOTBS1	8192	65536		1	2147483645	2147483645		65536	ONLINE	UNDO	LOGGING	NO	LOCAL	SYSTEM	NO	MANUAL	DISABLED	NOGUARANTEE	NO	HOST	NO	DISABLED	SHARED	DISABLED	OFF	N
TEMP	8192	1048576	1048576	1		2147483645	0	1048576	ONLINE	TEMPORARY	NOLOGGING	NO	LOCAL	UNIFORM	NO	MANUAL	DISABLED	NOT APPLY	NO	HOST	NO	DISABLED	SHARED	DISABLED	OFF	N
USERS	8192	65536		1	2147483645	2147483645		65536	ONLINE	PERMANENT	LOGGING	NO	LOCAL	SYSTEM	NO	AUTO	DISABLED	NOT APPLY	NO	HOST	NO	DISABLED	SHARED	DISABLED	OFF	N

表領域を作成する

自動拡張無しの場合

CREATE TABLESPACE SCOTT2_TEST DATAFILE 'C:\APP\MASAFUMI\PRODUCT\18.0.0\ORADATA\XE\SCOTT2_TEST.dbf' SIZE 100M;

上記のようなオプション無しで作成した結果は以下となった。

select * from dba_data_files

FILE_NAME	FILE_ID	TABLESPACE_NAME	BYTES	BLOCKS	STATUS	RELATIVE_FNO	AUTOEXTENSIBLE	MAXBYTES	MAXBLOCKS	INCREMENT_BY	USER_BYTES	USER_BLOCKS	ONLINE_STATUS	LOST_WRITE_PROTECT
C:\APP\MASAFUMI\PRODUCT\18.0.0\ORADATA\XE\SYSTEM01.DBF	1	SYSTEM	880803840	107520	AVAILABLE	1	YES	34359721984	4194302	1280	879755264	107392	SYSTEM	OFF
C:\APP\MASAFUMI\PRODUCT\18.0.0\ORADATA\XE\SYSAUX01.DBF	3	SYSAUX	576716800	70400	AVAILABLE	3	YES	34359721984	4194302	1280	575668224	70272	ONLINE	OFF
C:\APP\MASAFUMI\PRODUCT\18.0.0\ORADATA\XE\UNDOTBS01.DBF	4	UNDOTBS1	68157440	8320	AVAILABLE	4	YES	34359721984	4194302	640	67108864	8192	ONLINE	OFF
C:\APP\MASAFUMI\PRODUCT\18.0.0\ORADATA\XE\USERS01.DBF	7	USERS	5242880	640	AVAILABLE	7	YES	34359721984	4194302	160	4194304	512	ONLINE	OFF
C:\APP\MASAFUMI\PRODUCT\18.0.0\ORADATA\XE\SCOTT2_TEST.DBF	13	SCOTT2_TEST	104857600	12800	AVAILABLE	13	NO	0	0	0	103809024	12672	ONLINE	OFF

select * from SYS.dba_tablespaces

TABLESPACE_NAME	BLOCK_SIZE	INITIAL_EXTENT	NEXT_EXTENT	MIN_EXTENTS	MAX_EXTENTS	MAX_SIZE	PCT_INCREASE	MIN_EXTLEN	STATUS	CONTENTS	LOGGING	FORCE_LOGGING	EXTENT_MANAGEMENT	ALLOCATION_TYPE	PLUGGED_IN	SEGMENT_SPACE_MANAGEMENT	DEF_TAB_COMPRESSION	RETENTION	BIGFILE	PREDICATE_EVALUATION	ENCRYPTED	DEF_INMEMORY	SHARED	DEF_INDEX_COMPRESSION	LOST_WRITE_PROTECT	CHUNK_TABLESPACE
SYSTEM	8192	65536		1	2147483645	2147483645		65536	ONLINE	PERMANENT	LOGGING	YES	LOCAL	SYSTEM	NO	MANUAL	DISABLED	NOT APPLY	NO	HOST	NO	DISABLED	SHARED	DISABLED	OFF	N
SYSAUX	8192	65536		1	2147483645	2147483645		65536	ONLINE	PERMANENT	LOGGING	YES	LOCAL	SYSTEM	NO	AUTO	DISABLED	NOT APPLY	NO	HOST	NO	DISABLED	SHARED	DISABLED	OFF	N
UNDOTBS1	8192	65536		1	2147483645	2147483645		65536	ONLINE	UNDO	LOGGING	NO	LOCAL	SYSTEM	NO	MANUAL	DISABLED	NOGUARANTEE	NO	HOST	NO	DISABLED	SHARED	DISABLED	OFF	N
TEMP	8192	1048576	1048576	1		2147483645	0	1048576	ONLINE	TEMPORARY	NOLOGGING	NO	LOCAL	UNIFORM	NO	MANUAL	DISABLED	NOT APPLY	NO	HOST	NO	DISABLED	SHARED	DISABLED	OFF	N
USERS	8192	65536		1	2147483645	2147483645		65536	ONLINE	PERMANENT	LOGGING	NO	LOCAL	SYSTEM	NO	AUTO	DISABLED	NOT APPLY	NO	HOST	NO	DISABLED	SHARED	DISABLED	OFF	N
SCOTT2_TEST	8192	65536		1	2147483645	2147483645		65536	ONLINE	PERMANENT	LOGGING	NO	LOCAL	SYSTEM	NO	AUTO	DISABLED	NOT APPLY	NO	HOST	NO	DISABLED	SHARED	DISABLED	OFF	N

ユーザ

ユーザを作成する

検証用のため、デフォルトプロファイルの制約をなくす。パスワード変更などをしないため。

alter profile DEFAULT limit

FAILED_LOGIN_ATTEMPTS unlimited

PASSWORD_LIFE_TIME unlimited

PASSWORD_LOCK_TIME unlimited

PASSWORD_GRACE_TIME unlimited

以下、ユーザ作成。プロファイルは省略→DEFAULTプロファイルが適用される。 DEFAULT TABLESPACEをUSERS以外にすると、ORA-00959と出てしまうようだ。 DEFAULT TABLESPACEを指定しないと、SYSTEMになってしまうらしい。

CREATE USER SCOTT2

IDENTIFIED BY "tiger2"

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMP

ロールを付与する

GRANT connect to SCOTT2;

GRANT UNLIMITED TABLESPACE TO scott2;

GRANT CREATE TABLE TO scott2;

DDL

テーブル作成

CREATE TABLE employee_tbl ( EMP_NUM NUMBER(7, 0), EMP_NAME VARCHAR2(100), JOB VARCHAR2(50), CMT_TEXT VARCHAR2(2000), CONSTRAINT EMPLOYEE_PK1 PRIMARY KEY (EMP_NUM) ) TABLESPACE USERS;

SQL*PLUSの設定

DBMS_OUTPUTによる出力の有効化 SQL*Plus: set serveroutput on

SQL Developer: 表示(V)→DBMS出力(D)→接続のDBMS_OUTPUTを有効化(Ctrl+N)

DML

データ作成

DECLARE

l_job employee_tbl.job%type;

BEGIN

FOR r IN 1..2 LOOP

IF mod(r, 3) = 1 THEN

l_job := 'SALESMAN';

ELSIF mod(r, 3) = 2 THEN

l_job := 'MANAGER';

ELSE

l_job := 'ANALYST';

END IF;

DBMS_OUTPUT.PUT_LINE(r);

INSERT INTO employee_tbl (emp_num, emp_name, job, cmt_text)

values (r, '鈴木　一郎' || r , l_job, 'あいうえおかきくけこさしすせそ');

END LOOP;

END;