查询表空间的命令

 select t1.name,t2.name 

         from v$tablespace t1,v$datafile t2

         where t1.ts# = t2.ts#;
         
CREATE TABLESPACE ORM_342_BETA
DATAFILE '/app/oracle/oradata/sysware/ORM_342_BETA.DBF' size 800M         --存储地址 初始大小800M
    autoextend on next 50M maxsize unlimited                   --每次扩展10M，无限制扩展
         EXTENT MANAGEMENT LOCAL autoallocate SEGMENT SPACE MANAGEMENT AUTO;   

CREATE TABLESPACE SYSWARE_ORM_340_UPGRADE
DATAFILE '/app/oracle/oradata/sysware/SYSWARE_ORM_340_UPGRADE.DBF' size 800M      
    autoextend on next 50M maxsize unlimited                  
         EXTENT MANAGEMENT LOCAL autoallocate SEGMENT SPACE MANAGEMENT AUTO;   
1.切换oracle用户：
    su - oracle
2.进入sql 
    sqlplus sys/sysware@sysware as sysdba
3.创建权限目录，用sysdba登陆，在sql窗口执行(或者cmd窗口执行：sqlplus /@sysware（在那个数据库实例中） as sysdba)
    create directory 授权目录名 as '授权目录';   
    grant read,write on directory 授权目录名 to 数据库用户名;
如：
    create directory DPDIROEM20230414 as '/syswareIDP/dpdir/DPDIROEM20230414';   
    grant read,write on directory DPDIROEM20230414 to SYSWARE_ORM_340_UPGRADE;
    
    
    create directory dpdir1201 as '/syswareIDP/dpdir/dpdir1201';   
    grant read,write on directory dpdir1201 to SYSWARE_ORM_MR;

4.导入，在cmd窗口执行
impdp 数据库用户名/数据库密码 directory=授权目录名 remap_tablespace=导出数据库的表空间:表空间 dumpfile=数据文件名称.dmp remap_schema=导出数据库的用户名:数据库用户名 transform=OID:N
如：
impdp IDP_20200612/IDP_20200612@sysware directory=dpdir220200612 remap_tablespace=idp_20170313:IDP_20200612 dumpfile=DATA.DMP remap_schema=idp_20170313:IDP_20200612 transform=OID:N

impdp SYSWARE_ORM_340_UPGRADE/SYSWARE_ORM_340_UPGRADE@sysware directory=DPDIROEM20230414 remap_tablespace=SYSWARE_ORM_342_DEMO:SYSWARE_ORM_340_UPGRADE dumpfile=kmdata_exp.dmp remap_schema=SYSWARE_ORM_342_DEMO:SYSWARE_ORM_340_UPGRADE transform=OID:N

impdp SYSWARE_ORM_343_VERSION/SYSWARE_ORM_343_VERSION@sysware directory=DPDIROEM230704 remap_tablespace=SYSWARE_343_DEMO:SYSWARE_ORM_343_VERSION dumpfile=data.dmp   remap_schema=SYSWARE_343_DEMO:SYSWARE_ORM_343_VERSION transform=OID:N

SYSWARE_343_DEMO

--sqlplus IDP_20200612/IDP_20200612@localhost:1521/sysware
--查看帮助
expdp -help
impdp -help

------------------------------------导出-----：导出需要记录用户名、表空间名，导入需要用到
--  1.创建权限目录，用sysdba登陆，在sql窗口执行(或者cmd窗口执行：sqlplus /@sysware（在那个数据库实例中）as sysdba)/或者PLSQL使用SYSDBA登录SQL窗口中执行
create directory 授权目录名 as '授权目录';   
grant read,write on directory 授权目录名 to 数据库用户名;
如：
create directory dpdir220200612 as 'D:\dpdir\dpdir220200612';   
grant read,write on directory dpdir220200612 to IDP_20200612;

-- 2.导出，在cmd窗口执行
expdp 数据库用户名/数据库密码@orcl directory=授权目录名 dumpfile=数据文件名称.dmp
如：su
expdp IDP_20200612/IDP_20200612@sysware directory=dpdir220200612  dumpfile=data.dmp

全库导出：

如果你需要进行全库备份，并且担心权限问题，可以创建一个专门用于备份的用户，并授予它DATAPUMP_EXP_FULL_DATABASE角色，例如：
 

CREATE USER backup_user IDENTIFIED BY password;
GRANT DATAPUMP_EXP_FULL_DATABASE TO backup_user;
GRANT READ, WRITE ON DIRECTORY dpump_dir TO backup_user;

create directory allback as '/home/dpdir/allback';

GRANT READ, WRITE ON DIRECTORY allback TO backup_user;
expdp backup_user/password DIRECTORY=allback DUMPFILE=full_db.dmp FULL=Y

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options ORA-31626: job does not exist ORA-31633: unable to create master table "BACKUP_USER.SYS_EXPORT_FULL_05" ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95 ORA-06512: at "SYS.KUPV$FT", line 1038 ORA-01950: no privileges on tablespace 'USERS'

-- 先创建一个专门用于备份的表空间（可选）
CREATE TABLESPACE backup_ts DATAFILE '/path/to/backup_ts.dbf' SIZE 100M AUTOEXTEND ON;

-- 更改用户的默认表空间
ALTER USER BACKUP_USER DEFAULT TABLESPACE backup_ts;

-- 授予新表空间的配额
ALTER USER BACKUP_USER QUOTA UNLIMITED ON backup_ts;

重新执行导出：

expdp backup_user/password DIRECTORY=allback DUMPFILE=full_db.dmp FULL=Y