可传输表空间

淡淡的烟草味﹌ 2022-09-12 04:58 62阅读 0赞

## 测试表空间 ##

> hr
    create tablespace trans_tbs datafile size 100M;
    col tablespace_name format a30
    col file_name format a100
    select tablespace_name, file_name from dba_data_files order by tablespace_name;

## 测试用户 ##

> hr
    create user trans identified by trans default tablespace trans_tbs;
    alter user trans quota unlimited on trans_tbs;
    grant connect to trans;
    grant create table to trans;
    grant select on hr.jobs to trans;

## 测试表 ##

> hr
    conn trans/trans@source
    create table trans_tb tablespace trans_tbs as select * from hr.jobs;
    select count(*) from trans_tb;

## 测试表置为可读模式 ##

> hr
    alter tablespace trans_tbs read only;
    select tablespace_name, status from dba_tablespaces;

## 测试目录对象 ##

> hr
    !mkdir /data/dump
    create directory dump_dir as '/data/dump';
    grant read, write on directory dump_dir to system;
    col directory_name format a20
    col directory_path format a40
    select directory_name, directory_path from dba_directories where directory_name=upper('dump_dir');

## 导出表空间元数据 ##

> expdp system/oracle@source directory=dump_dir dumpfile=trans_tbs.dump transport_tablespaces=trans_tbs logfile=trans_tbs.log

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Zad6YaJ6YWS55qE5bCP55m9_size_20_color_FFFFFF_t_70_g_se_x_16]

## 删除表空间 ##

> hr
    drop tablespace trans_tbs including contents;
    select tablespace_name, status from dba_tablespaces;
    select count(*) from trans_tb;
    --drop tablespace trans_tbs including contents and datafile;

## 导入表空间元数据 ##

> impdp system/oracle@source directory=dump_dir dumpfile=trans_tbs.dump transport_datafiles='/data/BPXTEST/CBB631BCB18D2105E0531CB40A0AD267/datafile/o1_mf_trans_tb_jnm3mn7s_.dbf'

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Zad6YaJ6YWS55qE5bCP55m9_size_20_color_FFFFFF_t_70_g_se_x_16 1]

*  [OMF管理删除表空间默认删除数据文件][OMF]

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Zad6YaJ6YWS55qE5bCP55m9_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/f78f5c2dfccf4109938113d3a1e1431a.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Zad6YaJ6YWS55qE5bCP55m9_size_20_color_FFFFFF_t_70_g_se_x_16 1]: /images/20220828/64d35198899c460f9b651d49e62a8bf0.png
[OMF]: https://blog.csdn.net/hezuijiudexiaobai/article/details/120403103