SH유저에게 덤프파일 복사해서 IMPORT 하기

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 12 09:38:25 2018

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

SYS@orcl>desc dba_tab_roles;

ERROR:

ORA-04043: object dba_tab_roles does not exist

SYS@orcl>alter user sh identified by sh account unlock;

alter user sh identified by sh account unlock

           *

ERROR at line 1:

ORA-01918: user 'SH' does not exist

SYS@orcl>create user sh identified by sh ;

User created.

SYS@orcl>grant connect,resource to sh;

Grant succeeded.

덤프파일 복사해서 붙여넣은 후 SH에게 해당 디렉토리에 대한 권한 부여 !!!!

SYS@orcl>grant read, write on directory reports_dir to sh;

Grant succeeded.

SYS@orcl>exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

[oracle@localhost ~]$ impdp sh/sh directory=reports_dir dumpfile=sh.dmp;

Import: Release 11.2.0.1.0 - Production on Thu Apr 12 16:40:07 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

Master table "SH"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SH"."SYS_IMPORT_FULL_01":  sh/******** directory=reports_dir dumpfile=sh.dmp 

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

ORA-39083: Object type TABLE:"SH"."SALES" failed to create with error:

ORA-00959: tablespace 'EXAMPLE' does not exist

Failing sql is:

