--表空间使用情况
SELECT DF.TABLESPACE_NAME "表空间名",
TOTALSPACE "总空间M",
FREESPACE "剩余空间M",
ROUND((1 - FREESPACE / TOTALSPACE) * 100, 2) "使用率%"
FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / 1024 / 1024) TOTALSPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) DF,
(SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / 1024 / 1024) FREESPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) FS
WHERE DF.TABLESPACE_NAME = FS.TABLESPACE_NAME;
ALTER TABLESPACE USERS ADD DATAFILE 'D:\app\Administrator\oradata\MESXW2\USERS01.DBF' SIZE 1000M;
--添加数据文件,在一个表空间下
ALTER TABLESPACE USERS ADD DATAFILE
'D:\app\Administrator\oradata\MESXW2\USERS03.DBF'
size 1000M autoextend on ;
--查询文件是否自动递增
SELECT FILE_NAME, AUTOEXTENSIBLE, INCREMENT_BY FROM DBA_DATA_FILES;
--设置文件每次递增以100M
ALTER DATABASE DATAFILE 'D:\app\Administrator\oradata\MESXW2\USERS02.DBF'AUTOEXTEND ON NEXT 100M
5.导入成功后,需要为空表分配表空间,然后用command命令执行下列表:
select 'alter table '|| table_name|| ' allocate extent ; ' from user_tables where NUM_rows =0;
6.空表导出开关【导入成功后必须执行,command窗口执行】
alter system set deferred_segment_creation=false;
--导入
imp ycmes/ycmes@ORCL FILE=D:\LHmes\mes_40.dmp FULL=Y ignore=y