Import / Export dump Oracle

impexp

exp system/prolha24@xe file=/backup/oracle_NOVOSIS_DSV.dmp owner=NOVOSIS_DSV
imp system/admin@home FROMUSER=NOVOSIS_DSV TOUSER=NOVOSIS_DSV file=E:\tmp\oracle.dmp full=no log=e:\tmp\imp_log.log

permissions

DROP USER NOVOSIS_DSV  CASCADE;

CREATE USER NOVOSIS_DSV IDENTIFIED BY "NOVOSIS_DSV"; 
GRANT "CONNECT" TO NOVOSIS_DSV WITH ADMIN OPTION;
GRANT "DBA" TO NOVOSIS_DSV WITH ADMIN OPTION;
ALTER USER NOVOSIS_DSV DEFAULT ROLE "CONNECT", "DBA";

DROP USER SCSICS CASCADE;
/*
CREATE USER SCSICS
  IDENTIFIED BY VALUES 'E0F648A1E9EC9E71'
  DEFAULT TABLESPACE SCSICS_DI
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;
  -- 3 Roles for SCSICS 
  GRANT CONNECT TO SCSICS;
  GRANT RESOURCE TO SCSICS;
  GRANT ROLE_USUARIO TO SCSICS WITH ADMIN OPTION;
  ALTER USER SCSICS DEFAULT ROLE ALL;
  -- 1 System Privilege for SCSICS 
  GRANT UNLIMITED TABLESPACE TO SCSICS;
*/

DROP ROLE ROLE_SCSICS;

CREATE ROLE ROLE_SCSICS NOT IDENTIFIED;

