Armazenamento das Procedures/funções e PKGs – GPO (Grupo de Profissionais Oracle)

5 de setembro de 2017 às 12:58 am #108971

José Laurindo Chiappa

Moderador

Colega, o conceito que te falta é que os objetos compostos por CÓDIGO PL/SQL (como Procedures, packages, etc) fica NECESSARIAMENTE ARMAZENADO na tablespace SYSTEM : p manual de CONCEITOS BÁSICOS (online em http://docs.oracle.com/cd/B28359_01/server.111/b28318/physical.htm#CNCPT003 no caso de versão 11g) o diz textualmente :

“All data stored on behalf of stored PL/SQL program units (procedures, functions, packages and triggers) resides in the SYSTEM tablespace.”

Agora : isso posto, Não Faz Sentido o que vc diz, que “Como o banco XE não suporte a cláusula “Exclude….” : essa cláusula EXCLUDE é do EXPDP, ie, do DATAPUMP, e não tem NADA A VER com a Edição do database, e XE ACEITA SIM exportação via datapump… Exemplo (de um export full excluindo apenas uma determinada tablespace :

==> como o datapump/expdp trabalha com ** DIRECTORIES **, e não com PATH físico, vejamos quais Directories eu tenho :

SYSTEM:@XE:SQL>select * from all_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH

SYS TEMP_DIR C:EXPAND
SYS SUBDIR C:/Expand/db-sample-schemas-12.2.0.1/order_entry//
2002/Sep

SYS SS_OE_XMLDIR C:/Expand/db-sample-schemas-12.2.0.1/order_entry/
SYS LOG_FILE_DIR C:Expanddb-sample-schemas-12.2.0.1log_xe
SYS DATA_FILE_DIR C:/Expand/db-sample-schemas-12.2.0.1/sales_history
/

SYS MEDIA_DIR C:/Expand/db-sample-schemas-12.2.0.1/product_media
/

SYS TMP C:Usersjlchi_000
SYS ORACLECLRDIR C:oraclexeapporacleproduct11.2.0serverbinc
lr

SYS DATA_PUMP_DIR C:oraclexeapporacle/admin/xe/dpdump/
SYS XMLDIR C:oraclexeapporacleproduct11.2.0serverrdbms
xml

SYS ORACLE_OCM_CONFIG_DIR C:ADEaime_xe28oracle/ccr/state

11 linhas selecionadas.

==> vamos ver quais tablespaces eu tenho, pra eu tentar excluir algumas via parâmetro EXCLUDE, que nem vc quer :

SYSTEM:@XE:SQL>select tablespace_name from dba_tablespaces;

TABLESPACE_NAME

SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
TEST_DATA

6 linhas selecionadas.

==> vamos consultar alguns dos stored PL/SQLs, pra confirmar que eles serão sim gerados no dump file :

SYSTEM:@XE:SQL>select DISTINCT owner, type, NAME from DBA_SOURCE where owner = ‘HR’ order by 1,2,3
2 ;

OWNER TYPE NAME

HR PACKAGE CUST_SAL
HR PACKAGE BODY CUST_SAL
HR PROCEDURE ADD_JOB_HISTORY
HR PROCEDURE P1
HR PROCEDURE RUNSELECT
HR PROCEDURE SECURE_DML
HR TRIGGER SECURE_EMPLOYEES
HR TRIGGER TRIG_TESTE_SEQUENCE
HR TRIGGER UPDATE_JOB_HISTORY

9 linhas selecionadas.

SYSTEM:@XE:SQL>

SYSTEM:@XE:SQL>exit

==> ok, vou usar o directory DATA_PUMP_DIR pro meu exemplo… Não é obrigatório mas vou navegar pra ele e disparar o expdp/datapump dentro da pasta real para onde o DIRECTORY aponta, pra que depois eu possa ver e mostrar os arqs gerados mais facilmente :

C:Usersjlchi_000>cd C:oraclexeapporacle/admin/xe/dpdump/

==> disparo o dump via DATAPUMP/EXPDP que é quem suporta a opção de exclude :

C:oraclexeapporacleadminXEdpdump>expdp system/oracle directory=DATA_PUMP_DIR dumpfile=expdp_full_exclud_tabls.dmp logfile=expdp_full_exclud_tabls.exp full=y exclude=tablespac
es:USERS

Export: Release 11.2.0.2.0 – Production on Seg Set 4 16:59:09 2017

Conectado a: Oracle Database 11g Express Edition Release 11.2.0.2.0 – 64bit Production
ORA-39001: valor de argumento inválido
ORA-39038: O caminho de objeto “TABLESPACES” não é suportado para jobs de FULL.

C:oraclexeapporacleadminXEdpdump>

==>> OPA : viu a msg que deu ??? Tá BEM CLARO, o problema *** NÃO É *** com o XE ou não XE, é que para export FULL , que é o que vc quer, não dá pra excluir Tablespaces… Isso é COMPLETAMENTE DOCUMENTADO, veja o Manual em http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#SUTIL847 que claramente diz :

”
EXCLUDE

Default: There is no default

Purpose

Enables you to filter the metadata that is exported by specifying objects and object types to be excluded from the export operation.

Syntax and Description

EXCLUDE=object_type[:name_clause] [, …]

The object_type specifies the type of object to be excluded. To see a list of valid values for object_type, query the following views: DATABASE_EXPORT_OBJECTS for full mode…”

==> SE vc fizer a consulta na view DATABASE_EXPORT_OBJECTS, vc VAI VEr que realmente filtragem por TABLESPACE não tá disponível na versão 11g…

O que fazer : Já que vc quer excluir as tabelas de LOG que estão na tablespace de log, FAÇA ISSO !! No caso, sempre que eu quero informar cláusulas mais complexas pro client do datapump (ou do exp tradicional, que fosse) eu PREFIRO usar um PARFILE, pra evitar erros por causa de caracteres especiais :

C:oraclexeapporacleadminXEdpdump>type param_exclude.par
exclude=TABLE:” in (select table_name from DBA_TABLES where tablespace_name=’USERS’)”

C:oraclexeapporacleadminXEdpdump>

==> Sacou a lógica ?? Estou excluindo ** TODAS ** as tabelas que residem na tablespace USERS… A execução :

C:oraclexeapporacleadminXEdpdump>expdp system/oracle directory=DATA_PUMP_DIR dumpfile=expdp_full_exclud_tabls.dmp logfile=expdp_full_exclud_tabls.exp full=y parfile=param_exc
lude.par

Export: Release 11.2.0.2.0

Conectado a: Oracle Database 11g Express Edition Release 11.2.0.2.0 – 64bit Production
Iniciando “SYSTEM”.”SYS_EXPORT_FULL_01″: system/******** directory=DATA_PUMP_DIR dumpfile=expdp_full_exclud_tabls.dmp logfile=expdp_full_exclud_tabls.exp full=y parfile=param_excl
ude.par
Estimativa em andamento com o método BLOCKS…
Processando o tipo de objeto DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Estimativa em andamento com o método BLOCKS…
Processando o tipo de objeto DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Estimativa total usando o método de BLOCKS: 164.2 MB
Processando o tipo de objeto DATABASE_EXPORT/TABLESPACE
Processando o tipo de objeto DATABASE_EXPORT/PROFILE
Processando o tipo de objeto DATABASE_EXPORT/SYS_USER/USER
Processando o tipo de objeto DATABASE_EXPORT/SCHEMA/USER
…..
. . exportou “APEX_040000″.”WWV_FLOW_ROW_TEMPLATES” 482.7 KB 500 linhas
. . exportou “APEX_040000″.”WWV_FLOW_PROCESSING” 585.6 KB 284 linhas
ORA-39181: Apenas parte dos dados de tabela pode ser exportada devido ao controle de acesso detalhado em “OE”.”PURCHASEORDER”
. . exportou “OE”.”PURCHASEORDER” 243.9 KB 132 linhas
. . exportou “APEX_040000″.”WWV_FLOW_PAGE_DA_ACTIONS” 408.9 KB 2844 linhas
. . exportou “APEX_040000″.”WWV_FLOW_CUSTOM_AUTH_SETUPS” 44.28 KB 76 linhas
. . exportou “TEST_USER”.”TEST_TAB” 287.3 KB 10000 linhas
. . exportou “APEX_040000″.”WWV_FLOW_BUTTON_TEMPLATES” 170.4 KB 428 linhas
….
. . exportou “SYSTEM”.”REPCAT$_TEMPLATE_TARGETS” 0 KB 0 linhas
. . exportou “SYSTEM”.”REPCAT$_USER_AUTHORIZATIONS” 0 KB 0 linhas
. . exportou “SYSTEM”.”REPCAT$_USER_PARM_VALUES” 0 KB 0 linhas
. . exportou “SYSTEM”.”SQLPLUS_PRODUCT_PROFILE” 0 KB 0 linhas
Tabela-mestre “SYSTEM”.”SYS_EXPORT_FULL_01″ carregada/descarregada com sucesso

Conjunto de arquivos de dump para SYSTEM.SYS_EXPORT_FULL_01 é:
C:ORACLEXEAPPORACLEADMINXEDPDUMPEXPDP_FULL_EXCLUD_TABLS.DMP
O job “SYSTEM”.”SYS_EXPORT_FULL_01″ foi concluído com 1 erro(s) em 17:23:34

C:oraclexeapporacleadminXEdpdump>

==> Blz ? Agora vou importar só alguns tipos de objetos (e com a opção de gerar arquivo com o conteúdo, pra eu poder mostrar ele) :

C:oraclexeapporacleadminXEdpdump>impdp system/oracle directory=DATA_PUMP_DIR dumpfile=expdp_full_exclud_tabls.dmp logfile=expdp_full_exclud_tabls.imp include=PACKAGE,FUNCTION
,PROCEDURE show=y

Import: Release 11.2.0.2.0

Conectado a: Oracle Database 11g Express Edition Release 11.2.0.2.0 – 64bit Production
Modo Legado Ativo devido aos seguintes parâmetros:
Parâmetro do Modo Legado: “show=TRUE” Localização: Command Line, Substituído com: “sqlfile=expdp_full_exclud_tabls.sql”
Tabela-mestre “SYSTEM”.”SYS_SQL_FILE_FULL_01″ carregada/descarregada com sucesso
Iniciando “SYSTEM”.”SYS_SQL_FILE_FULL_01″: system/******** directory=DATA_PUMP_DIR dumpfile=expdp_full_exclud_tabls.dmp logfile=expdp_full_exclud_tabls.imp include=PACKAGE,FUNCTIO
N,PROCEDURE sqlfile=expdp_full_exclud_tabls.sql
Processando o tipo de objeto DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC
Processando o tipo de objeto DATABASE_EXPORT/SCHEMA/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processando o tipo de objeto DATABASE_EXPORT/SCHEMA/FUNCTION/FUNCTION
Processando o tipo de objeto DATABASE_EXPORT/SCHEMA/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
Processando o tipo de objeto DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE
Processando o tipo de objeto DATABASE_EXPORT/SCHEMA/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processando o tipo de objeto DATABASE_EXPORT/SCHEMA/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processando o tipo de objeto DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION
Processando o tipo de objeto DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE
Processando o tipo de objeto DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY
O job “SYSTEM”.”SYS_SQL_FILE_FULL_01″ foi concluído com sucesso

==> resultado :

C:oraclexeapporacleadminXEdpdump>type expdp_full_exclud_tabls.sql

— CONNECT SYSTEM
ALTER SESSION SET EVENTS ‘10150 TRACE NAME CONTEXT FOREVER, LEVEL 1’;
ALTER SESSION SET EVENTS ‘10904 TRACE NAME CONTEXT FOREVER, LEVEL 1’;
…..
— new object type path: DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC
CREATE PACKAGE dbms_repcat_auth wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
…

— CONNECT HR
CREATE PACKAGE “CUST_SAL” AS
x number;
y date;
PROCEDURE find_sal(c_id employees.EMPLOYEE_ID%type);
END cust_sal;
/
— CONNECT APEX_040000
CREATE package apexws
is
…..

====>> c.q.d. : as procedures/functions/packages NÃO SÃO eliminadas num export FULL seja qual for a tablespace de usuário que vc eliminar, E já que o datapump 11g (seja em qual EDITION for!!) não permite excluir tablespaces diretamente, vc exclui TABELAS ou ÌNDICES que residam na tabblespace que vc quer…

[]s

Chiappa