Objetivo:
Precisamos criar uma base para área de desenvolvimento realizar testes de uma aplicação e não temos espaço suficiente para clonar a base de produção, neste caso podemos utilizar um recurso do DataPump que permite copiar um percentual de dados da base.
Precisamos criar uma base para área de desenvolvimento realizar testes de uma aplicação e não temos espaço suficiente para clonar a base de produção, neste caso podemos utilizar um recurso do DataPump que permite copiar um percentual de dados da base.
Ambiente:
SO: Linux 2.6.9-67.0.0.0.1.EL i686 i386 GNU/Linux
Base: Oracle Database 10.2.0.5.0 com archive log habilitado
Overview:
Nessa simulação vamos utilizar um esquema para demonstrar como podemos criar uma base de teste menor que a base de produção. O esquema utilizado será o SH que é um esquema exemplo criado opcionamento durante a instalação do banco de dados.
Vamos utilizar a mesma base para fazer export e o import o que não inviabiliza a demonstração uma vez que o objetivo é demonstrar que podemos copiar todos os objetos de um esquema reduzindo a quantidade de espaço utilizada por eles.
Tópicos:
- Criar usuário de teste;
- Export esquema SH;
- Import esquema SH_TESTE;
- Comparar objetos dos esquemas;
- Comparar tamanhos dos esquemas;
1. Criar usuário de teste
SQL> create user sh_teste identified by oracle
default tablespace appl02 temporary tablespace temp;
Usuário criado.
SQL> grant connect to sh_teste;
Concessão bem-sucedida.
SQL> grant unlimited tablespace to sh_teste;
Concessão bem-sucedida.
SQL> spool off;
2. Export esquema SH
Primeiro vamos executar um EXPORT do esquema SH usando o parâmetro "SAMPLE", este parâmetro especifica um percentual de registros a serem copiados da base, neste caso vamos usar 30%:
[oracle@odbsrv02 ~]$ expdp \"/ as sysdba\" directory=DATA_PUMP_DIR sample=30
schemas=SH dumpfile=SH_esquema.dmp logfile=SH_esquema.log
Export: Release 10.2.0.5.0 - Production on Wednesday, 16 June, 2010 2:30:03
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" directory=DATA_PUMP_DIR
sample=30 schemas=SH dumpfile=SH_esquema.dmp logfile=SH_esquema.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 35.31 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Processing object type SCHEMA_EXPORT/DIMENSION
. . exported "SH"."CUSTOMERS" 3.004 MB 16875 rows
. . exported "SH"."SUPPLEMENTARY_DEMOGRAPHICS" 212.3 KB 1332 rows
. . exported "SH"."SALES":"SALES_Q4_2001" 701.6 KB 21029 rows
. . exported "SH"."SALES":"SALES_Q1_1999" 638.7 KB 19186 rows
. . exported "SH"."SALES":"SALES_Q3_2001" 659.5 KB 19746 rows
. . exported "SH"."SALES":"SALES_Q1_2000" 615.4 KB 18435 rows
. . exported "SH"."SALES":"SALES_Q1_2001" 613.8 KB 18351 rows
. . exported "SH"."SALES":"SALES_Q2_2001" 636.1 KB 19029 rows
. . exported "SH"."SALES":"SALES_Q3_1999" 672.3 KB 20208 rows
. . exported "SH"."SALES":"SALES_Q4_1999" 621.0 KB 18637 rows
. . exported "SH"."SALES":"SALES_Q2_2000" 557.8 KB 16645 rows
. . exported "SH"."SALES":"SALES_Q3_2000" 589.0 KB 17617 rows
. . exported "SH"."SALES":"SALES_Q4_1998" 482.4 KB 14421 rows
. . exported "SH"."SALES":"SALES_Q4_2000" 560.1 KB 16745 rows
. . exported "SH"."SALES":"SALES_Q2_1999" 546.6 KB 16363 rows
. . exported "SH"."SALES":"SALES_Q1_1998" 442.2 KB 13218 rows
. . exported "SH"."SALES":"SALES_Q3_1998" 512.7 KB 15349 rows
. . exported "SH"."SALES":"SALES_Q2_1998" 359.0 KB 10669 rows
. . exported "SH"."FWEEK_PSCAT_SALES_MV" 131.1 KB 3406 rows
. . exported "SH"."PROMOTIONS" 24.66 KB 167 rows
. . exported "SH"."TIMES" 130.2 KB 574 rows
. . exported "SH"."COSTS":"COSTS_Q4_2001" 87.75 KB 2697 rows
. . exported "SH"."COSTS":"COSTS_Q1_1999" 59.41 KB 1762 rows
. . exported "SH"."COSTS":"COSTS_Q1_2001" 72.94 KB 2203 rows
. . exported "SH"."COSTS":"COSTS_Q2_2001" 60.59 KB 1793 rows
. . exported "SH"."COSTS":"COSTS_Q3_2001" 74.60 KB 2259 rows
. . exported "SH"."COSTS":"COSTS_Q1_1998" 47.25 KB 1356 rows
. . exported "SH"."COSTS":"COSTS_Q1_2000" 41.74 KB 1169 rows
. . exported "SH"."COSTS":"COSTS_Q2_1998" 27.42 KB 692 rows
. . exported "SH"."COSTS":"COSTS_Q2_1999" 44.67 KB 1269 rows
. . exported "SH"."COSTS":"COSTS_Q2_2000" 39.98 KB 1110 rows
. . exported "SH"."COSTS":"COSTS_Q3_1998" 44.03 KB 1250 rows
. . exported "SH"."COSTS":"COSTS_Q3_1999" 47.19 KB 1353 rows
. . exported "SH"."COSTS":"COSTS_Q3_2000" 48.67 KB 1399 rows
. . exported "SH"."COSTS":"COSTS_Q4_1998" 47.65 KB 1369 rows
. . exported "SH"."COSTS":"COSTS_Q4_1999" 53.85 KB 1576 rows
. . exported "SH"."COSTS":"COSTS_Q4_2000" 52.44 KB 1525 rows
. . exported "SH"."CAL_MONTH_SALES_MV" 5.476 KB 13 rows
. . exported "SH"."CHANNELS" 6.515 KB 1 rows
. . exported "SH"."COUNTRIES" 8.226 KB 7 rows
. . exported "SH"."PRODUCTS" 15.93 KB 24 rows
. . exported "SH"."COSTS":"COSTS_1995" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_1996" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_H1_1997" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_H2_1997" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q1_2002" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q1_2003" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q2_2002" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q2_2003" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q3_2002" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q3_2003" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q4_2002" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q4_2003" 0 KB 0 rows
. . exported "SH"."MVIEW$_EXCEPTIONS" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_1995" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_1996" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_H1_1997" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_H2_1997" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_Q1_2002" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_Q1_2003" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_Q2_2002" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_Q2_2003" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_Q3_2002" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_Q3_2003" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_Q4_2002" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_Q4_2003" 0 KB 0 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/product/10.2.0/db_1/admin/lab1/dpdump/SH_esquema.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 02:32:02
[oracle@odbsrv02 ~]$
3. Import esquema SH_TESTE
Em seguida vamos importar os objetos do esquema SH no esquema SH_TESTE dizendo ao Data Pump para reduzir o tamanho das extensões para 30%:
[oracle@odbsrv02 ~]$ impdp \"/ as sysdba\" transform=pctspace:30 remap_schema=SH:SH_TESTE
directory=DATA_PUMP_DIR dumpfile=SH_esquema.dmp logfile=imp_SH_TESTE_esquema.log
Import: Release 10.2.0.5.0 - Production on Wednesday, 16 June, 2010 2:47:58
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": "/******** AS SYSDBA" transform=pctspace:70 remap_schema=SH:SH_TESTE
directory=DATA_PUMP_DIR dumpfile=SH_esquema.dmp logfile=imp_SH_TESTE_esquema.log
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SH_TESTE" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SH_TESTE"."CUSTOMERS" 3.004 MB 16875 rows
. . imported "SH_TESTE"."SUPPLEMENTARY_DEMOGRAPHICS" 212.3 KB 1332 rows
. . imported "SH_TESTE"."SALES":"SALES_Q4_2001" 701.6 KB 21029 rows
. . imported "SH_TESTE"."SALES":"SALES_Q1_1999" 638.7 KB 19186 rows
. . imported "SH_TESTE"."SALES":"SALES_Q3_2001" 659.5 KB 19746 rows
. . imported "SH_TESTE"."SALES":"SALES_Q1_2000" 615.4 KB 18435 rows
. . imported "SH_TESTE"."SALES":"SALES_Q1_2001" 613.8 KB 18351 rows
. . imported "SH_TESTE"."SALES":"SALES_Q2_2001" 636.1 KB 19029 rows
. . imported "SH_TESTE"."SALES":"SALES_Q3_1999" 672.3 KB 20208 rows
. . imported "SH_TESTE"."SALES":"SALES_Q4_1999" 621.0 KB 18637 rows
. . imported "SH_TESTE"."SALES":"SALES_Q2_2000" 557.8 KB 16645 rows
. . imported "SH_TESTE"."SALES":"SALES_Q3_2000" 589.0 KB 17617 rows
. . imported "SH_TESTE"."SALES":"SALES_Q4_1998" 482.4 KB 14421 rows
. . imported "SH_TESTE"."SALES":"SALES_Q4_2000" 560.1 KB 16745 rows
. . imported "SH_TESTE"."SALES":"SALES_Q2_1999" 546.6 KB 16363 rows
. . imported "SH_TESTE"."SALES":"SALES_Q1_1998" 442.2 KB 13218 rows
. . imported "SH_TESTE"."SALES":"SALES_Q3_1998" 512.7 KB 15349 rows
. . imported "SH_TESTE"."SALES":"SALES_Q2_1998" 359.0 KB 10669 rows
. . imported "SH_TESTE"."FWEEK_PSCAT_SALES_MV" 131.1 KB 3406 rows
. . imported "SH_TESTE"."PROMOTIONS" 24.66 KB 167 rows
. . imported "SH_TESTE"."TIMES" 130.2 KB 574 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q4_2001" 87.75 KB 2697 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q1_1999" 59.41 KB 1762 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q1_2001" 72.94 KB 2203 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q2_2001" 60.59 KB 1793 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q3_2001" 74.60 KB 2259 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q1_1998" 47.25 KB 1356 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q1_2000" 41.74 KB 1169 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q2_1998" 27.42 KB 692 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q2_1999" 44.67 KB 1269 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q2_2000" 39.98 KB 1110 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q3_1998" 44.03 KB 1250 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q3_1999" 47.19 KB 1353 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q3_2000" 48.67 KB 1399 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q4_1998" 47.65 KB 1369 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q4_1999" 53.85 KB 1576 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q4_2000" 52.44 KB 1525 rows
. . imported "SH_TESTE"."CAL_MONTH_SALES_MV" 5.476 KB 13 rows
. . imported "SH_TESTE"."CHANNELS" 6.515 KB 1 rows
. . imported "SH_TESTE"."COUNTRIES" 8.226 KB 7 rows
. . imported "SH_TESTE"."PRODUCTS" 15.93 KB 24 rows
. . imported "SH_TESTE"."COSTS":"COSTS_1995" 0 KB 0 rows
. . imported "SH_TESTE"."COSTS":"COSTS_1996" 0 KB 0 rows
. . imported "SH_TESTE"."COSTS":"COSTS_H1_1997" 0 KB 0 rows
. . imported "SH_TESTE"."COSTS":"COSTS_H2_1997" 0 KB 0 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q1_2002" 0 KB 0 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q1_2003" 0 KB 0 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q2_2002" 0 KB 0 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q2_2003" 0 KB 0 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q3_2002" 0 KB 0 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q3_2003" 0 KB 0 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q4_2002" 0 KB 0 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q4_2003" 0 KB 0 rows
. . imported "SH_TESTE"."MVIEW$_EXCEPTIONS" 0 KB 0 rows
. . imported "SH_TESTE"."SALES":"SALES_1995" 0 KB 0 rows
. . imported "SH_TESTE"."SALES":"SALES_1996" 0 KB 0 rows
. . imported "SH_TESTE"."SALES":"SALES_H1_1997" 0 KB 0 rows
. . imported "SH_TESTE"."SALES":"SALES_H2_1997" 0 KB 0 rows
. . imported "SH_TESTE"."SALES":"SALES_Q1_2002" 0 KB 0 rows
. . imported "SH_TESTE"."SALES":"SALES_Q1_2003" 0 KB 0 rows
. . imported "SH_TESTE"."SALES":"SALES_Q2_2002" 0 KB 0 rows
. . imported "SH_TESTE"."SALES":"SALES_Q2_2003" 0 KB 0 rows
. . imported "SH_TESTE"."SALES":"SALES_Q3_2002" 0 KB 0 rows
. . imported "SH_TESTE"."SALES":"SALES_Q3_2003" 0 KB 0 rows
. . imported "SH_TESTE"."SALES":"SALES_Q4_2002" 0 KB 0 rows
. . imported "SH_TESTE"."SALES":"SALES_Q4_2003" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Processing object type SCHEMA_EXPORT/DIMENSION
Job "SYS"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 02:49:15
[oracle@odbsrv02 ~]$
4. Comparar objetos dos esquemas
Concluida a copia precisamos verificar se todos os objetos foram copiados:
SQL> select owner, object_type, count(1) Qtde
2 from dba_objects
3 where owner in ('SH','SH_TESTE')
4 group by owner, object_type
5 order by 2;
OWNER OBJECT_TYPE QTDE
------------------------------ ------------------- ----------
SH DIMENSION 5
SH_TESTE DIMENSION 5
SH_TESTE INDEX 28
SH INDEX 28
SH_TESTE INDEX PARTITION 168
SH INDEX PARTITION 168
SH_TESTE LOB 2
SH LOB 2
SH MATERIALIZED VIEW 2
SH_TESTE MATERIALIZED VIEW 2
SH_TESTE TABLE 17
SH TABLE 17
SH_TESTE TABLE PARTITION 56
SH TABLE PARTITION 56
SH VIEW 1
SH_TESTE VIEW 1
16 linhas selecionadas.
SQL>
5. Comparar tamanhos dos esquemas
Agora vamos verificar se o DataPump fez o trabalho dele corretamente, vamos comparar a quantidade de espaço utilizada pelo esquema SH e SH_TESTE:
SQL> select owner, sum(bytes)/1024 "Tamanho KB"
2 from dba_segments
3 where owner in ('SH','SH_TESTE')
4 group by owner;
OWNER Tamanho KB
------------------------------ ----------
SH_TESTE 35712
SH 81984
SQL>
Obs: A redução do espaço utilizado pelo esquema não é um calculo exato devido
a forma como o Oracle trabalha com segmentos de dados e extensões.
Nenhum comentário:
Postar um comentário