GRANT SELECT ON NOVOSIS_DSV.SEQ_TBSICSACAO TO ROLE_SCSICS;
GRANT SELECT ON NOVOSIS_DSV.SEQ_TBSICSANEXO TO ROLE_SCSICS;
GRANT SELECT ON NOVOSIS_DSV.SEQ_TBSICSARQUIVOARTIGO TO ROLE_SCSICS;
GRANT SELECT ON NOVOSIS_DSV.SEQ_TBSICSARQUIVOLEGISLACAO TO ROLE_SCSICS;
GRANT SELECT ON NOVOSIS_DSV.SEQ_TBSICSARQUIVONOTICIA TO ROLE_SCSICS;
GRANT SELECT ON NOVOSIS_DSV.SEQ_TBSICSARTIGO TO ROLE_SCSICS;
GRANT SELECT ON NOVOSIS_DSV.SEQ_TBSICSARTIGOSETOR TO ROLE_SCSICS;
GRANT SELECT ON NOVOSIS_DSV.SEQ_TBSICSCOMENTARIO TO ROLE_SCSICS;
GRANT SELECT ON NOVOSIS_DSV.SEQ_TBSICSENQUETE TO ROLE_SCSICS;
GRANT SELECT ON NOVOSIS_DSV.SEQ_TBSICSESTATISTICA TO ROLE_SCSICS;
GRANT SELECT ON NOVOSIS_DSV.SEQ_TBSICSEVENTO TO ROLE_SCSICS;
GRANT SELECT ON NOVOSIS_DSV.SEQ_TBSICSFORNECEDOR TO ROLE_SCSICS;
GRANT SELECT ON NOVOSIS_DSV.SEQ_TBSICSFORNECEDORSETOR TO ROLE_SCSICS;
GRANT SELECT ON NOVOSIS_DSV.SEQ_TBSICSFORUMCOMENTARIO TO ROLE_SCSICS;
GRANT SELECT ON NOVOSIS_DSV.SEQ_TBSICSFORUMPOSTAGEM TO ROLE_SCSICS;
GRANT SELECT ON NOVOSIS_DSV.SEQ_TBSICSGRUPOFORNECEDOR TO ROLE_SCSICS;
GRANT SELECT ON NOVOSIS_DSV.SEQ_TBSICSIMAGEMARTIGO TO ROLE_SCSICS;
GRANT SELECT ON NOVOSIS_DSV.SEQ_TBSICSIMAGEMFORNECEDOR TO ROLE_SCSICS;
GRANT SELECT ON NOVOSIS_DSV.SEQ_TBSICSIMAGEMNOTICIA TO ROLE_SCSICS;
GRANT SELECT ON NOVOSIS_DSV.SEQ_TBSICSIMAGEMPUBLICACAO TO ROLE_SCSICS;
GRANT SELECT ON NOVOSIS_DSV.SEQ_TBSICSLEGISLACAO TO ROLE_SCSICS;
GRANT SELECT ON NOVOSIS_DSV.SEQ_TBSICSMENSAGEM TO ROLE_SCSICS;
GRANT SELECT ON NOVOSIS_DSV.SEQ_TBSICSNOTICIA TO ROLE_SCSICS;
GRANT SELECT ON NOVOSIS_DSV.SEQ_TBSICSOPCAORESPOSTA TO ROLE_SCSICS;
GRANT SELECT ON NOVOSIS_DSV.SEQ_TBSICSPERFIL TO ROLE_SCSICS;
GRANT SELECT ON NOVOSIS_DSV.SEQ_TBSICSPERGUNTA TO ROLE_SCSICS;
GRANT SELECT ON NOVOSIS_DSV.SEQ_TBSICSPUBLICACAO TO ROLE_SCSICS;
GRANT SELECT ON NOVOSIS_DSV.SEQ_TBSICSPUBLICACAOSETOR TO ROLE_SCSICS;
GRANT SELECT ON NOVOSIS_DSV.SEQ_TBSICSQUESTIONARIOAVALIAC TO ROLE_SCSICS;
GRANT SELECT ON NOVOSIS_DSV.SEQ_TBSICSRELATORIO TO ROLE_SCSICS;
GRANT SELECT ON NOVOSIS_DSV.SEQ_TBSICSSERVICOPRODUTO TO ROLE_SCSICS;
GRANT SELECT ON NOVOSIS_DSV.SEQ_TBSICSSETOR TO ROLE_SCSICS;
GRANT SELECT ON NOVOSIS_DSV.SEQ_TBSICSSOBRESITE TO ROLE_SCSICS;
GRANT SELECT ON NOVOSIS_DSV.SEQ_TBSICSSOLICITARELATORIO TO ROLE_SCSICS;
GRANT SELECT ON NOVOSIS_DSV.SEQ_TBSICSSUBGRUPOFORNECEDOR TO ROLE_SCSICS;
GRANT SELECT ON NOVOSIS_DSV.SEQ_TBSISCSGPSBGPFORNECEDOR TO ROLE_SCSICS;
GRANT DELETE, INSERT, SELECT, UPDATE ON NOVOSIS_DSV.TBSICSACAO TO ROLE_SCSICS;
GRANT DELETE, INSERT, SELECT, UPDATE ON NOVOSIS_DSV.TBSICSACAOESTATISTICA TO ROLE_SCSICS;
GRANT DELETE, INSERT, SELECT, UPDATE ON NOVOSIS_DSV.TBSICSANEXO TO ROLE_SCSICS;
GRANT DELETE, INSERT, SELECT, UPDATE ON NOVOSIS_DSV.TBSICSARTIGO TO ROLE_SCSICS;
GRANT DELETE, INSERT, SELECT, UPDATE ON NOVOSIS_DSV.TBSICSARTIGOANEXO TO ROLE_SCSICS;
GRANT DELETE, INSERT, SELECT, UPDATE ON NOVOSIS_DSV.TBSICSARTIGOSETOR TO ROLE_SCSICS;
GRANT DELETE, INSERT, SELECT, UPDATE ON NOVOSIS_DSV.TBSICSCOMENTARIO TO ROLE_SCSICS;
GRANT DELETE, INSERT, SELECT, UPDATE ON NOVOSIS_DSV.TBSICSENQUETE TO ROLE_SCSICS;
GRANT DELETE, INSERT, SELECT, UPDATE ON NOVOSIS_DSV.TBSICSESTATISTICA TO ROLE_SCSICS;
GRANT DELETE, INSERT, SELECT, UPDATE ON NOVOSIS_DSV.TBSICSEVENTO TO ROLE_SCSICS;
GRANT DELETE, INSERT, SELECT, UPDATE ON NOVOSIS_DSV.TBSICSEVENTOSETOR TO ROLE_SCSICS;
GRANT DELETE, INSERT, SELECT, UPDATE ON NOVOSIS_DSV.TBSICSFORNECEDOR TO ROLE_SCSICS;
GRANT DELETE, INSERT, SELECT, UPDATE ON NOVOSIS_DSV.TBSICSFORNECEDORSETOR TO ROLE_SCSICS;
GRANT DELETE, INSERT, SELECT, UPDATE ON NOVOSIS_DSV.TBSICSFORNEGRUPOFORNE TO ROLE_SCSICS;
GRANT DELETE, INSERT, SELECT, UPDATE ON NOVOSIS_DSV.TBSICSFORUMCOMENTARIO TO ROLE_SCSICS;
GRANT DELETE, INSERT, SELECT, UPDATE ON NOVOSIS_DSV.TBSICSFORUMPOSTAGEM TO ROLE_SCSICS;
GRANT DELETE, INSERT, SELECT, UPDATE ON NOVOSIS_DSV.TBSICSFORUMSETOR TO ROLE_SCSICS;
GRANT DELETE, INSERT, SELECT, UPDATE ON NOVOSIS_DSV.TBSICSGRUPOFORNECEDOR TO ROLE_SCSICS;
GRANT DELETE, INSERT, SELECT, UPDATE ON NOVOSIS_DSV.TBSICSGRUPOSETOR TO ROLE_SCSICS;
GRANT DELETE, INSERT, SELECT, UPDATE ON NOVOSIS_DSV.TBSICSGRUPOSUBGRUPOFORNECEDOR TO ROLE_SCSICS;
GRANT DELETE, INSERT, SELECT, UPDATE ON NOVOSIS_DSV.TBSICSLAYOUTEMAIL TO ROLE_SCSICS;
GRANT DELETE, INSERT, SELECT, UPDATE ON NOVOSIS_DSV.TBSICSLEGISLACAO TO ROLE_SCSICS;
GRANT DELETE, INSERT, SELECT, UPDATE ON NOVOSIS_DSV.TBSICSLEGISLACAOSETOR TO ROLE_SCSICS;
GRANT DELETE, INSERT, SELECT, UPDATE ON NOVOSIS_DSV.TBSICSMENSAGEM TO ROLE_SCSICS;
GRANT DELETE, INSERT, SELECT, UPDATE ON NOVOSIS_DSV.TBSICSMETODOACAOESTATISTICA TO ROLE_SCSICS;
GRANT DELETE, INSERT, SELECT, UPDATE ON NOVOSIS_DSV.TBSICSNOTICIA TO ROLE_SCSICS;
GRANT DELETE, INSERT, SELECT, UPDATE ON NOVOSIS_DSV.TBSICSNOTICIAANEXO TO ROLE_SCSICS;
GRANT DELETE, INSERT, SELECT, UPDATE ON NOVOSIS_DSV.TBSICSNOTICIASETOR TO ROLE_SCSICS;
GRANT DELETE, INSERT, SELECT, UPDATE ON NOVOSIS_DSV.TBSICSOPCAORESPOSTA TO ROLE_SCSICS;
GRANT DELETE, INSERT, SELECT, UPDATE ON NOVOSIS_DSV.TBSICSPERFIL TO ROLE_SCSICS;
GRANT DELETE, INSERT, SELECT, UPDATE ON NOVOSIS_DSV.TBSICSPERFILACAO TO ROLE_SCSICS;
GRANT DELETE, INSERT, SELECT, UPDATE ON NOVOSIS_DSV.TBSICSPERGUNTA TO ROLE_SCSICS;
GRANT DELETE, INSERT, SELECT, UPDATE ON NOVOSIS_DSV.TBSICSQUESTIONARIOAVALIACAO TO ROLE_SCSICS;
GRANT DELETE, INSERT, SELECT, UPDATE ON NOVOSIS_DSV.TBSICSRELATORIO TO ROLE_SCSICS;
GRANT DELETE, INSERT, SELECT, UPDATE ON NOVOSIS_DSV.TBSICSRELATORIOSETOR TO ROLE_SCSICS;
GRANT DELETE, INSERT, SELECT, UPDATE ON NOVOSIS_DSV.TBSICSSERVICOPRODUTO TO ROLE_SCSICS;
GRANT DELETE, INSERT, SELECT, UPDATE ON NOVOSIS_DSV.TBSICSSERVICOPRODUTOFORNECEDOR TO ROLE_SCSICS;
GRANT DELETE, INSERT, SELECT, UPDATE ON NOVOSIS_DSV.TBSICSSETOR TO ROLE_SCSICS;
GRANT DELETE, INSERT, SELECT, UPDATE ON NOVOSIS_DSV.TBSICSSOBRESITE TO ROLE_SCSICS;
GRANT DELETE, INSERT, SELECT, UPDATE ON NOVOSIS_DSV.TBSICSSOLICITARELATORIO TO ROLE_SCSICS;
GRANT DELETE, INSERT, SELECT, UPDATE ON NOVOSIS_DSV.TBSICSSUBGRUPOFORNECEDOR TO ROLE_SCSICS;
GRANT DELETE, INSERT, SELECT, UPDATE ON NOVOSIS_DSV.TBSICSUSUARIO TO ROLE_SCSICS;
GRANT DELETE, INSERT, SELECT, UPDATE ON NOVOSIS_DSV.TBSICSUSUARIOSETOR TO ROLE_SCSICS;
GRANT SELECT ON SCSICS.SEQ_TBSICSACAO TO ROLE_SCSICS;
GRANT SELECT ON SCSICS.SEQ_TBSICSANEXO TO ROLE_SCSICS;
GRANT SELECT ON SCSICS.SEQ_TBSICSARQUIVOARTIGO TO ROLE_SCSICS;
GRANT SELECT ON SCSICS.SEQ_TBSICSARQUIVOLEGISLACAO TO ROLE_SCSICS;
GRANT SELECT ON SCSICS.SEQ_TBSICSARQUIVONOTICIA TO ROLE_SCSICS;
GRANT SELECT ON SCSICS.SEQ_TBSICSARTIGO TO ROLE_SCSICS;
GRANT SELECT ON SCSICS.SEQ_TBSICSARTIGOSETOR TO ROLE_SCSICS;
GRANT SELECT ON SCSICS.SEQ_TBSICSCOMENTARIO TO ROLE_SCSICS;
GRANT SELECT ON SCSICS.SEQ_TBSICSENQUETE TO ROLE_SCSICS;
GRANT SELECT ON SCSICS.SEQ_TBSICSESTATISTICA TO ROLE_SCSICS;
GRANT SELECT ON SCSICS.SEQ_TBSICSEVENTO TO ROLE_SCSICS;
GRANT SELECT ON SCSICS.SEQ_TBSICSFORNECEDOR TO ROLE_SCSICS;
GRANT SELECT ON SCSICS.SEQ_TBSICSFORNECEDORSETOR TO ROLE_SCSICS;
GRANT SELECT ON SCSICS.SEQ_TBSICSFORUMCOMENTARIO TO ROLE_SCSICS;
GRANT SELECT ON SCSICS.SEQ_TBSICSFORUMPOSTAGEM TO ROLE_SCSICS;
GRANT SELECT ON SCSICS.SEQ_TBSICSGRUPOFORNECEDOR TO ROLE_SCSICS;
GRANT SELECT ON SCSICS.SEQ_TBSICSIMAGEMARTIGO TO ROLE_SCSICS;
GRANT SELECT ON SCSICS.SEQ_TBSICSIMAGEMFORNECEDOR TO ROLE_SCSICS;
GRANT SELECT ON SCSICS.SEQ_TBSICSIMAGEMNOTICIA TO ROLE_SCSICS;
GRANT SELECT ON SCSICS.SEQ_TBSICSIMAGEMPUBLICACAO TO ROLE_SCSICS;
GRANT SELECT ON SCSICS.SEQ_TBSICSLEGISLACAO TO ROLE_SCSICS;
GRANT SELECT ON SCSICS.SEQ_TBSICSMENSAGEM TO ROLE_SCSICS;
GRANT SELECT ON SCSICS.SEQ_TBSICSNOTICIA TO ROLE_SCSICS;
GRANT SELECT ON SCSICS.SEQ_TBSICSOPCAORESPOSTA TO ROLE_SCSICS;
GRANT SELECT ON SCSICS.SEQ_TBSICSPERFIL TO ROLE_SCSICS;
GRANT SELECT ON SCSICS.SEQ_TBSICSPERGUNTA TO ROLE_SCSICS;
GRANT SELECT ON SCSICS.SEQ_TBSICSPUBLICACAO TO ROLE_SCSICS;
GRANT SELECT ON SCSICS.SEQ_TBSICSPUBLICACAOSETOR TO ROLE_SCSICS;
GRANT SELECT ON SCSICS.SEQ_TBSICSQUESTIONARIOAVALIAC TO ROLE_SCSICS;
GRANT SELECT ON SCSICS.SEQ_TBSICSRELATORIO TO ROLE_SCSICS;
GRANT SELECT ON SCSICS.SEQ_TBSICSSERVICOPRODUTO TO ROLE_SCSICS;
GRANT SELECT ON SCSICS.SEQ_TBSICSSETOR TO ROLE_SCSICS;
GRANT SELECT ON SCSICS.SEQ_TBSICSSOBRESITE TO ROLE_SCSICS;
GRANT SELECT ON SCSICS.SEQ_TBSICSSOLICITARELATORIO TO ROLE_SCSICS;
GRANT SELECT ON SCSICS.SEQ_TBSICSSUBGRUPOFORNECEDOR TO ROLE_SCSICS;
GRANT SELECT ON SCSICS.SEQ_TBSISCSGPSBGPFORNECEDOR TO ROLE_SCSICS;
GRANT DBA TO ROLE_SCSICS WITH ADMIN OPTION;
GRANT ROLE_SCSICS TO SYSTEM WITH ADMIN OPTION;

DROP TABLESPACE SCSICS_DI INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE SCSICS_DI DATAFILE 
/************ TODO **********/
  'C:\oraclexe\app\oracle\oradata\XE\SCSICS_DI' SIZE 57M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON;


DROP TABLESPACE SCSICS_DB INCLUDING CONTENTS AND DATAFILES;

CREATE TABLESPACE SCSICS_DB DATAFILE 
/************ TODO **********/
  'C:\oraclexe\app\oracle\oradata\XE\SCSICS_DB' SIZE 2901696K AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON;

CREATE UNDO TABLESPACE UNDO DATAFILE 
/************ TODO **********/
  'C:\oraclexe\app\oracle\oradata\XE\undo.dbf' SIZE 500M AUTOEXTEND ON NEXT 5M MAXSIZE 500M
ONLINE
RETENTION NOGUARANTEE
BLOCKSIZE 8K
FLASHBACK ON;