Differences between revisions 17 and 18
⇤ ← Revision 17 as of 2016-11-04 05:57:26 →
Size: 6845
Editor: localhost
Comment:
← Revision 18 as of 2016-11-04 06:20:14 → ⇥
Size: 8202
Editor: localhost
Comment:
Deletions are marked like this. Additions are marked like this.
Line 14: Line 14:
现有环境
{{{
SQL> select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
from
(select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
from dba_free_space group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as tbs_size
from dba_data_files group by tablespace_name
UNION
select tablespace_name, sum(bytes)/1024/1024 tbs_size
from dba_temp_files
group by tablespace_name ) b
where a.tablespace_name(+)=b.tablespace_name;

TABLESPACE_NAME SIZEMB FREEMB
------------------------------------------------------------ ---------- ----------
SYSAUX 5120 3588.19
UNDOTBS1 3715 3619.63
EAS_D_FXWL_TEMP2 2048 2047
USERS 5 3.69
EAS_D_FXWL_STANDARD 97340 2910.88
SYSTEM 6740 152
EAS_T_FXWL_STANDARD 5120 5119
TEMP 9047

已选择8行。
}}}


在RAC中创建

users

select * from all_users;

create schema(users)

select tablespace_name from user_tablespaces;

select USERNAME, DEFAULT_TABLESPACE from DBA_USERS;

select USERNAME, DEFAULT_TABLESPACE from DBA_USERS where DEFAULT_TABLESPACE = 'DEV_DB';

create tablespace

现有环境 

SQL> select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
from
(select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
from dba_free_space group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as tbs_size
from dba_data_files group by tablespace_name
UNION
select tablespace_name, sum(bytes)/1024/1024 tbs_size
from dba_temp_files
group by tablespace_name ) b
where a.tablespace_name(+)=b.tablespace_name;

TABLESPACE_NAME                                                  SIZEMB     FREEMB
------------------------------------------------------------ ---------- ----------
SYSAUX                                                             5120    3588.19
UNDOTBS1                                                           3715    3619.63
EAS_D_FXWL_TEMP2                                                   2048       2047
USERS                                                                 5       3.69
EAS_D_FXWL_STANDARD                                               97340    2910.88
SYSTEM                                                             6740        152
EAS_T_FXWL_STANDARD                                                5120       5119
TEMP                                                               9047

已选择8行。

在RAC中创建 

conn fxwl/fxwl
select USERNAME, DEFAULT_TABLESPACE from DBA_USERS;

CREATE TABLESPACE  EAS_D_FXWL_STANDARD
    DATAFILE '+SSD_DATA' SIZE 30G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE
    LOGGING
    ONLINE
    SEGMENT SPACE MANAGEMENT AUTO
    /

ALTER TABLESPACE  EAS_D_FXWL_STANDARD add  DATAFILE '+SSD_DATA' SIZE 30G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;
ALTER TABLESPACE  EAS_D_FXWL_STANDARD add  DATAFILE '+SSD_DATA' SIZE 30G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;
ALTER TABLESPACE  EAS_D_FXWL_STANDARD add  DATAFILE '+SSD_DATA' SIZE 30G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;

create user fxwl identified by fxwl default tablespace EAS_D_FXWL_STANDARD quota unlimited on EAS_D_FXWL_STANDARD;

create user fxwlq identified by fxwlq default tablespace users quota unlimited on users;

grant dba to fxwl;

impdp \"system as sysdba\" schemas=fxwl directory=dump_dir dumpfile=FXWL_201611030600.DMP logfile=impdpFrom11.2.0.1.log table_exists_action=replace parfile=dmp.txt cluster=n 

SQL> select name from v$tempfile;

NAME
-----------------------------------------------------------
D:\ORACLE\ORADATA\FXWL\TEMP01.DBF

SQL> select name from v$datafile;

NAME
-----------------------------------------------------------
D:\ORACLE\ORADATA\FXWL\SYSTEM01.DBF
D:\ORACLE\ORADATA\FXWL\SYSAUX01.DBF
D:\ORACLE\ORADATA\FXWL\UNDOTBS01.DBF
D:\ORACLE\ORADATA\FXWL\USERS01.DBF
D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD101.ORA
D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD102.ORA
D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD103.ORA
D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD104.ORA
D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD105.ORA
D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD106.ORA
D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD107.ORA

