创建用户表空间以及导入数据总结
创建新的表空间:NEWCLAIMSPACE
CREATE TABLESPACE NEWCLAIMSPACE
LOGGING
DATAFILE 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\REPORTDB\TEST_DATA01.DBF'
SIZE 32M
AUTOEXTEND ON
NEXT 32M MAXSIZE 4092M
EXTENT MANAGEMENT LOCAL;
创建用户NEWCLAIM
CREATE USER NEWCLAIM IDENTIFIED BY NEWCLAIM
DEFAULT TABLESPACE NEWCLAIMSPACE
赋予用户权限:(DBA权限)
GRANT CONNECT,RESOURCE TO NEWCLAIM ;
GRANT CREATE USER,DROP USER,ALTER USER ,CREATE ANY VIEW ,
DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE,
DBA,CONNECT,RESOURCE,CREATE SESSION TO NEWCLAIM ;
扩大表空间:
ALTER DATABASE DATAFILE 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\REPORTDB\TEST_DATA01.DBF' RESIZE 4092M;
数据的导入(dmp格式文件):
IMP NEWCLAIM/NEWCLAIM@REPORTDB FULL=Y FILE=E:\NEWNEWCLAIM\YWUSER.DMP FROMUSER=YWUSER TOUSER=NEWCLAIM IGNORE=Y
查看默认表空间:
查看各表的表空间:
各表空间使用情况:
select tablespace_name,bytes/1024/1024 file_size_mb,file_name from dba_data_files
select * from dba_tables s where 1 = 1 and owner = 'STAT'
ALTER TABLESPACE STAT ADD DATAFILE 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\REPORTDB\STAT02.DBF' SIZE 5120M;
select file_id,file_name,tablespace_name,autoextensible,increment_by from dba_data_files order by file_id desc;