データファイル
データファイルの一覧を確認する。
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 |
COMPRESS_FOR |
DEF_INMEMORY |
DEF_INMEMORY_PRIORITY |
DEF_INMEMORY_DISTRIBUTE |
DEF_INMEMORY_COMPRESSION |
DEF_INMEMORY_DUPLICATE |
SHARED |
DEF_INDEX_COMPRESSION |
INDEX_COMPRESS_FOR |
DEF_CELLMEMORY |
DEF_INMEMORY_SERVICE |
DEF_INMEMORY_SERVICE_NAME |
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 |
COMPRESS_FOR |
DEF_INMEMORY |
DEF_INMEMORY_PRIORITY |
DEF_INMEMORY_DISTRIBUTE |
DEF_INMEMORY_COMPRESSION |
DEF_INMEMORY_DUPLICATE |
SHARED |
DEF_INDEX_COMPRESSION |
INDEX_COMPRESS_FOR |
DEF_CELLMEMORY |
DEF_INMEMORY_SERVICE |
DEF_INMEMORY_SERVICE_NAME |
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;
テーブル作成
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)
データ作成
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;