CREATE OR REPLACE DIRECTORY EXP_DIR AS ‘D:\ORACLE\EXP_DIR‘;

O Banco de dados Oracle é composto por uma ou mais unidades lógicas de armazenamento. Estas unidades lógicas são chamadas de TABLESPACES, basicamente são subdivisões do Banco de Dados, onde os segmentos (estruturas para armazenamento de dados) semelhantes são agrupados, facilitando o gerenciamento destes segmentos no Banco de Dados. Pode-se dizer que as TABLESPACES são grupos lógicos de armazenamento.

Criar a tablespace para dados:
create tablespace VS_DADOS
datafile ‘D:\ORACLE\TableSpaces\Vs_Dados.dbs’ size 100M reuse
autoextend on next 50M
default storage (initial 512K next 256K
minextents 1
maxextents unlimited
pctincrease 0)
online;

Criar a tablespace para indíces:
create tablespace VS_INDICES
datafile ‘D:\ORACLE\TableSpaces\Vs_Indices.dbs’ size 100M reuse
autoextend on next 20M
default storage (initial 512K next 256K
minextents 1
maxextents unlimited
pctincrease 0)
online;

Criar a tablespace para usuário:
create tablespace VS_USER
datafile ‘D:\ORACLE\TableSpaces\Vs_User.dbs’ size 100M reuse
autoextend on next 10M
default storage (initial 512K next 256K
minextents 1
maxextents unlimited
pctincrease 0)
online;

Antes dos comandos, uma breve introdução ao que significa Drop, Create e Grant nesta etapa.

• Drop: Realiza a exclusão do USER que já existe com uma base restaurada. Imagine nesse momento que você está excluindo a base restaurada;

• Create: Realiza o cadastro de um novo USER no SID(ORCL) do Oracle que está acessando no momento;

• Grant: Concede os direitos de acesso para os USER’s.

/************************************/
/** DROP USERS **/DROP USER VIASOFT CASCADE;
/** CREATE USERS **/ CREATE USER VIASOFT /*NOME DO USUARIO */ IDENTIFIED BY VIASOFT /*SENHA */ DEFAULT TABLESPACE VS_USER; /*TABLE PADRAO */
/** PRIVILÉGIOS **/GRANT DBA TO VIASOFT; GRANT CREATE SESSION TO VIASOFT;
/** TYPE USERS **/GRANT EXECUTE ON SYS.UTL_RECOMP TO VIASOFT;
/************************************/
DROP USER VIASOFTSYS CASCADE;
CREATE USER VIASOFTSYS IDENTIFIED BY VIASOFTSYS DEFAULT TABLESPACE VS_USER;
GRANT DBA TO VIASOFTSYS;
GRANT CREATE SESSION TO VIASOFTSYS;
GRANT EXECUTE ON SYS.UTL_RECOMP TO VIASOFTSYS;
/************************************/
DROP USER VIASOFTCTB CASCADE;
CREATE USER VIASOFTCTB IDENTIFIED BY VIASOFTCTB DEFAULT TABLESPACE VS_USER;
GRANT DBA TO VIASOFTCTB;
GRANT CREATE SESSION TO VIASOFTCTB;
GRANT EXECUTE ON SYS.UTL_RECOMP TO VIASOFTCTB;
/************************************/
DROP USER VIASOFTFISCAL CASCADE;
CREATE USER VIASOFTFISCAL IDENTIFIED BY VIASOFTFISCAL DEFAULT TABLESPACE VS_USER;
GRANT DBA TO VIASOFTFISCAL;
GRANT CREATE SESSION TO VIASOFTFISCAL;
GRANT EXECUTE ON SYS.UTL_RECOMP TO VIASOFTFISCAL;
/************************************/
DROP USER VIASOFTBASE CASCADE;
CREATE USER VIASOFTBASE IDENTIFIED BY VIASOFTBASE DEFAULT TABLESPACE VS_USER;
GRANT DBA TO VIASOFTBASE;
GRANT CREATE SESSION TO VIASOFTBASE;
GRANT EXECUTE ON SYS.UTL_RECOMP TO VIASOFTBASE;
/************************************/
DROP USER VIASOFTCP CASCADE;
CREATE USER VIASOFTCP IDENTIFIED BY VIASOFTCP DEFAULT TABLESPACE VS_USER;
GRANT DBA TO VIASOFTCP;
GRANT CREATE SESSION TO VIASOFTCP;
GRANT EXECUTE ON SYS.UTL_RECOMP TO VIASOFTCP;
/************************************/
DROP USER VIASOFTRH CASCADE;
CREATE USER VIASOFTRH IDENTIFIED BY VIASOFTRH DEFAULT TABLESPACE VS_USER;
GRANT DBA TO VIASOFTRH;
GRANT CREATE SESSION TO VIASOFTRH;
GRANT EXECUTE ON SYS.UTL_RECOMP TO VIASOFTRH;
/************************************/
DROP USER VIASOFTMCP CASCADE;
CREATE USER VIASOFTMCP IDENTIFIED BY VIASOFTMCP DEFAULT TABLESPACE VS_USER;
GRANT DBA TO VIASOFTMCP;
GRANT CREATE SESSION TO VIASOFTMCP;
GRANT EXECUTE ON SYS.UTL_RECOMP TO VIASOFTMCP;
/************************************/
DROP USER VIASOFTLOGISTICA CASCADE;
CREATE USER VIASOFTLOGISTICA IDENTIFIED BY VIASOFTLOGISTICA DEFAULT TABLESPACE VS_USER;
GRANT DBA TO VIASOFTLOGISTICA;
GRANT CREATE SESSION TO VIASOFTLOGISTICA;
GRANT EXECUTE ON SYS.UTL_RECOMP TO VIASOFTLOGISTICA;
/************************************/
DROP USER VIASOFTFIN CASCADE;
CREATE USER VIASOFTFIN IDENTIFIED BY VIASOFTFIN DEFAULT TABLESPACE VS_USER;
GRANT DBA TO VIASOFTFIN;
GRANT CREATE SESSION TO VIASOFTFIN;
GRANT EXECUTE ON SYS.UTL_RECOMP TO VIASOFTFIN;
/************************************/
DROP USER VIASOFTGP CASCADE;
CREATE USER VIASOFTGP IDENTIFIED BY VIASOFTGP DEFAULT TABLESPACE VS_USER;
GRANT DBA TO VIASOFTGP;
GRANT CREATE SESSION TO VIASOFTGP;
GRANT EXECUTE ON SYS.UTL_RECOMP TO VIASOFTGP;
/************************************/
DROP USER VIASOFTMERC CASCADE;
CREATE USER VIASOFTMERC IDENTIFIED BY VIASOFTMERC DEFAULT TABLESPACE VS_USER;
GRANT DBA TO VIASOFTMERC;
GRANT CREATE SESSION TO VIASOFTMERC;
GRANT EXECUTE ON SYS.UTL_RECOMP TO VIASOFTMERC;

GRANT CREATE SEQUENCE TO VIASOFT;

COMMIT WORK;