oracle10 sys 表空间_导入数据库时相关表空间的操作及删除表操作

表空间相关操作

创建表空间

create tablespace httbs_mesprd datafile 'E:appAdministratororadatahzvidmprdhttbs_mesprd_1.DBF'

size 2G

autoextend on

next 100M maxsize 4G

extent management local;

给表空间增加数据文件

select file_name,tablespace_name from dba_data_files;

alter tablespace httbs_mesprd add datafile 'E:appAdministratororadatahzvidmprdhttbs_mesprd_2.DBF'

size 100m

autoextend on

next 100M maxsize 4G;

select file_name,tablespace_name from dba_data_files;

alter tablespace httbs_mesprd add datafile 'E:appAdministratororadatahzvidmprdhttbs_mesprd_3.DBF'

size 100m

autoextend on

next 100M ;

删除表空间

drop tablespace httbs_mesprd including contents and datafiles;

指定表空间创建用户

create user MESPRD identified by MESPRD

default tablespace HTTBS_MESPRD ;

授权

grant connect,resource to MESPRD；

查看并创建逻辑目录

create or replace directory back_dmp as '/u01/backup';

select * from dba_directories;

赋予导出用户的逻辑目录操作权限

grant read,write on directory back_dmp to MESPRD;

截断历史表

手动导出(表)

exp

/u01/backup/sysaud.dmp

t

yes

yes

sys.aud$

查看表的数据量

select owner,table_name,SAMPLE_SIZE from dba_tables where owner='SYS' and table_name='AUD$';

查看表大小

select SEGMENT_NAME,TABLESPACE_NAME,sum(BYTES/1024/1024)||'M' from USER_extents where SEGMENT_TYPE='TABLE'

group by SEGMENT_NAME,TABLESPACE_NAME

截短表

TRUNCATE TABLE SYS.AUD$；