NAME
-----------------------------------------------------------
D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_TEMP2101.ORA
D:\ORACLE\ORADATA\FXWL\EAS_T_FXWL_STANDARD101.ORA

已选择13行。

SQL>

dump.txt 

remap_datafile="'D:/ORACLE/ORADATA/FXWL/SYSTEM01.DBF':'+DATA'"
remap_datafile="'D:/ORACLE/ORADATA/FXWL/SYSAUX01.DBF':'+DATA'"
remap_datafile="'D:/ORACLE/ORADATA/FXWL/UNDOTBS01.DBF':'+DATA'"
remap_datafile="'D:/ORACLE/ORADATA/FXWL/USERS01.DBF':'+DATA'"
remap_datafile="'D:/ORACLE/ORADATA/FXWL/EAS_D_FXWL_STANDARD101.ORA':'+DATA'"
remap_datafile="'D:/ORACLE/ORADATA/FXWL/EAS_D_FXWL_STANDARD102.ORA':'+DATA'"
remap_datafile="'D:/ORACLE/ORADATA/FXWL/EAS_D_FXWL_STANDARD103.ORA':'+DATA'"
remap_datafile="'D:/ORACLE/ORADATA/FXWL/EAS_D_FXWL_STANDARD104.ORA':'+DATA'"
remap_datafile="'D:/ORACLE/ORADATA/FXWL/EAS_D_FXWL_STANDARD105.ORA':'+DATA'"
remap_datafile="'D:/ORACLE/ORADATA/FXWL/EAS_D_FXWL_STANDARD106.ORA':'+DATA'"
remap_datafile="'D:/ORACLE/ORADATA/FXWL/EAS_D_FXWL_STANDARD107.ORA':'+DATA'"


'D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_TEMP2101.ORA':'+SSD_DATA'
'D:\ORACLE\ORADATA\FXWL\EAS_T_FXWL_STANDARD101.ORA':'+SSD_DATA'

 impdp \"system as sysdba\" schemas=fxwl directory=dump_dir dumpfile=FXWL_201611030600.DMP logfile=impdpFrom11.2.0.1.log table_exists_action=replace  cluster=n remap_datafile='D:\ORACLE\ORADATA\FXWL\SYSTEM01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\SYSAUX01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\UNDOTBS01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\USERS01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD101.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD102.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD103.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD104.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD106.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD107.ORA':'+SSD_DATA'

full with replace table_exists_action

 impdp \"system as sysdba\" full=y directory=dump_dir dumpfile=FXWL_FULL_DATA_BACKUP.DMP logfile=1101.1156impdpFrom11.2.0.1_full.log table_exists_action=replace  cluster=n remap_datafile='D:\ORACLE\ORADATA\FXWL\SYSTEM01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\SYSAUX01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\UNDOTBS01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\USERS01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD101.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD102.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD103.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD104.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD106.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD107.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_TEMP2101.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_T_FXWL_STANDARD101.ORA':'+SSD_DATA'

full

 impdp \"system as sysdba\" full=y directory=dump_dir dumpfile=FXWL_FULL_DATA_BACKUP.DMP logfile=1101.1156impdpFrom11.2.0.1_full.log remap_datafile='D:\ORACLE\ORADATA\FXWL\SYSTEM01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\SYSAUX01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\UNDOTBS01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\USERS01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD101.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD102.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD103.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD104.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD106.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD107.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_TEMP2101.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_T_FXWL_STANDARD101.ORA':'+SSD_DATA'

schemas

 impdp \"system as sysdba\" schemas=fxwl directory=dump_dir dumpfile=FXWL_201611030600.DMP logfile=1104.1325impdpFrom11.2.0.1_schemas.log remap_datafile='D:\ORACLE\ORADATA\FXWL\SYSTEM01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\SYSAUX01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\UNDOTBS01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\USERS01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD101.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD102.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD103.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD104.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD106.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD107.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_TEMP2101.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_T_FXWL_STANDARD101.ORA':'+SSD_DATA'

select count(table_name) from user_tables;

désert/Oracle/create_new_schema (last edited 2017-04-27 08:33:53 by merlyn)