select file#,status,enabled from v$datafile; FILE# STATUS &nbs..."/>
您好,登錄后才能下訂單哦!
表空間的四種狀態(tài):online,offline,read write和ready only
SQL> select file#,status,enabled from v$datafile;
FILE# STATUS ENABLED
-------- --------- -------------
1 SYSTEM READ WRITE
2 ONLINE READ WRITE
3 ONLINE READ WRITE
4 ONLINE READ WRITE
5 ONLINE READ WRITE
users表空間狀態(tài)改變:
SQL>alter tablespace users offline;
SQL>alter tablespace users online;
SQL>alter tablespace users read write;
SQL>alter tablespace users read only;
創(chuàng)建表空間:
SQL>create tablespace tb01 datafile '/u01/oracle/oradata/orcl/tb01.dbf' size 10m autoextent on;
表空間重命名:
SQL>alter tablespace tb01 rename to tb02;
表空間自動(dòng)擴(kuò)張,AUTOEXTENSIBLE自動(dòng)擴(kuò)張字段:
SQL> select tablespace_name,file_name,autoextensible from dba_data_files;
SQL> select tablespace_name,file_name,autoextensible from dba_data_files;
TABLESPACE_NAME FILE_NAME AUTOEXTENSIBLE
------------------------ ------------------------- --------------
USERS /home/oracle/oradata/orcl/users01.dbf YES
SYSAUX /home/oracle/oradata/orcl/sysaux01.dbf YES
UNDOTBS1 /home/oracle/oradata/orcl/undotbs01.dbf YES
SYSTEM /home/oracle/oradata/orcl/system01.dbf YES
EXAMPLE /home/oracle/oradata/orcl/example01.dbf YES
SQL> select file#,name from v$datafile;
FILE# NAME
------- ----------------------------------------
1 /home/oracle/oradata/orcl/system01.dbf
2 /home/oracle/oradata/orcl/undotbs01.dbf
3 /home/oracle/oradata/orcl/sysaux01.dbf
4 /home/oracle/oradata/orcl/users01.dbf
5 /home/oracle/oradata/orcl/example01.dbf
SQL>alter database datafile 6 resize autoextend on;
SQL>alter tablespace tb01 add datafile '/u01/oracle/oradata/orcl/tb01.dbf' size 30m autoextent on;
表空間文件重命名:
前提是數(shù)據(jù)庫是歸檔模式,數(shù)據(jù)庫處于mount或者open模式,open模式的時(shí)候,表空間要offline,最后把表空間online:
SQL>alter tablespace tb01 offline;
SQL>ho cp /u01/oracle/oradata/orcl/tb01.dbf /u01/oracle/oradata/orcl/tb02.dbf
SQL>alter tablespace rename datafile '/u01/oracle/oradata/orcl/tb01.dbf' to '/u01/oracle/oradata/orcl/tb02.dbf';
SQL>alter tablespace tb01 online;
需要介質(zhì)恢復(fù)的話:
SQL>recover tablespace tb01;
刪除表空間:
SQL>drop tablespace tb01 including contents and datafiles;
查看表空間:
SQL> select tablespace_name from dba_data_files;
TABLESPACE_NAME
----------------
USERS
SYSAUX
UNDOTBS1
SYSTEM
EXAMPLE
SQL> select name from v$tablespace;
NAME
----------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
EXAMPLE
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。