{"id":1053,"date":"2014-11-07T14:45:42","date_gmt":"2014-11-07T17:45:42","guid":{"rendered":"http:\/\/www.dbarj.com.br\/?p=1053"},"modified":"2016-01-14T15:11:07","modified_gmt":"2016-01-14T17:11:07","slug":"renomear-usuarios-oracle-11g-dblink-loopback","status":"publish","type":"post","link":"https:\/\/www.dbarj.com.br\/pt-br\/2014\/11\/renomear-usuarios-oracle-11g-dblink-loopback\/","title":{"rendered":"Renomear usu\u00e1rios no Oracle 11g com DBLink de Loopback"},"content":{"rendered":"

Nesse artigo, iremos lidar com\u00a0uma situa\u00e7\u00e3o muito comum em que o DBA recebe a tarefa de renomear ou efetuar a\u00a0c\u00f3pia\u00a0de um usu\u00e1rio no pr\u00f3prio Banco de Dados. Nas linhas abaixo, vou\u00a0explicar como efetuar esta c\u00f3pia sem a necessidade de maiores complexidades e\u00a0de gerar arquivos de dumps\u00a0intermedi\u00e1rios, utilizando o import DataPump com um dblink de loopback.<\/p>\n

Artigo Publicado na OTN:\u00a0http:\/\/www.oracle.com\/technetwork\/pt\/articles\/sql\/clonando-usuarios-dblink-loopback-2354073-ptb.html<\/a><\/strong><\/p>\n

Essa tarefa pode ser feita em 3 etapas r\u00e1pidas:<\/p>\n

    \n
  1. Criar um dblink de loopback tempor\u00e1rio.<\/li>\n
  2. Executar o DataPump Import para clonar o usu\u00e1rio.<\/li>\n
  3. Remover o dblink tempor\u00e1rio criado no item 1.<\/li>\n<\/ol>\n

    Vamos ent\u00e3o come\u00e7ar:<\/p>\n

    1) Criar um dblink de loopback tempor\u00e1rio.<\/h2>\n

    Neste exemplo, iremos clonar todo o usu\u00e1rio SCOTT<\/span><\/strong> com seus objetos e dados para um novo, SCOTT_BKP<\/span><\/strong>. Faremos esta tarefa com o usu\u00e1rio “DBADMIN”, que \u00e9 um DBA desta base.<\/p>\n

    O primeiro passo ser\u00e1 criar o dblink de loopback tempor\u00e1rio. Supondo que o SERVICE_NAME<\/strong> do BD seja “ORCL<\/strong>” e ele esteja executando na porta “1521<\/strong>“:<\/p>\n

    create database link TMP_DBLINK\r\nusing '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL= TCP) (HOST=127.0.0.1)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ORCL)))';<\/pre>\n
    Voc\u00ea tamb\u00e9m pode usar apenas o nome da entrada de TNS, caso exista uma que j\u00e1 aponte localmente para o pr\u00f3prio BD. Supondo, por exemplo, que h\u00e1 um TNS Names com nome “ORCL”:<\/p>\n
    create database link TMP_DBLINK using 'ORCL';<\/pre>\n
    Note que em ambos os casos omiti a cl\u00e1usula\u00a0“connect to” para for\u00e7ar uma conex\u00e3o ao banco com o usu\u00e1rio local que est\u00e1 conectado (DBAMIN, no caso).\u00a0Depois de cri\u00e1-lo, teste-o executando:<\/p>\n
    SQL> select * from dual@TMP_DBLINK;\r\n\r\nD\r\n-\r\nX<\/pre>\n
    2) Executar o DataPump Import para clonar o usu\u00e1rio.<\/h2>\n
    O pr\u00f3ximo passo ser\u00e1 efetuar diretamente o clone do usu\u00e1rio. Se voc\u00ea estiver usando este tutorial para renomear um usu\u00e1rio, garanta que n\u00e3o existe qualquer transa\u00e7\u00e3o, DDL ou DML em seus objetos.<\/p>\n
    Execute o bloco PL\/SQL:<\/p>\n
    DECLARE\r\n\tH1 NUMBER;\r\n\tHSTATUS VARCHAR2(200);\r\n\tFROMUSER VARCHAR2(30);\r\n\tTOUSER VARCHAR2(30);\r\nBEGIN\r\n\tFROMUSER := 'SCOTT';\r\n\tTOUSER := 'SCOTT_BKP';\r\n\tH1 := DBMS_DATAPUMP.OPEN(OPERATION => 'IMPORT', JOB_MODE => 'SCHEMA', REMOTE_LINK => 'TMP_DBLINK');\r\n\tDBMS_DATAPUMP.METADATA_FILTER(HANDLE => H1, NAME => 'SCHEMA_LIST', VALUE => '''' || FROMUSER || '''');\r\n\tDBMS_DATAPUMP.METADATA_REMAP(HANDLE => H1, NAME => 'REMAP_SCHEMA', OLD_VALUE => FROMUSER, VALUE => TOUSER);\r\n\tDBMS_DATAPUMP.START_JOB(HANDLE => H1);\r\n\tDBMS_DATAPUMP.WAIT_FOR_JOB(HANDLE => H1, JOB_STATE => HSTATUS);\r\n\tDBMS_OUTPUT.PUT_LINE('STATUS = ' || HSTATUS);\r\nEND;\r\n\/<\/pre>\n
    O tempo de execu\u00e7\u00e3o do script varia de acordo com a quantidade de objetos e dados do usu\u00e1rio. Verifique se o output foi “STATUS = COMPLETED<\/strong>“. Caso n\u00e3o tenha sido, siga as etapas da \u00faltima parte deste artigo.<\/p>\n
    3) Remover o dblink tempor\u00e1rio criado no item 1.<\/h2>\n
    Por fim, confira se todos os objetos foram duplicados com o SQL abaixo:<\/p>\n
    select   owner,object_type,status,count(*)\r\nfrom     dba_objects\r\nwhere    owner in ('SCOTT','SCOTT_BKP')\r\ngroup by owner,object_type,status\r\norder by 2,1,3;<\/pre>\n
    Se tudo estiver id\u00eantico, remova\u00a0o dblink\u00a0tempor\u00e1rio criado:<\/p>\n
    drop database link TMP_DBLINK;<\/pre>\n
    Pronto, agora voc\u00ea tem uma c\u00f3pia id\u00eantica do seu schema no seu BD!\u00a0Se voc\u00ea estiver usando este tutorial para renomear um usu\u00e1rio, voc\u00ea pode agora remover o usu\u00e1rio antigo ou guarda-lo como um backup.<\/p>\n
    Verificando em caso de Falhas<\/span><\/h2>\n
    Se tiver problemas durante a checagem de objetos, \u00e9 poss\u00edvel ativar o log do import datapump para identificar a causa da falha. Para tanto, ser\u00e1 necess\u00e1rio criar um diret\u00f3rio tempor\u00e1rio para o log:<\/p>\n
    create directory TMP_DIR as '\/tmp';<\/pre>\n
    Em seguida, adicione a instru\u00e7\u00e3o abaixo ap\u00f3s a linha do PL\/SQL com o comando DBMS_DATAPUMP.OPEN<\/span>:<\/p>\n
    DBMS_DATAPUMP.ADD_FILE(\r\n\tHANDLE => H1,\r\n\tFILENAME => 'imp.log',\r\n\tDIRECTORY => 'TMP_DIR',\r\n\tFILETYPE => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE\r\n);<\/pre>\n
    Por fim, ap\u00f3s corrigir o erro e executar o comando corretamente, n\u00e3o esque\u00e7a de remover o diret\u00f3rio tempor\u00e1rio:<\/p>\n
    drop directory TMP_DIR;\r\n<\/pre>\nGostou? N\u00e3o deixe de comentar ou deixar um \ud83d\udc4d!<\/b>\n
    Continue lendo<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-1053","post","type-post","status-publish","format-standard","hentry","category-database","item-wrap"],"yoast_head":"\nRenomear usu\u00e1rios no Oracle 11g com DBLink de Loopback - DBA - Rodrigo Jorge - Oracle Tips and Guides<\/title>\n<meta name=\"description\" content=\"Como renomear rapidamente um usu\u00e1rio no Oracle 11g atrav\u00e9s de DBLink de Loopback\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbarj.com.br\/pt-br\/2014\/11\/renomear-usuarios-oracle-11g-dblink-loopback\/\" \/>\n<meta name=\"twitter:label1\" content=\"Escrito por\" \/>\n\t<meta name=\"twitter:data1\" content=\"DBA RJ\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. tempo de leitura\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutos\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/2014\\\/11\\\/renomear-usuarios-oracle-11g-dblink-loopback\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/2014\\\/11\\\/renomear-usuarios-oracle-11g-dblink-loopback\\\/\"},\"author\":{\"name\":\"DBA RJ\",\"@id\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/#\\\/schema\\\/person\\\/28a44ca3a6633fe4156ad1ea209d40a9\"},\"headline\":\"Renomear usu\u00e1rios no Oracle 11g com DBLink de Loopback\",\"datePublished\":\"2014-11-07T17:45:42+00:00\",\"dateModified\":\"2016-01-14T17:11:07+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/2014\\\/11\\\/renomear-usuarios-oracle-11g-dblink-loopback\\\/\"},\"wordCount\":517,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/#\\\/schema\\\/person\\\/28a44ca3a6633fe4156ad1ea209d40a9\"},\"articleSection\":[\"Oracle Database General\"],\"inLanguage\":\"pt-BR\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/2014\\\/11\\\/renomear-usuarios-oracle-11g-dblink-loopback\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/2014\\\/11\\\/renomear-usuarios-oracle-11g-dblink-loopback\\\/\",\"url\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/2014\\\/11\\\/renomear-usuarios-oracle-11g-dblink-loopback\\\/\",\"name\":\"Renomear usu\u00e1rios no Oracle 11g com DBLink de Loopback - DBA - Rodrigo Jorge - Oracle Tips and Guides\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/#website\"},\"datePublished\":\"2014-11-07T17:45:42+00:00\",\"dateModified\":\"2016-01-14T17:11:07+00:00\",\"description\":\"Como renomear rapidamente um usu\u00e1rio no Oracle 11g atrav\u00e9s de DBLink de Loopback\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/2014\\\/11\\\/renomear-usuarios-oracle-11g-dblink-loopback\\\/#breadcrumb\"},\"inLanguage\":\"pt-BR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/2014\\\/11\\\/renomear-usuarios-oracle-11g-dblink-loopback\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/2014\\\/11\\\/renomear-usuarios-oracle-11g-dblink-loopback\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Renomear usu\u00e1rios no Oracle 11g com DBLink de Loopback\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/#website\",\"url\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/\",\"name\":\"DBA - Rodrigo Jorge - Oracle Tips and Guides\",\"description\":\"Blog about Databases, Security and High Availability\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/#\\\/schema\\\/person\\\/28a44ca3a6633fe4156ad1ea209d40a9\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"pt-BR\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/#\\\/schema\\\/person\\\/28a44ca3a6633fe4156ad1ea209d40a9\",\"name\":\"DBA RJ\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"pt-BR\",\"@id\":\"https:\\\/\\\/www.dbarj.com.br\\\/wp-content\\\/uploads\\\/2019\\\/09\\\/RodrigoJorgePOUG19.png\",\"url\":\"https:\\\/\\\/www.dbarj.com.br\\\/wp-content\\\/uploads\\\/2019\\\/09\\\/RodrigoJorgePOUG19.png\",\"contentUrl\":\"https:\\\/\\\/www.dbarj.com.br\\\/wp-content\\\/uploads\\\/2019\\\/09\\\/RodrigoJorgePOUG19.png\",\"width\":712,\"height\":712,\"caption\":\"DBA RJ\"},\"logo\":{\"@id\":\"https:\\\/\\\/www.dbarj.com.br\\\/wp-content\\\/uploads\\\/2019\\\/09\\\/RodrigoJorgePOUG19.png\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Renomear usu\u00e1rios no Oracle 11g com DBLink de Loopback - DBA - Rodrigo Jorge - Oracle Tips and Guides","description":"Como renomear rapidamente um usu\u00e1rio no Oracle 11g atrav\u00e9s de DBLink de Loopback","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbarj.com.br\/pt-br\/2014\/11\/renomear-usuarios-oracle-11g-dblink-loopback\/","twitter_misc":{"Escrito por":"DBA RJ","Est. tempo de leitura":"4 minutos"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbarj.com.br\/pt-br\/2014\/11\/renomear-usuarios-oracle-11g-dblink-loopback\/#article","isPartOf":{"@id":"https:\/\/www.dbarj.com.br\/pt-br\/2014\/11\/renomear-usuarios-oracle-11g-dblink-loopback\/"},"author":{"name":"DBA RJ","@id":"https:\/\/www.dbarj.com.br\/pt-br\/#\/schema\/person\/28a44ca3a6633fe4156ad1ea209d40a9"},"headline":"Renomear usu\u00e1rios no Oracle 11g com DBLink de Loopback","datePublished":"2014-11-07T17:45:42+00:00","dateModified":"2016-01-14T17:11:07+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbarj.com.br\/pt-br\/2014\/11\/renomear-usuarios-oracle-11g-dblink-loopback\/"},"wordCount":517,"commentCount":0,"publisher":{"@id":"https:\/\/www.dbarj.com.br\/pt-br\/#\/schema\/person\/28a44ca3a6633fe4156ad1ea209d40a9"},"articleSection":["Oracle Database General"],"inLanguage":"pt-BR","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbarj.com.br\/pt-br\/2014\/11\/renomear-usuarios-oracle-11g-dblink-loopback\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbarj.com.br\/pt-br\/2014\/11\/renomear-usuarios-oracle-11g-dblink-loopback\/","url":"https:\/\/www.dbarj.com.br\/pt-br\/2014\/11\/renomear-usuarios-oracle-11g-dblink-loopback\/","name":"Renomear usu\u00e1rios no Oracle 11g com DBLink de Loopback - DBA - Rodrigo Jorge - Oracle Tips and Guides","isPartOf":{"@id":"https:\/\/www.dbarj.com.br\/pt-br\/#website"},"datePublished":"2014-11-07T17:45:42+00:00","dateModified":"2016-01-14T17:11:07+00:00","description":"Como renomear rapidamente um usu\u00e1rio no Oracle 11g atrav\u00e9s de DBLink de Loopback","breadcrumb":{"@id":"https:\/\/www.dbarj.com.br\/pt-br\/2014\/11\/renomear-usuarios-oracle-11g-dblink-loopback\/#breadcrumb"},"inLanguage":"pt-BR","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbarj.com.br\/pt-br\/2014\/11\/renomear-usuarios-oracle-11g-dblink-loopback\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbarj.com.br\/pt-br\/2014\/11\/renomear-usuarios-oracle-11g-dblink-loopback\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.dbarj.com.br\/pt-br\/"},{"@type":"ListItem","position":2,"name":"Renomear usu\u00e1rios no Oracle 11g com DBLink de Loopback"}]},{"@type":"WebSite","@id":"https:\/\/www.dbarj.com.br\/pt-br\/#website","url":"https:\/\/www.dbarj.com.br\/pt-br\/","name":"DBA - Rodrigo Jorge - Oracle Tips and Guides","description":"Blog about Databases, Security and High Availability","publisher":{"@id":"https:\/\/www.dbarj.com.br\/pt-br\/#\/schema\/person\/28a44ca3a6633fe4156ad1ea209d40a9"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbarj.com.br\/pt-br\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"pt-BR"},{"@type":["Person","Organization"],"@id":"https:\/\/www.dbarj.com.br\/pt-br\/#\/schema\/person\/28a44ca3a6633fe4156ad1ea209d40a9","name":"DBA RJ","image":{"@type":"ImageObject","inLanguage":"pt-BR","@id":"https:\/\/www.dbarj.com.br\/wp-content\/uploads\/2019\/09\/RodrigoJorgePOUG19.png","url":"https:\/\/www.dbarj.com.br\/wp-content\/uploads\/2019\/09\/RodrigoJorgePOUG19.png","contentUrl":"https:\/\/www.dbarj.com.br\/wp-content\/uploads\/2019\/09\/RodrigoJorgePOUG19.png","width":712,"height":712,"caption":"DBA RJ"},"logo":{"@id":"https:\/\/www.dbarj.com.br\/wp-content\/uploads\/2019\/09\/RodrigoJorgePOUG19.png"}}]}},"_links":{"self":[{"href":"https:\/\/www.dbarj.com.br\/pt-br\/wp-json\/wp\/v2\/posts\/1053","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbarj.com.br\/pt-br\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbarj.com.br\/pt-br\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbarj.com.br\/pt-br\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbarj.com.br\/pt-br\/wp-json\/wp\/v2\/comments?post=1053"}],"version-history":[{"count":0,"href":"https:\/\/www.dbarj.com.br\/pt-br\/wp-json\/wp\/v2\/posts\/1053\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbarj.com.br\/pt-br\/wp-json\/wp\/v2\/media?parent=1053"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbarj.com.br\/pt-br\/wp-json\/wp\/v2\/categories?post=1053"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbarj.com.br\/pt-br\/wp-json\/wp\/v2\/tags?post=1053"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}