注意:此文档本人笔记有点乱,持续优化。
//创建表空间
说明:GMACTS:表空间名,DATAFILE后边是路径,默认1G 超出范围以10M大小增加
CREATE TABLESPACE GMACTS
DATAFILE 'E:/oracle_tablespaces/GMACTS'
SIZE 1024M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
CREATE TABLESPACE TBS_GMAC_INDEX
DATAFILE 'E:/oracle_tablespaces/TBS_GMAC_INDEX'
SIZE 1024M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
//底下的语法修改表空间大小
ALTER TABLESPACE GMACTS ADD DATAFILE
'E:/oracle_tablespaces/GMACTS'
SIZE 1024M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
//删除表空间 如果删除不了就重启oracle 服务
drop tablespace GMACTS including contents and datafiles;
//创建用户、授权
说明:用户名:GMAC_0317,密码:123456
create user GMAC_0317 identified by "123456";
//修改用户默认表空间语法:
ALTER USER 用户名 IDENTIFIED BY 口令 DEFAULT TABLESPACE 表空间;
//例如:
alter user GMAC_0317 default tablespace GMACTS;
//给用户赋权限(dba是管理员,一般前两个就行)
grant connect,resource,dba to GMAC_0317;
//创建逻辑目录 别名data_dir 此目录底下放着导入导出文件及log文件
create directory data_dir as 'D:\tableStruct';
//导入导出注意保持一致
exp/imp导出/导入数据
expdp/impdp导出/导入数据
//导入案例
imp GMAC_0317/123456@orcl file=F:\20210618_GMAC\20210618_GMAC.DMP full=y ignore=y;
impdp GMAC_0317/123456@orcl directory="data_dir" dumpfile="20210618_GMAC.DMP " FULL=y;
//查看表空间使用情况
select
a.a1 表空间名称,
c.c2 类型,
c.c3 区管理,
b.b2/1024/1024 表空间大小M,
(b.b2-a.a2)/1024/1024 已使用M,
substr((b.b2-a.a2)/b.b2*100,1,5) 利用率
from
(select tablespace_name a1,sum(nvl(bytes,0)) a2 from dba_free_space group by tablespace_name) a,
(select tablespace_name b1,sum(bytes) b2 from dba_data_files group by tablespace_name) b,
(select tablespace_name c1,contents c2,extent_management c3 from dba_tablespaces)c
where a.a1=b.b1 and c.c1=b.b1;