SQL>CREATETABLESPACE tsoa DATAFILE '/dm8/dmdbms/data/DAMENG/tsoa01.dbf' SIZE 32,'/dm8/dmdbms/data/DAMENG/tsoa02.dbf' SIZE 32;
executed successfully
used time: 32.685(ms).Execute id is503.SQL>SQL>select name,FILE_NUM,STATUS$ from v$tablespacewhere name='TSOA';
LINEID NAME FILE_NUM STATUS$
---------- ---- ----------- -----------1 TSOA 20
used time: 0.714(ms).Execute id is505.SQL>SQL>select TABLESPACE_NAME,FILE_NAME,round(BYTES/1024/1024) size_MB,BLOCKS,AUTOEXTENSIBLE from dba_data_files where TABLESPACE_NAME='TSOA';
LINEID TABLESPACE_NAME FILE_NAME SIZE_MB BLOCKS AUTOEXTENSIBLE
---------- --------------- ----------------------------- ------- -------------------- --------------1 TSOA /dm8/dmdbms/data/DAMENG/tsoa01.dbf 324096 YES
2 TSOA /dm8/dmdbms/data/DAMENG/tsoa02.dbf 324096 YES
used time: 14.854(ms).Execute id is507.SQL>
重命名表空间
altertablespace tsoa to tsoa2;
无论表空间是否有数据,表空间都可以重新命名
SQL>createtable t01 (id int) storage(on TSOA);
executed successfully
used time: 5.752(ms).Execute id is511.SQL>SQL>insertinto t01 values(100);
affect rows1
used time: 0.891(ms).Execute id is516.SQL>commit;
executed successfully
used time: 1.303(ms).Execute id is517.SQL>SQL>SQL>select OWNER,TABLE_NAME,TABLESPACE_NAME,PARTITIONED from dba_tables where TABLE_NAME='T01';
LINEID OWNER TABLE_NAME TABLESPACE_NAME PARTITIONED
---------- ------ ---------- --------------- -----------1 SYSDBA T01 TSOA NO
used time: 74.828(ms).Execute id is513.SQL>SQL>SQL>SQL>altertablespace tsoa renameto tsoa2;
executed successfully
used time: 5.753(ms).Execute id is514.SQL>select OWNER,TABLE_NAME,TABLESPACE_NAME,PARTITIONED from dba_tables where TABLE_NAME='T01';
LINEID OWNER TABLE_NAME TABLESPACE_NAME PARTITIONED
---------- ------ ---------- --------------- -----------1 SYSDBA T01 TSOA2 NO
used time: 6.234(ms).Execute id is515.SQL>
创建表和插入数据到表空间
SQL>createtable t01 (id int) storage(on TSOA);
executed successfully
used time: 5.752(ms).Execute id is511.SQL>SQL>insertinto t01 values(100);
affect rows1
used time: 0.891(ms).Execute id is516.SQL>commit;
executed successfully
used time: 1.303(ms).Execute id is517.SQL>SQL>SQL>select OWNER,TABLE_NAME,TABLESPACE_NAME,PARTITIONED from dba_tables where TABLE_NAME='T01';
LINEID OWNER TABLE_NAME TABLESPACE_NAME PARTITIONED
---------- ------ ---------- --------------- -----------1 SYSDBA T01 TSOA NO
used time: 74.828(ms).Execute id is513.SQL>SQL>
删除表空间
能被删除的表空间,必须没有任何数据;
SQL>dropTABLESPACE tsoa2;dropTABLESPACE tsoa2;[-3412]:Try todrop used tablespace.
used time: 4.599(ms).Execute id is0.SQL>select*from t01;
LINEID ID
---------- -----------1100
used time: 0.466(ms).Execute id is539.SQL>SQL>droptable t01;
executed successfully
used time: 18.854(ms).Execute id is540.SQL>SQL>dropTABLESPACE tsoa2;
executed successfully
used time: 101.793(ms).Execute id is541.SQL>select*from t01;select*from t01;[-2106]:Error in line: 1
Invalid tableorview name [T01].
used time: 0.568(ms).Execute id is0.SQL>
添加数据文件
SQL>altertablespace tsoa2 add DATAFILE '/dm8/dmdbms/data/DAMENG/tsoa03.dbf' SIZE 32;
executed successfully
used time: 9.120(ms).Execute id is520.SQL>select TABLESPACE_NAME,FILE_NAME,round(BYTES/1024/1024) size_MB,BLOCKS,AUTOEXTENSIBLE from dba_data_files where TABLESPACE_NAME='TSOA2';
LINEID TABLESPACE_NAME FILE_NAME SIZE_MB BLOCKS AUTOEXTENSIBLE
---------- --------------- ----------------------------- ------- -------------------- --------------1 TSOA2 /dm8/dmdbms/data/DAMENG/tsoa01.dbf 324096 YES
2 TSOA2 /dm8/dmdbms/data/DAMENG/tsoa03.dbf 324096 YES
3 TSOA2 /dm8/dmdbms/data/DAMENG/tsoa02.dbf 324096 YES
used time: 2.152(ms).Execute id is521.SQL>
扩展数据文件
数据文件可以变大,达梦数据库无法通过resize或者drop datafile来缩小表空间,只能通过迁移,导入导出来实现。
SQL>ALTERTABLESPACE tsoa2 RESIZE DATAFILE '/dm8/dmdbms/data/DAMENG/tsoa03.dbf'TO64;
executed successfully
used time: 4.897(ms).Execute id is524.SQL>select TABLESPACE_NAME,FILE_NAME,round(BYTES/1024/1024) size_MB,BLOCKS,AUTOEXTENSIBLE from dba_data_files where TABLESPACE_NAME='TSOA2';
LINEID TABLESPACE_NAME FILE_NAME SIZE_MB BLOCKS AUTOEXTENSIBLE
---------- --------------- ----------------------------- ------- -------------------- --------------1 TSOA2 /dm8/dmdbms/data/DAMENG/tsoa01.dbf 324096 YES
2 TSOA2 /dm8/dmdbms/data/DAMENG/tsoa03.dbf 648192 YES
3 TSOA2 /dm8/dmdbms/data/DAMENG/tsoa02.dbf 324096 YES
used time: 2.181(ms).Execute id is525.SQL>ALTERTABLESPACE tsoa2 RESIZE DATAFILE '/dm8/dmdbms/data/DAMENG/tsoa03.dbf'TO32;ALTERTABLESPACE tsoa2 RESIZE DATAFILE '/dm8/dmdbms/data/DAMENG/tsoa03.dbf'TO32;[-2410]:Datafile[/dm8/dmdbms/data/DAMENG/tsoa03.dbf] size is invalid.
used time: 3.145(ms).Execute id is0.SQL>
重命名数据文件
SQL>select TABLESPACE_NAME,FILE_NAME,round(BYTES/1024/1024) size_MB,BLOCKS,AUTOEXTENSIBLE from dba_data_files where TABLESPACE_NAME='TSOA2';
LINEID TABLESPACE_NAME FILE_NAME SIZE_MB BLOCKS AUTOEXTENSIBLE
---------- --------------- ----------------------------- ------- -------------------- --------------1 TSOA2 /dm8/dmdbms/data/DAMENG/tsoa01.dbf 324096 YES
2 TSOA2 /dm8/dmdbms/data/DAMENG/tsoa03.dbf 648192 YES
3 TSOA2 /dm8/dmdbms/data/DAMENG/tsoa02.dbf 324096 YES
used time: 1.386(ms).Execute id is532.SQL>SQL>ALTERTABLESPACE tsoa2 offline;
executed successfully
used time: 95.018(ms).Execute id is527.SQL>SQL>select*from t01;select*from t01;[-3408]:tablespace[TSOA2]is offline.
used time: 0.823(ms).Execute id is0.SQL>SQL>SQL>ALTERTABLESPACE tsoa2 rename DATAFILE '/dm8/dmdbms/data/DAMENG/tsoa03.dbf'to'/dm8/dmdbms/data/DAMENG/tsoa04.dbf';
executed successfully
used time: 255.943(ms).Execute id is529.SQL>ALTERTABLESPACE tsoa2 online;
executed successfully
used time: 5.693(ms).Execute id is530.SQL>select*from t01;
LINEID ID
---------- -----------1100
used time: 0.746(ms).Execute id is531.SQL>select TABLESPACE_NAME,FILE_NAME,round(BYTES/1024/1024) size_MB,BLOCKS,AUTOEXTENSIBLE from dba_data_files where TABLESPACE_NAME='TSOA2';
LINEID TABLESPACE_NAME FILE_NAME SIZE_MB BLOCKS AUTOEXTENSIBLE
---------- --------------- ----------------------------- ------- -------------------- --------------1 TSOA2 /dm8/dmdbms/data/DAMENG/tsoa01.dbf 324096 YES
2 TSOA2 /dm8/dmdbms/data/DAMENG/tsoa04.dbf 648192 YES
3 TSOA2 /dm8/dmdbms/data/DAMENG/tsoa02.dbf 324096 YES
used time: 1.386(ms).Execute id is532.SQL>
更换数据文件路径
SQL>select TABLESPACE_NAME,FILE_NAME,round(BYTES/1024/1024) size_MB,BLOCKS,AUTOEXTENSIBLE from dba_data_files where TABLESPACE_NAME='TSOA2';
LINEID TABLESPACE_NAME FILE_NAME SIZE_MB BLOCKS AUTOEXTENSIBLE
---------- --------------- ----------------------------- ------- -------------------- --------------1 TSOA2 /dm8/dmdbms/data/DAMENG/tsoa01.dbf 324096 YES
2 TSOA2 /dm8/dmdbms/data/DAMENG/tsoa04.dbf 648192 YES
3 TSOA2 /dm8/dmdbms/data/DAMENG/tsoa02.dbf 324096 YES
used time: 1.386(ms).Execute id is532.SQL>SQL>ALTERTABLESPACE tsoa2 offline;
executed successfully
used time: 91.852(ms).Execute id is533.SQL>SQL>ALTERTABLESPACE tsoa2 rename DATAFILE '/dm8/dmdbms/data/DAMENG/tsoa04.dbf'to'/dm8/dmdbms/DAMENG/tsoa05.dbf';
executed successfully
used time: 87.344(ms).Execute id is534.SQL>select TABLESPACE_NAME,FILE_NAME,round(BYTES/1024/1024) size_MB,BLOCKS,AUTOEXTENSIBLE from dba_data_files where TABLESPACE_NAME='TSOA2';
LINEID TABLESPACE_NAME FILE_NAME SIZE_MB BLOCKS AUTOEXTENSIBLE
---------- --------------- ---------------------------------- ------- -------------------- --------------1 TSOA2 /dm8/dmdbms/data/DAMENG/tsoa01.dbf 324096 YES
2 TSOA2 /dm8/dmdbms/DAMENG/tsoa05.dbf 648192 YES
3 TSOA2 /dm8/dmdbms/data/DAMENG/tsoa02.dbf 324096 YES
used time: 29.460(ms).Execute id is535.SQL>ALTERTABLESPACE tsoa2 online;
executed successfully
used time: 9.299(ms).Execute id is536.SQL>select*from t01;
LINEID ID
---------- -----------1100
used time: 0.563(ms).Execute id is537.SQL>
删除数据文件
达梦数据库无法通过resize或者drop datafile来缩小表空间,只能通过迁移,导入导出来实现。
SQL>SQL>SQL>ALTERTABLESPACE tsoa2 drop DATAFILE '/dm8/dmdbms/data/DAMENG/tsoa01.dbf';ALTERTABLESPACE tsoa2 drop DATAFILE '/dm8/dmdbms/data/DAMENG/tsoa01.dbf';ALTERTABLESPACE tsoa2 drop DATAFILE '/dm8/dmdbms/data/DAMENG/tsoa01.dbf';*
line 1,column47, nearby [drop] has error[-2007]:
Syntax error.
used time: 0.216(ms).Execute id is0.SQL>