oracle10g_alter table_測試3
SQL>
SQL>
SQL> show user
USER is "TBS_02"
SQL> conn /as sysdba
Connected.
SQL> select username from dba_users;
USERNAME
------------------------------
TBS_02
FS1
ZXY
SCOTT
TEST
AUDIT_TEST
TSMSYS
MDDATA
DIP
MDSYS
ORDSYS
USERNAME
------------------------------
EXFSYS
DMSYS
DBSNMP
WMSYS
CTXSYS
ANONYMOUS
SYSMAN
XDB
ORDPLUGINS
SI_INFORMTN_SCHEMA
OLAPSYS
USERNAME
------------------------------
SYS
SYSTEM
OUTLN
MGMT_VIEW
26 rows selected.
SQL> conn test/system
Connected.
SQL> desc user_sys_privs;
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME VARCHAR2(30)
PRIVILEGE NOT NULL VARCHAR2(40)
ADMIN_OPTION VARCHAR2(3)
SQL> select username,privilege from user_sys_privs;--檢視給這個使用者授權的所有許可權
USERNAME PRIVILEGE
------------------------------ ----------------------------------------
TEST DROP TABLESPACE
TEST CREATE TABLESPACE
TEST UNLIMITED TABLESPACE
SQL> conn /as sysdba
Connected.
SQL> conn test/system
Connected.
SQL> alter tablespace test add datafile '/oracle/db/test02.dbf' size 20m;--test使用者沒有alter tablespace許可權喲
alter tablespace test add datafile '/oracle/db/test02.dbf' size 20m
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> conn /as sysdba
Connected.
SQL> grant alter tablespace to test;--給test使用者授權
Grant succeeded.
SQL> conn test/system
Connected.
SQL> select username,privilege from user_sys_privs;
USERNAME PRIVILEGE
------------------------------ ----------------------------------------
TEST DROP TABLESPACE
TEST CREATE TABLESPACE
TEST ALTER TABLESPACE
TEST UNLIMITED TABLESPACE
SQL> alter tablespace test add datafile '/oracle/db/test02.dbf' size 20m;--新增資料檔案(給表空間test)
Tablespace altered.
SQL> conn /as sysdba
Connected.
SQL> revoke alter tablespace from test;--回收許可權
Revoke succeeded.
SQL> grant manage tablespace to test;--這個manage tablespace許可權只能使表空間上下線,進行表空間備份
Grant succeeded.
SQL> conn test/system
Connected.
SQL> select username,privilege from user_sys_privs;
USERNAME PRIVILEGE
------------------------------ ----------------------------------------
TEST DROP TABLESPACE
TEST MANAGE TABLESPACE
TEST CREATE TABLESPACE
TEST UNLIMITED TABLESPACE
SQL> alter tablespace test offline;
Tablespace altered.
SQL> alter tablespace test online;
Tablespace altered.
SQL> alter tablespace add datafile '/oracle/db/test03.dbf' size 10m;
alter tablespace add datafile '/oracle/db/test03.dbf' size 10m
*
ERROR at line 1:
ORA-02140: invalid tablespace name
SQL> alter tablespace test add datafile '/oracle/db/test03.dbf' size 10m;
alter tablespace test add datafile '/oracle/db/test03.dbf' size 10m
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> alter tablespace test read only;
-- 使表空間只讀,這種不能進行事務,可以檢視v$transaction;另如果你想從read write切到
---read only(在已有事務情況下,執行這個語句就會hang在哪兒)
Tablespace altered.
SQL> alter tablespace test read write;
Tablespace altered.
SQL> conn test/system
Connected.
SQL> alter tablespace test add datafile '/oracle/db/test04.dbf' size 10m;
Tablespace altered.
SQL> alter tablespace test drop datafile /oracle/db/test04.dbf' size 10m;
alter tablespace test drop datafile /oracle/db/test04.dbf' size 10m
*
ERROR at line 1:
ORA-02236: invalid file name
SQL> alter tablespace test drop datafile '/oracle/db/test04.dbf';
Tablespace altered.
SQL> conn /as sysdba
Connected.
SQL> alter tablespace tbs_temp_02 add tempfile '/oracle/db/temp03.dbf' size 10m;--新增臨時表空間資料檔案,記得用tempfile
Tablespace altered.
SQL> alter tablespace tbs_temp_02 drop tempfile '/oracle/db/temp03.dbf';--刪除臨時表空間的臨時檔案
Tablespace altered.
SQL> alter tablespace tbs_temp_02 rename to tbs_temp;---重新命名臨時表空間名字
Tablespace altered.
SQL> select file_name,file_id,tablespace_name from dba_temp_files where tablespace_name='TBS_TEMP';
FILE_NAME FILE_ID TABLESPACE
------------------------------ ---------- ----------
/oracle/product/10.2.0/db_1/db 5 TBS_TEMP
s/temp02.dbf
SQL> alter tablespace test offline;
Tablespace altered.
SQL> alter tablespace test online;
Tablespace altered.
SQL> alter tablespace test read only;
Tablespace altered.
SQL> alter tablespace test add datafile '/oracle/newtest.dbf' size 10m;
alter tablespace test add datafile '/oracle/newtest.dbf' size 10m
*
ERROR at line 1:
ORA-01641: tablespace 'TEST' is not online - cannot add data file
SQL> alter tablespace test read write;
Tablespace altered.
SQL> alter tablespace test add datafile '/oracle/newtest.dbf' size 10m;
Tablespace altered.
SQL> alter tablespace test offline;
Tablespace altered.
SQL> host
bash-3.00$ mv /oracle/newtest.dbf /oracle/newloc.dbf
bash-3.00$ ls -l /oracle/new*
-rw-r----- 1 ora10g oinstall 10493952 9月 13 01:01 /oracle/newloc.dbf
/oracle/newdir:
total 0
bash-3.00$ exit
exit
SQL> alter tablespace test rename file '/oracle/newtest.dbf' to '/oracle/newloc.dbf';
alter tablespace test rename file '/oracle/newtest.dbf' to '/oracle/newloc.dbf'
*
ERROR at line 1:
ORA-02152: Invalid ALTER TABLESPACE ... RENAME option
SQL> alter tablespace test rename datafile '/oracle/newtest.dbf' to '/oracle/newloc.dbf';
Tablespace altered.
SQL> alter tablespace test datafile offline;
Tablespace altered.
SQL> alter tablespace test online;
Tablespace altered.
SQL> alter tablespace test datafile offline;---datafile offline和online
Tablespace altered.
SQL> desc dba_data_files;
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS VARCHAR2(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
ONLINE_STATUS VARCHAR2(7)
SQL> select file_name,tablespace_name,online_status from dba_data_files where tablespace_name='TEST';
FILE_NAME TABLESPACE ONLINE_ ---online_status為recover,你要recover tablespace或recover datafile
------------------------------ ---------- -------
/oracle/db/ORA10G/datafile/o1_ TEST RECOVER
mf_test_5bphyzjl_.dbf
/oracle/db/test02.dbf TEST RECOVER
/oracle/newloc.dbf TEST RECOVER
SQL> alter tablespace test datafile online;
alter tablespace test datafile online
*
ERROR at line 1:
ORA-01113: file 14 needs media recovery
ORA-01110: data file 14: '/oracle/newloc.dbf'
SQL> recover tablespace test;
Media recovery complete.
SQL> select file_name,tablespace_name,online_status from dba_data_files where tablespace_name='TEST';
FILE_NAME TABLESPACE ONLINE_
------------------------------ ---------- -------
/oracle/db/ORA10G/datafile/o1_ TEST OFFLINE
mf_test_5bphyzjl_.dbf
/oracle/db/test02.dbf TEST OFFLINE
/oracle/newloc.dbf TEST OFFLINE
SQL> alter tablespace test datafile online;
Tablespace altered.
SQL> alter tablespace test datafile online;
Tablespace altered.
SQL> alter tablespace test online;
Tablespace altered.
SQL> alter tablespace test force logging;
--令表空間強制記日誌,用於flashback query
---和flashback transaction(說白了就會一堆的日誌會寫在oracle中)
Tablespace altered.
SQL> select tablespace_name,force_logging from dba_tablespaces where tablespace_name='TEST';
TABLESPACE FOR
---------- ---
TEST YES
SQL> alter tablespace test no force logging;--關閉強制日誌
Tablespace altered.
SQL> select tablespace_name,force_logging from dba_tablespaces where tablespace_name='TEST';
TABLESPACE FOR
---------- ---
TEST NO
SQL> alter tablespace test offline normal;--normal是預設,會自動把sga中的資料flush到資料檔案中
Tablespace altered.
SQL> alter tablespace test online;
Tablespace altered.
SQL> alter tablespace test offline temporary;
--會執行一個checkpoint,但不確保把相關資料寫入資料檔案,online時可能需要介質恢復
Tablespace altered.
SQL> alter tablespace test online;
alte
Tablespace altered.
SQL> alter tablespace test offline immediate;---這個在online必須要介質恢復
Tablespace altered.
SQL> set long 99999
SQL> select dbms_metadata.get_ddl('TABLESPACE','TEST') from dual;
DBMS_METADATA.GET_DDL('TABLESPACE','TEST')
--------------------------------------------------------------------------------
CREATE TABLESPACE "TEST" DATAFILE
'/oracle/db/ORA10G/datafile/o1_mf_test_5bphyzjl_.dbf' SIZE 104857600,
'/oracle/db/test02.dbf' SIZE 20971520,
'/oracle/newloc.dbf' SIZE 10485760
LOGGING OFFLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
SQL> select file_name,tablespace_name,autoextensible from dba_data_files where tablespace_name='TEST';
FILE_NAME TABLESPACE AUT
------------------------------ ---------- ---
/oracle/db/ORA10G/datafile/o1_ TEST
mf_test_5bphyzjl_.dbf
/oracle/db/test02.dbf TEST
/oracle/newloc.dbf TEST
SQL> alter tablespace test autoextend on;--autoextend用於大檔案表空間和字典管理表空間
alter tablespace test autoextend on
*
ERROR at line 1:
ORA-32773: operation not supported for smallfile tablespace TEST
SQL> alter database datafile '/oracle/newloc.dbf' autoextend on;
alter database datafile '/oracle/newloc.dbf' autoextend on
*
ERROR at line 1:
ORA-00376: file 14 cannot be read at this time
ORA-01110: data file 14: '/oracle/newloc.dbf'
SQL> alter tablespace test online;
alter tablespace test online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/oracle/db/ORA10G/datafile/o1_mf_test_5bphyzjl_.dbf'
SQL> recover tablespace test;
Media recovery complete.
SQL> alter tablespace test online;
Tablespace altered.
SQL> alter database datafile '/oracle/newloc.dbf' autoextend on;
Database altered.
SQL> select file_name,tablespace_name,autoextensible from dba_data_files where tablespace_name='TEST';
FILE_NAME TABLESPACE AUT
------------------------------ ---------- ---
/oracle/db/ORA10G/datafile/o1_ TEST NO
mf_test_5bphyzjl_.dbf
/oracle/db/test02.dbf TEST NO
/oracle/newloc.dbf TEST YES
SQL> alter database datafile /oracle/newloc.dbf' autoextend off;
alter database datafile /oracle/newloc.dbf' autoextend off
*
ERROR at line 1:
ORA-02236: invalid file name
SQL> alter database datafile '/oracle/newloc.dbf' autoextend off;--利用alter database使資料檔案擴充套件
Database altered.
SQL> alter tablespace test maxsize unlimited;
alter tablespace test maxsize unlimited
*
ERROR at line 1:
ORA-02142: missing or invalid ALTER TABLESPACE option
SQL> alter database datafile '/oracle/newloc.dbf' autoextend on maxsize unlimited;--最大大小無限,開自動擴充套件
Database altered.
SQL> alter tablespace test guarantee;--guarantee和noguarantee用於撤消表空間
alter tablespace test guarantee
*
ERROR at line 1:
ORA-02142: missing or invalid ALTER TABLESPACE option
SQL> alter tablespace test resize 300m;--resize用於大檔案表空間
alter tablespace test resize 300m
*
ERROR at line 1:
ORA-32773: operation not supported for smallfile tablespace TEST
SQL> create bigfile tablespace bigone datafile '/oracle/db/bigone01' size 10m;--對比測試建立大檔案表空間
Tablespace created.
SQL> alter tablespace bigone resize 20m;
Tablespace altered.
Tablespace altered.
SQL> alter tablespace test read only;
Tablespace altered.
SQL> alter tablespace test begin backup;
alter tablespace test begin backup
*
ERROR at line 1:
ORA-01642: begin backup not needed for read only tablespace 'TEST'
SQL> alter tablespace test read write;
SQL> alter tablespace test begin backup;
Tablespace altered.
SQL> alter tablespace test end backup;
Tablespace altered.
SQL> alter tablespace test begin backup;
Tablespace altered.
SQL> shutdown immediate;
ORA-01149: cannot shutdown - file 5 has online backup set
ORA-01110: data file 5: '/oracle/db/ORA10G/datafile/o1_mf_test_5bphyzjl_.dbf'
SQL> alter tablespace test offline;
alter tablespace test offline
*
ERROR at line 1:
ORA-01150: cannot prevent writes - file 5 has online backup set
ORA-01110: data file 5: '/oracle/db/ORA10G/datafile/o1_mf_test_5bphyzjl_.dbf'
SQL> alter tablespace test end backup;
Tablespace altered.
SQL> alter tablespace test offline;
Tablespace altered.
SQL> drop tablespace test including contents and datafiles;
Tablespace dropped.
SQL> alter tablespace trans read write;
Tablespace altered.
SQL> alter tablespace trans read only;
Tablespace altered.
SQL> alter tablespace trans drop datafile '/oracle/db/trans.dbf';
alter tablespace trans drop datafile '/oracle/db/trans.dbf'
*
ERROR at line 1:
ORA-03261: the tablespace TRANS has only one file
SQL> select count(*) from v$transaction;
COUNT(*)
----------
0
SQL> /
COUNT(*)
----------
1
SQL> alter tablespace fs1 read only;
Tablespace altered.
SQL> alter tablespace fs1 read write;
Tablespace altered.
SQL> select count(*) from v$transaction;
COUNT(*)
----------
1
SQL> alter tablespace fs1 read only;
^Calter tablespace fs1 read only
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
SQL> select count(*) from v$transaction;
COUNT(*)
----------
1
SQL> /
COUNT(*)
----------
0
SQL> alter tablespace fs1 read write;
alter tablespace fs1 read write
*
ERROR at line 1:
ORA-01646: tablespace 'FS1' is not read only - cannot make read write
---測試遷移表空間的資料檔案,
SQL> create tablespace newtbs datafile '/oracle/db/newtbs01.dbf' size 10m;--建表空間
Tablespace created.
SQL> alter tablespace newtbs offline normal;--遷移前,離線
Tablespace altered.
SQL> host mv /oracle/db/newtbs01.dbf /oracle/newtbs01.dbf--os級mv
SQL> alter tablespace newtbs rename datafile '/oracle/db/newtbs01.dbf' to '/oracle/newtbs01.dbf';--資料庫遷移,要更改控制檔案和資料檔案的標頭檔案資訊
Tablespace altered.
SQL> alter tablespace newtbs online;
Tablespace altered.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-614509/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle10g_alter table shrink space_compact_cascade回收空間測試(一)Oracle
- 測試3
- 小程式自動化測試--測試3
- OpenSSL測試-SM3
- 一、測試基礎（3）
- 測試通用技術3
- 測試測試測試測試測試測試
- MySQL 聯合索引測試3MySql索引
- 實驗3：軟體測試
- 實驗3——軟體測試
- 3月27課堂測試
- 【星雲測試】開發者測試（3）-採用精準測試工具對springcloud微服務應用進行穿透測試SpringGCCloud微服務穿透
- 測試你的前端程式碼 – part3（端到端測試）前端
- 控制元件測試功能點3控制元件
- 網速測試利器-iperf3
- MemSQL初體驗 - (3)效能測試SQL
- 架構之路（3） ：單元測試架構
- 軟體測試要學什麼（3）軟體測試流程詳解
- 開發者測試（3）-採用精準測試工具對springcloud微服務應用進行穿透測試SpringGCCloud微服務穿透
- 軟體測試經典面試題（3）面試題
- 基準測試：HTTP/3 有多快？ - requestmetricsHTTP
- 求助:ejb3 的local介面測試
- LoadRunner測試WebService的3種方式Web
- 3 個主流 PHP 框架的效能測試PHP框架
- Android單元測試（3）：JUnit 的使用Android
- oracle10g_rman_語法測試_3Oracle
- 效能測試過程中最佳化-3：
- App測試、Web測試和介面測試一般測試流程APPWeb
- 軟體安全測試擔心效果不好?這3個安全測試工具你用了嗎?
- 介面測試測試流程
- 全鏈路壓測(3)：技術改造和測試驗證
- 《移動網頁設計與開發HTML5+CSS3+JavaScript》——1.7 測試，再測試，進行更多的測試網頁HTMLCSSS3JavaScript
- 紅米3S續航測試評測 紅米3S續航怎麼樣
- Flutter 學習之路 - 測試（單元測試，Widget 測試，整合測試）Flutter
- 介面測試，負載測試，併發測試，壓力測試區別負載
- 黑盒測試、白盒測試、單元測試、整合測試、系統測試、驗收測試的區別與聯絡...
- 測試CMS同步測試CMS同步測試CMS同步
- （一）效能測試（壓力測試、負載測試）負載