CREATE TABLE "SH"."SALES" ("PROD_ID" NUMBER NOT NULL ENABLE, "CUST_ID" NUMBER NOT NULL ENABLE, "TIME_ID" DATE NOT NULL ENABLE, "CHANNEL_ID" NUMBER NOT NULL ENABLE, "PROMO_ID" NUMBER NOT NULL ENABLE, "QUANTITY_SOLD" NUMBER(10,2) NOT NULL ENABLE, "AMOUNT_SOLD" NUMBER(10,2) NOT NULL ENABLE) PCTFREE 5 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS  NOLOGGING  STORAGE( BUFFER_POO

ORA-39083: Object type TABLE:"SH"."COSTS" failed to create with error:

ORA-00959: tablespace 'EXAMPLE' does not exist

Failing sql is:

CREATE TABLE "SH"."COSTS" ("PROD_ID" NUMBER NOT NULL ENABLE, "TIME_ID" DATE NOT NULL ENABLE, "PROMO_ID" NUMBER NOT NULL ENABLE, "CHANNEL_ID" NUMBER NOT NULL ENABLE, "UNIT_COST" NUMBER(10,2) NOT NULL ENABLE, "UNIT_PRICE" NUMBER(10,2) NOT NULL ENABLE) PCTFREE 5 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS  NOLOGGING  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLAS

ORA-39083: Object type TABLE:"SH"."TIMES" failed to create with error:

ORA-00959: tablespace 'EXAMPLE' does not exist

Failing sql is:

CREATE TABLE "SH"."TIMES" ("TIME_ID" DATE NOT NULL ENABLE, "DAY_NAME" VARCHAR2(9 BYTE) NOT NULL ENABLE, "DAY_NUMBER_IN_WEEK" NUMBER(1,0) NOT NULL ENABLE, "DAY_NUMBER_IN_MONTH" NUMBER(2,0) NOT NULL ENABLE, "CALENDAR_WEEK_NUMBER" NUMBER(2,0) NOT NULL ENABLE, "FISCAL_WEEK_NUMBER" NUMBER(2,0) NOT NULL ENABLE, "WEEK_ENDING_DAY" DATE NOT NULL ENABLE, "WEEK_ENDING_DAY_ID" NUMBER N

ORA-39083: Object type TABLE:"SH"."PRODUCTS" failed to create with error:

ORA-00959: tablespace 'EXAMPLE' does not exist

Failing sql is:

CREATE TABLE "SH"."PRODUCTS" ("PROD_ID" NUMBER(6,0) NOT NULL ENABLE, "PROD_NAME" VARCHAR2(50 BYTE) NOT NULL ENABLE, "PROD_DESC" VARCHAR2(4000 BYTE) NOT NULL ENABLE, "PROD_SUBCATEGORY" VARCHAR2(50 BYTE) NOT NULL ENABLE, "PROD_SUBCATEGORY_ID" NUMBER NOT NULL ENABLE, "PROD_SUBCATEGORY_DESC" VARCHAR2(2000 BYTE) NOT NULL ENABLE, "PROD_CATEGORY" VARCHAR2(50 BYTE) NOT NULL ENAB

ORA-39083: Object type TABLE:"SH"."CHANNELS" failed to create with error:

ORA-00959: tablespace 'EXAMPLE' does not exist

Failing sql is:

CREATE TABLE "SH"."CHANNELS" ("CHANNEL_ID" NUMBER NOT NULL ENABLE, "CHANNEL_DESC" VARCHAR2(20 BYTE) NOT NULL ENABLE, "CHANNEL_CLASS" VARCHAR2(20 BYTE) NOT NULL ENABLE, "CHANNEL_CLASS_ID" NUMBER NOT NULL ENABLE, "CHANNEL_TOTAL" VARCHAR2(13 BYTE) NOT NULL ENABLE, "CHANNEL_TOTAL_ID" NUMBER NOT NULL ENABLE) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS

ORA-39083: Object type TABLE:"SH"."PROMOTIONS" failed to create with error:

ORA-00959: tablespace 'EXAMPLE' does not exist

Failing sql is:

CREATE TABLE "SH"."PROMOTIONS" ("PROMO_ID" NUMBER(6,0) NOT NULL ENABLE, "PROMO_NAME" VARCHAR2(30 BYTE) NOT NULL ENABLE, "PROMO_SUBCATEGORY" VARCHAR2(30 BYTE) NOT NULL ENABLE, "PROMO_SUBCATEGORY_ID" NUMBER NOT NULL ENABLE, "PROMO_CATEGORY" VARCHAR2(30 BYTE) NOT NULL ENABLE, "PROMO_CATEGORY_ID" NUMBER NOT NULL ENABLE, "PROMO_COST" NUMBER(10,2) NOT NULL ENABLE, "PROMO_BEG

ORA-39083: Object type TABLE:"SH"."CUSTOMERS" failed to create with error:

ORA-00959: tablespace 'EXAMPLE' does not exist

Failing sql is:

CREATE TABLE "SH"."CUSTOMERS" ("CUST_ID" NUMBER NOT NULL ENABLE, "CUST_FIRST_NAME" VARCHAR2(20 BYTE) NOT NULL ENABLE, "CUST_LAST_NAME" VARCHAR2(40 BYTE) NOT NULL ENABLE, "CUST_GENDER" CHAR(1 BYTE) NOT NULL ENABLE, "CUST_YEAR_OF_BIRTH" NUMBER(4,0) NOT NULL ENABLE, "CUST_MARITAL_STATUS" VARCHAR2(20 BYTE), "CUST_STREET_ADDRESS" VARCHAR2(40 BYTE) NOT NULL ENABLE, "CUST_POST

ORA-39083: Object type TABLE:"SH"."COUNTRIES" failed to create with error:

ORA-00959: tablespace 'EXAMPLE' does not exist

Failing sql is:

CREATE TABLE "SH"."COUNTRIES" ("COUNTRY_ID" NUMBER NOT NULL ENABLE, "COUNTRY_ISO_CODE" CHAR(2 BYTE) NOT NULL ENABLE, "COUNTRY_NAME" VARCHAR2(40 BYTE) NOT NULL ENABLE, "COUNTRY_SUBREGION" VARCHAR2(30 BYTE) NOT NULL ENABLE, "COUNTRY_SUBREGION_ID" NUMBER NOT NULL ENABLE, "COUNTRY_REGION" VARCHAR2(20 BYTE) NOT NULL ENABLE, "COUNTRY_REGION_ID" NUMBER NOT NULL ENABLE, "COUNTR

ORA-39083: Object type TABLE:"SH"."SUPPLEMENTARY_DEMOGRAPHICS" failed to create with error:

ORA-00959: tablespace 'EXAMPLE' does not exist

Failing sql is:

CREATE TABLE "SH"."SUPPLEMENTARY_DEMOGRAPHICS" ("CUST_ID" NUMBER NOT NULL ENABLE, "EDUCATION" VARCHAR2(21 BYTE), "OCCUPATION" VARCHAR2(21 BYTE), "HOUSEHOLD_SIZE" VARCHAR2(21 BYTE), "YRS_RESIDENCE" NUMBER, "AFFINITY_CARD" NUMBER(10,0), "BULK_PACK_DISKETTES" NUMBER(10,0), "FLAT_PANEL_MONITOR" NUMBER(10,0), "HOME_THEATER_PACKAGE" NUMBER(10,0), "BOOKKEEPING

ORA-39083: Object type TABLE:"SH"."SALES_TRANSACTIONS_EXT" failed to create with error:

ORA-06564: object DATA_FILE_DIR does not exist

Failing sql is:

CREATE TABLE "SH"."SALES_TRANSACTIONS_EXT" ("PROD_ID" NUMBER, "CUST_ID" NUMBER, "TIME_ID" DATE, "CHANNEL_ID" NUMBER, "PROMO_ID" NUMBER, "QUANTITY_SOLD" NUMBER, "AMOUNT_SOLD" NUMBER(10,2), "UNIT_COST" NUMBER(10,2), "UNIT_PRICE" NUMBER(10,2)) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY "DATA_FILE_DIR" ACCESS PARAMETERS ( RECORDS DELIMITED BY 

ORA-39083: Object type TABLE:"SH"."CAL_MONTH_SALES_MV" failed to create with error:

ORA-00959: tablespace 'EXAMPLE' does not exist

Failing sql is:

CREATE TABLE "SH"."CAL_MONTH_SALES_MV" ("CALENDAR_MONTH_DESC" VARCHAR2(8 BYTE) NOT NULL ENABLE, "DOLLARS" NUMBER) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT C

ORA-39083: Object type TABLE:"SH"."FWEEK_PSCAT_SALES_MV" failed to create with error:

ORA-00959: tablespace 'EXAMPLE' does not exist

Failing sql is:

CREATE TABLE "SH"."FWEEK_PSCAT_SALES_MV" ("WEEK_ENDING_DAY" DATE NOT NULL ENABLE, "PROD_SUBCATEGORY" VARCHAR2(50 BYTE) NOT NULL ENABLE, "DOLLARS" NUMBER, "CHANNEL_ID" NUMBER NOT NULL ENABLE, "PROMO_ID" NUMBER NOT NULL ENABLE) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXT

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "SH"."DIMENSION_EXCEPTIONS"                     0 KB       0 rows

Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/VIEW/VIEW

ORA-31685: Object type VIEW:"SH"."PROFITS" failed due to insufficient privileges. Failing sql is:

CREATE  FORCE VIEW "SH"."PROFITS" ("CHANNEL_ID", "CUST_ID", "PROD_ID", "PROMO_ID", "TIME_ID", "UNIT_COST", "UNIT_PRICE", "AMOUNT_SOLD", "QUANTITY_SOLD", "TOTAL_COST") AS SELECT

        s.channel_id,

        s.cust_id,

        s.prod_id,

        s.promo_id,

        s.time_id,

        c.unit_cost,

        c.unit_price,

        s.amount_sold,

        s.quantity_sold,

        c.unit_cost * s.quantity_sold TOTAL_C

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX

Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW

ORA-31685: Object type MATERIALIZED_VIEW:"SH"."CAL_MONTH_SALES_MV" failed due to insufficient privileges. Failing sql is:

CREATE MATERIALIZED VIEW "SH"."CAL_MONTH_SALES_MV" ("CALENDAR_MONTH_DESC", "DOLLARS") USING ("CAL_MONTH_SALES_MV", (10, 'ORCL', 2, 0, 0, "SH", "TIMES", '2018-01-18 14:57:20', 8, 74146, '2018-01-18 14:57:21', '', 1, '0208', 977068, 0, NULL, 1, "SH", "SALES", '2018-01-18 14:57:20', 33032, 74088, '2018-01-18 14:57:21', '', 1, '88', 977068, 0, NULL), 1183809, 10, ('1950-01-01 12:00:00', 21

ORA-31685: Object type MATERIALIZED_VIEW:"SH"."FWEEK_PSCAT_SALES_MV" failed due to insufficient privileges. Failing sql is:

CREATE MATERIALIZED VIEW "SH"."FWEEK_PSCAT_SALES_MV" ("WEEK_ENDING_DAY", "PROD_SUBCATEGORY", "DOLLARS", "CHANNEL_ID", "PROMO_ID") USING ("FWEEK_PSCAT_SALES_MV", (10, 'ORCL', 3, 0, 0, "SH", "PRODUCTS", '2018-01-18 14:57:21', 8, 74148, '2018-01-18 14:57:22', '', 1, '12', 977091, 0, NULL, 1, "SH", "TIMES", '2018-01-18 14:57:21', 8, 74146, '2018-01-18 14:57:22', '', 1, '82', 977091, 0, N

Processing object type SCHEMA_EXPORT/DIMENSION

ORA-39083: Object type DIMENSION failed to create with error:

ORA-00942: table or view does not exist

Failing sql is:

 CREATE DIMENSION "SH"."CUSTOMERS_DIM" LEVEL "CUSTOMER" IS ("CUSTOMERS"."CUST_ID") LEVEL "CITY" IS ("CUSTOMERS"."CUST_CITY_ID") LEVEL "STATE" IS ("CUSTOMERS"."CUST_STATE_PROVINCE_ID") LEVEL "COUNTRY" IS ("COUNTRIES"."COUNTRY_ID") LEVEL "SUBREGION" IS ("COUNTRIES"."COUNTRY_SUBREGION_ID") LEVEL "REGION" IS ("COUNTRIES"."COUNTRY_REGION_ID") LEVEL "GEOG_TOTAL" IS ("COUNTRIES"."COUNTRY_TOTAL_ID

ORA-39083: Object type DIMENSION failed to create with error:

ORA-00942: table or view does not exist

Failing sql is:

 CREATE DIMENSION "SH"."PRODUCTS_DIM" LEVEL "PRODUCT" IS ("PRODUCTS"."PROD_ID") LEVEL "SUBCATEGORY" IS ("PRODUCTS"."PROD_SUBCATEGORY_ID") LEVEL "CATEGORY" IS ("PRODUCTS"."PROD_CATEGORY_ID") LEVEL "PROD_TOTAL" IS ("PRODUCTS"."PROD_TOTAL_ID") HIERARCHY "PROD_ROLLUP" ("PRODUCT" CHILD OF "SUBCATEGORY" CHILD OF "CATEGORY" CHILD OF "PROD_TOTAL") ATTRIBUTE "PRODUCT" LEVEL "PRODUCT" DETERMINES "PR

ORA-39083: Object type DIMENSION failed to create with error:

ORA-00942: table or view does not exist

Failing sql is:

 CREATE DIMENSION "SH"."TIMES_DIM" LEVEL "DAY" IS ("TIMES"."TIME_ID") LEVEL "MONTH" IS ("TIMES"."CALENDAR_MONTH_ID") LEVEL "QUARTER" IS ("TIMES"."CALENDAR_QUARTER_ID") LEVEL "YEAR" IS ("TIMES"."CALENDAR_YEAR_ID") LEVEL "FIS_WEEK" IS ("TIMES"."WEEK_ENDING_DAY_ID") LEVEL "FIS_MONTH" IS ("TIMES"."FISCAL_MONTH_ID") LEVEL "FIS_QUARTER" IS ("TIMES"."FISCAL_QUARTER_ID") LEVEL "FIS_YEAR" IS ("TIME

ORA-39083: Object type DIMENSION failed to create with error:

ORA-00942: table or view does not exist

Failing sql is:

 CREATE DIMENSION "SH"."CHANNELS_DIM" LEVEL "CHANNEL" IS ("CHANNELS"."CHANNEL_ID") LEVEL "CHANNEL_CLASS" IS ("CHANNELS"."CHANNEL_CLASS_ID") LEVEL "CHANNEL_TOTAL" IS ("CHANNELS"."CHANNEL_TOTAL_ID") HIERARCHY "CHANNEL_ROLLUP" ("CHANNEL" CHILD OF "CHANNEL_CLASS" CHILD OF "CHANNEL_TOTAL") ATTRIBUTE "CHANNEL" LEVEL "CHANNEL" DETERMINES "CHANNELS"."CHANNEL_DESC" ATTRIBUTE "CHANNEL_CLASS" LEVEL "

ORA-39083: Object type DIMENSION failed to create with error:

ORA-00942: table or view does not exist

Failing sql is:

 CREATE DIMENSION "SH"."PROMOTIONS_DIM" LEVEL "PROMO" IS ("PROMOTIONS"."PROMO_ID") LEVEL "SUBCATEGORY" IS ("PROMOTIONS"."PROMO_SUBCATEGORY_ID") LEVEL "CATEGORY" IS ("PROMOTIONS"."PROMO_CATEGORY_ID") LEVEL "PROMO_TOTAL" IS ("PROMOTIONS"."PROMO_TOTAL_ID") HIERARCHY "PROMO_ROLLUP" ("PROMO" CHILD OF "SUBCATEGORY" CHILD OF "CATEGORY" CHILD OF "PROMO_TOTAL") ATTRIBUTE "PROMO" LEVEL "PROMO" DETER

Job "SH"."SYS_IMPORT_FULL_01" completed with 20 error(s) at 16:40:17

[oracle@localhost ~]$ sqlplus sh/sh

SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 12 16:40:36 2018

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options