{"id":904,"date":"2014-03-24T09:01:04","date_gmt":"2014-03-24T08:01:04","guid":{"rendered":"http:\/\/clemente.pamplona.name\/dba\/?p=904"},"modified":"2014-04-14T16:45:48","modified_gmt":"2014-04-14T14:45:48","slug":"tabla-noexp-en-oracle","status":"publish","type":"post","link":"http:\/\/clemente.pamplona.name\/dba\/tabla-noexp-en-oracle\/","title":{"rendered":"Tabla noexp$ en oracle"},"content":{"rendered":"<p>Hoy vamos ha hacer una de esas entradas \u00abpara guardar\u00bb, su merito es totalmente de un compa\u00f1ero de trabajo, que me ha contado esta ma\u00f1ana la existencia de esta tabla.<br \/>\nOracle tiene una tabla llamada  <strong>noexp$<\/strong> donde puedes ubicar los objetos de los que no quieras que se hagan exports.<\/p>\n<p>Esta tabla tiene 3 campos, el OWNER del objeto, el OWNER del objeto y el tipo de objeto que es.<\/p>\n<pre>\r\nSQL> desc NOEXP$\r\nName Null? Type\r\n--------------------- -------- ------------\r\n OWNER                 NOT NULL VARCHAR2(30)\r\nNAME                  NOT NULL VARCHAR2(30)\r\nOBJ_TYPE              NOT NULL NUMBER\r\n<\/pre>\n<p>Si  queremos exportar la tabla PASS del esquema FOTOS, ejecutaremos el comando :<\/p>\n<pre>\r\n[oracle@test] [$expdp system\/***** tables=FOTOS.PASS file=nada.dmp directory=backup\r\nExport: Release 11.2.0.2.0 - Production on Lun Abr 14 16:38:48 2014\r\nCopyright (c) 1982, 2009, Oracle and\/or its affiliates.  All rights reserved.\r\nConnected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production\r\nLegacy Mode Active due to the following parameters:\r\nLegacy Mode Parameter: \"file=nada.dmp\" Location: Command Line, Replaced with: \"dumpfile=nada.dmp\"\r\nLegacy Mode has set reuse_dumpfiles=true parameter.\r\nStarting \"SYSTEM\".\"SYS_EXPORT_TABLE_01\":  system\/******** tables=FOTOS.PASS dumpfile=nada.dmp directory=backup reuse_dumpfiles=true\r\nEstimate in progress using BLOCKS method...\r\nProcessing object type TABLE_EXPORT\/TABLE\/TABLE_DATA\r\nTotal estimation using BLOCKS method: 1024 KB\r\nProcessing object type TABLE_EXPORT\/TABLE\/TABLE\r\nProcessing object type TABLE_EXPORT\/TABLE\/INDEX\/INDEX\r\nProcessing object type TABLE_EXPORT\/TABLE\/CONSTRAINT\/CONSTRAINT\r\nProcessing object type TABLE_EXPORT\/TABLE\/INDEX\/STATISTICS\/INDEX_STATISTICS\r\nProcessing object type TABLE_EXPORT\/TABLE\/CONSTRAINT\/REF_CONSTRAINT\r\nProcessing object type TABLE_EXPORT\/TABLE\/STATISTICS\/TABLE_STATISTICS\r\n. . exported \"FOTOS\".\"PASS\"                              6.796 KB      27 rows\r\nMaster table \"SYSTEM\".\"SYS_EXPORT_TABLE_01\" successfully loaded\/unloaded\r\n******************************************************************************\r\nDump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:\r\n  \/vgbackup\/backup\/oracle\/nada.dmp\r\nJob \"SYSTEM\".\"SYS_EXPORT_TABLE_01\" successfully completed at 21:39:07\r\n<\/pre>\n<p>Ha funcionado correctamente, pero , <strong>y si no queremos que se pueda exportar esta tabla?<\/strong><\/p>\n<p>Lo primero que tendremos que hacer es ver el tipo de objeto que es esa tabla. Para ello ejecutaremos la consulta <\/p>\n<pre>\r\nSQL> select name,type# from obj$ where name = 'PASS';\r\nNAME                                TYPE#\r\n------------------------------ ----------\r\nPASS                                    2\r\n<\/pre>\n<p>Una vez sabemos que es de tipo \u00ab2\u00bb, la introduciremos en la tabla \u00abnoexp$\u00bb (como sys) <\/p>\n<pre>\r\ninsert into NOEXP$ values ('FOTOS','PASS','2');\r\n<\/pre>\n<p>Que ocurre si intentas hacer un export de esa tabla??<\/p>\n<pre>\r\n[oracle@test [$expdp system\/**** tables=FOTOS.PASS file=nada.dmp directory=backup\r\nExport: Release 11.2.0.2.0 - Production on Lun Abr 14 16:42:03 2014\r\nCopyright (c) 1982, 2009, Oracle and\/or its affiliates.  All rights reserved.\r\nConnected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production\r\nLegacy Mode Active due to the following parameters:\r\nLegacy Mode Parameter: \"file=nada.dmp\" Location: Command Line, Replaced with: \"dumpfile=nada.dmp\"\r\nLegacy Mode has set reuse_dumpfiles=true parameter.\r\nStarting \"SYSTEM\".\"SYS_EXPORT_TABLE_01\":  system\/******** tables=FOTOS.PASS dumpfile=nada.dmp directory=backup reuse_dumpfiles=true\r\nEstimate in progress using BLOCKS method...\r\nProcessing object type TABLE_EXPORT\/TABLE\/TABLE_DATA\r\nTotal estimation using BLOCKS method: 0 KB\r\nORA-39166: Object FOTOS.PASS was not found.\r\nORA-31655: no data or metadata objects selected for job\r\nJob \"SYSTEM\".\"SYS_EXPORT_TABLE_01\" completed with 2 error(s) at 16:42:07\r\n<\/pre>\n<p>Como vemos, el export (expdp) no encuentra la tabla, con lo que no puede llevar a cabo la exportaci\u00f3n.<br \/>\nHay que tener mucho cuidado ya que, lo que no est\u00e1 exportando es la tabla, no los contenidos, con lo que , en nuestro caso, un export full de la base de datos har\u00eda que la aplicaci\u00f3n no funcionase ya que ,no existe la tabla de passwords ( ni la estructura ni los datos)<\/p>\n<p>M\u00e1s informaci\u00f3n, como siempre en metalink en la nota  <strong>\u00abOERR: EXP 55 %s.%s is marked not exportable (Doc ID 47991.1)\u00bb<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Hoy vamos ha hacer una de esas entradas \u00abpara guardar\u00bb, su merito es totalmente de un compa\u00f1ero de trabajo, que me ha contado esta ma\u00f1ana la existencia de esta tabla. Oracle tiene una tabla llamada noexp$ donde puedes ubicar los &hellip; <a href=\"http:\/\/clemente.pamplona.name\/dba\/tabla-noexp-en-oracle\/\">Sigue leyendo <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,66],"tags":[246,97,106,152,147,154,153],"class_list":["post-904","post","type-post","status-publish","format-standard","hentry","category-backup","category-errores-ora","tag-backup","tag-expdp","tag-impdp","tag-noexp","tag-oerr-exp-55","tag-ora-31655","tag-ora-39166"],"_links":{"self":[{"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/posts\/904","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/comments?post=904"}],"version-history":[{"count":3,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/posts\/904\/revisions"}],"predecessor-version":[{"id":915,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/posts\/904\/revisions\/915"}],"wp:attachment":[{"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/media?parent=904"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/categories?post=904"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/tags?post=904"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}