-- 작은 용량을 붙이려고 할 때 에러가 발생.
iSQL(sysdba)> ALTER TABLESPACE SYS_TBS_DISK_UNDO
2 ADD DATAFILE 'undo002.dbf' AUTOEXTEND ON NEXT 1M MAXSIZE 100M;
[ERR-11150 : The INITSIZE of the data file exceeds the MAXSIZE of the data file.]
-- 구문 오류 발생 ( MB -> M 로 사용해야 함)
iSQL(sysdba)> ALTER TABLESPACE SYS_TBS_DISK_UNDO
2 ADD DATAFILE 'undo002.dbf' AUTOEXTEND ON NEXT 1M MAXSIZE 100MB;
[ERR-31001 : SQL syntax error
0002 : ADD DATAFILE 'undo002.dbf' AUTOEXTEND ON NEXT 1M MAXSIZE 100MB
^ ^
]
-- Undo data file 추가
iSQL(sysdba)> ALTER TABLESPACE SYS_TBS_DISK_UNDO
2 ADD DATAFILE 'undo002.dbf' AUTOEXTEND ON NEXT 1M MAXSIZE 1G;
Alter success.
iSQL(sysdba)> exit
[jyj@jyj dbs]$ pwd
/home/jyj/altibase/dbs
[jyj@jyj dbs]$ ls -al
합계 3809364
drwxr-xr-x. 2 jyj jyj 4096 2015-08-17 13:01 .
drwxrwxr-x. 19 jyj jyj 4096 2015-08-13 15:14 ..
-rw-r-----. 1 jyj jyj 532480 2015-08-17 12:59 dwfile0.dwf
-rw-r-----. 1 jyj jyj 532480 2015-08-17 13:01 dwfile1.dwf
-rw-r-----. 1 jyj jyj 2146443264 2015-08-17 12:57 system001.dbf
-rw-r-----. 1 jyj jyj 104865792 2015-08-13 18:16 temp001.dbf
-rw-r-----. 1 jyj jyj 1101012992 2015-08-17 12:57 test_db.dbf
-rw-r-----. 1 jyj jyj 10493952 2015-08-13 18:16 test_db_tmp.dbf
-rw-r-----. 1 jyj jyj 268443648 2015-08-17 12:59 undo001.dbf
-rw-r-----. 1 jyj jyj 268443648 2015-08-17 13:01 undo002.dbf
[jyj@jyj dbs]$
iSQL> set linesize 10000;
iSQL> set colsize 50;
iSQL> select * from V$DATAFILES where name like '%undo%';
ID NAME SPACEID OLDEST_LSN_LFGID OLDEST_LSN_FILENO OLDEST_LSN_OFFSET CREATE_LSN_LFGID CREATE_LSN_FILENO CREATE_LSN_OFFSET SM_VERSION NEXTSIZE MAXSIZE INITSIZE CURRSIZE AUTOEXTEND IOCOUNT OPENED MODIFIED STATE MAX_OPEN_FD_COUNT CUR_OPEN_FD_COUNT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0 /home/jyj/altibase/dbs/undo001.dbf 3 0 842 4273633 0 0 2583 84148225 128 262143 32768 32768 1 0 1 0 2 8 1
1 /home/jyj/altibase/dbs/undo002.dbf 3 0 842 4273633 0 842 4523174 84148225 128 131072 32768 32768 1 0 1 0 2 8 1
2 rows selected.
ALTER TABLESPACE SYS_TBS_DISK_UNDO ALTER DATAFILE 'undo002.dbf' AUTOEXTEND OFF;
iSQL> select * from V$DATAFILES where name like '%undo%';
ID NAME SPACEID OLDEST_LSN_LFGID OLDEST_LSN_FILENO OLDEST_LSN_OFFSET CREATE_LSN_LFGID CREATE_LSN_FILENO CREATE_LSN_OFFSET SM_VERSION NEXTSIZE MAXSIZE INITSIZE CURRSIZE AUTOEXTEND IOCOUNT OPENED MODIFIED STATE MAX_OPEN_FD_COUNT CUR_OPEN_FD_COUNT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0 /home/jyj/altibase/dbs/undo001.dbf 3 0 842 4554863 0 0 2583 84148225 128 262143 32768 32768 1 0 1 0 2 8 2
1 /home/jyj/altibase/dbs/undo002.dbf 3 0 842 4554863 0 842 4523174 84148225 0 0 32768 32768 0 0 1 0 2 8 2
2 rows selected.
iSQL>
-- 이미 OFF 되어 있거나 ON 이 되어 있으면 오류 발생.
iSQL> ALTER TABLESPACE SYS_TBS_DISK_UNDO ALTER DATAFILE 'undo002.dbf' AUTOEXTEND OFF;
[ERR-11032 : The attribute of AUTOEXTEND mode has already been set. (FID :1)]
iSQL> ALTER TABLESPACE SYS_TBS_DISK_UNDO ALTER DATAFILE 'undo002.dbf' AUTOEXTEND On;
Alter success.
iSQL> ALTER TABLESPACE SYS_TBS_DISK_UNDO ALTER DATAFILE 'undo002.dbf' AUTOEXTEND On;
[ERR-11032 : The attribute of AUTOEXTEND mode has already been set. (FID :1)]
iSQL>