Below scripts created by myself and tested in 11gR2 RAC databases.
---------------------------------------------------------------------
-- script awrrpt_donghua.sql
---------------------------------------------------------------------
set serveroutput on
spool master_awr_control.sql
declare
cursor c is
select to_char(s.startup_time,'dd Mon "at" HH24:mi:ss') instart_fmt
, di.instance_name inst_name
, di.instance_number instance_number
, di.db_name db_name
, di.dbid dbid
, lag (s.snap_id,1,0) over (partition by di.instance_number order by s.snap_id) begin_snap_id
, s.snap_id end_snap_id
, to_char(s.begin_interval_time,'yyyymmddhh24mi') beginsnapdat
, to_char(s.end_interval_time,'yyyymmddhh24mi') endsnapdat
, s.snap_level lvl
from dba_hist_snapshot s
, dba_hist_database_instance di
,gv$instance i
,v$database d
where s.dbid = d.dbid
and di.dbid = d.dbid
and s.instance_number = i.instance_number
and di.instance_number = i.instance_number
and di.dbid = s.dbid
and di.instance_number = s.instance_number
and di.startup_time = s.startup_time
and s.begin_interval_time > trunc(sysdate -7) -- last last 7 days
order by di.db_name, i.instance_name, s.snap_id;
begin
for c1 in c
loop
if c1.begin_snap_id > 0 then
dbms_output.put_line('spool '||c1.inst_name||'_'
||c1.begin_snap_id||'_'||c1.end_snap_id||'_'||c1.beginsnapdat||'_'||c1.endsnapdat||'.html');
dbms_output.put_line('select output from table(dbms_workload_repository.awr_report_html( '||c1.dbid||','||
c1.instance_number||','||
c1.begin_snap_id||','||
c1.end_snap_id||',0 ));');
dbms_output.put_line('spool off');
end if;
end loop;
end;
/
spool off;
set heading off
set pages 50000
set linesize 1500
set trimspool on
set trimout on
set term off
set verify off;
set feedback off;
@master_awr_control.sql
exit
-----------------------------------------------------------------------------------
---- output -----------------------------------------------------------------------
-----------------------------------------------------------------------------------
orcl_10_11_201109130000_201109130100.html
orcl_11_12_201109130100_201109130200.html
orcl_12_13_201109130200_201109130300.html
orcl_13_14_201109130300_201109130400.html
orcl_14_15_201109130400_201109130500.html
orcl_15_16_201109130500_201109130601.html
orcl_16_17_201109130601_201109130700.html
orcl_17_18_201109130700_201109130800.html
orcl_18_19_201109130800_201109130900.html
orcl_19_20_201109130900_201109131000.html
orcl_20_21_201109131000_201109131100.html
orcl_21_22_201109131100_201109131200.html
orcl_22_23_201109131200_201109131300.html
Friday, September 23, 2011
Tuesday, September 13, 2011
How to manually put Log Shipping secondary database into readonly mode
Beside choose "standby mode" using SSMO Transaction Log Shipping interface, below is the T-SQL way to change the secondary mode from "restoring" to "readonly".
-- The T-SQL Script tested in SQL Server 2008 R2
-- Put in to readonly mode, suggest to temporary disable the restore agent,
-- otherwise the agent will automatically put it in "restoring" mode.
RESTORE LOG [Northwind]
WITH STANDBY = N'C:\backup2\ROLLBACK_UNDO_Northwind.BAK'
GO
-- Put back into recovery mode
RESTORE LOG [Northwind] WITH NORECOVERY
GO
-- The T-SQL Script tested in SQL Server 2008 R2
-- Put in to readonly mode, suggest to temporary disable the restore agent,
-- otherwise the agent will automatically put it in "restoring" mode.
RESTORE LOG [Northwind]
WITH STANDBY = N'C:\backup2\ROLLBACK_UNDO_Northwind.BAK'
GO
-- Put back into recovery mode
RESTORE LOG [Northwind] WITH NORECOVERY
GO
Monday, September 12, 2011
Manually install sample schema in 11gR2
[oracle@vmxdb03b schema]$ ls -l $ORACLE_HOME/assistants/dbca/templates/
total 289296
-rw-r--r--. 1 oracle oinstall 5104 Sep 5 2010 Data_Warehouse.dbc
-rwxr-xr-x. 1 oracle oinstall 21839872 Sep 7 2010 example01.dfb
-rwxr-xr-x. 1 oracle oinstall 1490944 Sep 7 2010 example.dmp
-rw-r--r--. 1 oracle oinstall 4984 Sep 5 2010 General_Purpose.dbc
-rw-r--r--. 1 oracle oinstall 11489 Feb 15 2010 New_Database.dbt
-rw-r-----. 1 oracle oinstall 11956 Aug 30 19:20 orcl.dbt
-rwxr-xr-x. 1 oracle oinstall 9748480 Sep 7 2010 Seed_Database.ctl
-rwxr-xr-x. 1 oracle oinstall 263118848 Sep 7 2010 Seed_Database.dfb
[oracle@vmxdb03b schema]$ ls -l $ORACLE_HOME/demo/schema
total 84
drwxr-xr-x. 2 oracle oinstall 4096 Aug 30 19:13 bus_intelligence
-rw-r--r--. 1 oracle oinstall 2322 Apr 3 2009 drop_sch.sql
drwxr-xr-x. 2 oracle oinstall 4096 Aug 30 19:13 human_resources
drwxr-xr-x. 2 oracle oinstall 4096 Sep 12 17:22 log
-rw-r--r--. 1 oracle oinstall 1757 Aug 30 19:15 mk_dir.sql
-rw-r--r--. 1 oracle oinstall 1757 Aug 30 19:15 mk_dir.sql.ouibak
-rw-r--r--. 1 oracle oinstall 1600 Aug 16 2006 mk_dir.sql.sbs
-rw-r--r--. 1 oracle oinstall 28267 Jul 13 2010 mkplug.sql
drwxr-xr-x. 3 oracle oinstall 4096 Aug 30 19:15 order_entry
drwxr-xr-x. 2 oracle oinstall 4096 Aug 30 19:13 sales_history
-rw-r--r--. 1 oracle oinstall 16850 Jul 28 2010 sted_mkplug.sql.dbl
[oracle@vmxdb03b schema]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema
[oracle@vmxdb03b schema]$
[oracle@vmxdb03b schema]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.2.0 Production on Mon Sep 12 17:30:02 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL> @mkplug.sql
specify password for SYS as parameter 1:
Enter value for 1: ora123
specify password for HR as parameter 2:
Enter value for 2: ora123
specify password for OE as parameter 3:
Enter value for 3: ora123
specify password for PM as parameter 4:
Enter value for 4: ora123
specify password for IX as parameter 5:
Enter value for 5: ora123
specify password for SH as parameter 6:
Enter value for 6: ora123
specify password for BI as parameter 7:
Enter value for 7: ora123
specify INPUT metadata import file as parameter 8:
Enter value for 8: example.dmp
specify INPUT database backup file for tablespace EXAMPLE as parameter 9:
Enter value for 9: example01.dfb
specify OUTPUT database file for tablespace EXAMPLE as parameter 10:
Enter value for 10: /u01/app/oracle/oradata/orcl/example01.dbf
specify OUTPUT log directory as parameter 11:
Enter value for 11: /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/
specify OUTPUT dump file directory as parameter 12:
Enter value for 12: /u01/app/oracle/product/11.2.0/dbhome_1/assistants/dbca/templates/
Sample Schemas are being plugged in ...
Connected.
TO_CHAR(SYSTIMEST
-----------------
20110912 05:31:05
1 row selected.
old 1: CREATE USER hr IDENTIFIED BY &&password_hr
new 1: CREATE USER hr IDENTIFIED BY ora123
User created.
old 1: CREATE USER oe IDENTIFIED BY &&password_oe
new 1: CREATE USER oe IDENTIFIED BY ora123
User created.
old 1: CREATE USER ix IDENTIFIED BY &&password_ix
new 1: CREATE USER ix IDENTIFIED BY ora123
User created.
old 1: CREATE USER sh IDENTIFIED BY &&password_sh
new 1: CREATE USER sh IDENTIFIED BY ora123
User created.
old 1: CREATE USER pm IDENTIFIED BY &&password_pm
new 1: CREATE USER pm IDENTIFIED BY ora123
User created.
old 1: CREATE USER bi IDENTIFIED BY &&password_bi
new 1: CREATE USER bi IDENTIFIED BY ora123
User created.
SQL> SET FEEDBACK 1
SQL> SET NUMWIDTH 10
SQL> SET LINESIZE 80
SQL> SET TRIMSPOOL ON
SQL> SET TAB OFF
SQL> SET PAGESIZE 100
SQL>
SQL> CREATE OR REPLACE DIRECTORY data_file_dir AS '/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/sales_history/';
Directory created.
SQL> CREATE OR REPLACE DIRECTORY log_file_dir AS '/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/log/';
Directory created.
SQL> CREATE OR REPLACE DIRECTORY media_dir AS '/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/product_media/';
Directory created.
SQL>
SQL> GRANT READ ON DIRECTORY media_dir TO pm;
Grant succeeded.
SQL> GRANT READ ON DIRECTORY log_file_dir TO sh;
Grant succeeded.
SQL> GRANT READ ON DIRECTORY data_file_dir TO sh;
Grant succeeded.
SQL> GRANT WRITE ON DIRECTORY log_file_dir TO sh;
Grant succeeded.
SQL> EXECUTE DBMS_DATAPUMP_UTL.REPLACE_DEFAULT_DIR;
PL/SQL procedure successfully completed.
SQL> EXECUTE ORACLE_OCM.MGMT_CONFIG_UTL.create_replace_dir_obj;
PL/SQL procedure successfully completed.
SQL>
SQL> GRANT CREATE SESSION TO hr;
Grant succeeded.
SQL> GRANT ALTER SESSION TO hr;
Grant succeeded.
SQL> GRANT CREATE DATABASE LINK TO hr;
Grant succeeded.
SQL> GRANT CREATE SEQUENCE TO hr;
Grant succeeded.
SQL> GRANT CREATE SYNONYM TO hr;
Grant succeeded.
SQL> GRANT CREATE VIEW TO hr;
Grant succeeded.
SQL> GRANT RESOURCE TO hr;
Grant succeeded.
SQL> GRANT execute ON sys.dbms_stats TO hr;
Grant succeeded.
SQL>
SQL> GRANT CREATE SESSION TO oe;
Grant succeeded.
SQL> GRANT CREATE DATABASE LINK TO oe;
Grant succeeded.
SQL> GRANT CREATE SYNONYM TO oe;
Grant succeeded.
SQL> GRANT CREATE VIEW TO oe;
Grant succeeded.
SQL> GRANT RESOURCE TO oe;
Grant succeeded.
SQL> GRANT CREATE MATERIALIZED VIEW TO oe;
Grant succeeded.
SQL> GRANT QUERY REWRITE TO oe;
Grant succeeded.
SQL> GRANT execute ON sys.dbms_stats TO oe;
Grant succeeded.
SQL>
SQL> GRANT CONNECT TO pm;
Grant succeeded.
SQL> GRANT RESOURCE TO pm;
Grant succeeded.
SQL> GRANT execute ON sys.dbms_stats TO pm;
Grant succeeded.
SQL> GRANT READ ON DIRECTORY media_dir TO pm;
Grant succeeded.
SQL>
SQL> GRANT CONNECT TO ix;
Grant succeeded.
SQL> GRANT RESOURCE TO ix;
Grant succeeded.
SQL>
SQL> GRANT aq_administrator_role TO ix;
Grant succeeded.
SQL> GRANT aq_user_role TO ix;
Grant succeeded.
SQL>
SQL> GRANT ALTER SESSION TO ix;
Grant succeeded.
SQL> GRANT CREATE CLUSTER TO ix;
Grant succeeded.
SQL> GRANT CREATE DATABASE LINK TO ix;
Grant succeeded.
SQL> GRANT CREATE SEQUENCE TO ix;
Grant succeeded.
SQL> GRANT CREATE SESSION TO ix;
Grant succeeded.
SQL> GRANT CREATE SYNONYM TO ix;
Grant succeeded.
SQL> GRANT CREATE TABLE TO ix;
Grant succeeded.
SQL> GRANT CREATE VIEW TO ix;
Grant succeeded.
SQL> GRANT CREATE CLUSTER TO ix;
Grant succeeded.
SQL> GRANT CREATE INDEXTYPE TO ix;
Grant succeeded.
SQL> GRANT CREATE OPERATOR TO ix;
Grant succeeded.
SQL> GRANT CREATE PROCEDURE TO ix;
Grant succeeded.
SQL> GRANT CREATE SEQUENCE TO ix;
Grant succeeded.
SQL> GRANT CREATE TABLE TO ix;
Grant succeeded.
SQL> GRANT CREATE TRIGGER TO ix;
Grant succeeded.
SQL> GRANT CREATE TYPE TO ix;
Grant succeeded.
SQL> GRANT CREATE SESSION TO ix;
Grant succeeded.
SQL>
SQL> GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE TO ix;
Grant succeeded.
SQL>
SQL> GRANT EXECUTE ON sys.dbms_stats TO ix;
Grant succeeded.
SQL> GRANT EXECUTE ON DBMS_AQ TO ix;
Grant succeeded.
SQL> GRANT EXECUTE ON DBMS_AQADM TO ix;
Grant succeeded.
SQL> GRANT EXECUTE ON DBMS_APPLY_ADM TO ix;
Grant succeeded.
SQL> GRANT EXECUTE ON DBMS_CAPTURE_ADM TO ix;
Grant succeeded.
SQL> GRANT EXECUTE ON DBMS_FLASHBACK TO ix;
Grant succeeded.
SQL> GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO ix;
Grant succeeded.
SQL> GRANT EXECUTE ON DBMS_STREAMS_ADM TO ix;
Grant succeeded.
SQL> GRANT SELECT ANY DICTIONARY TO ix;
Grant succeeded.
SQL>
SQL> EXECUTE DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( -
> privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ, -
> grantee => 'ix', -
> grant_option => FALSE);
PL/SQL procedure successfully completed.
SQL>
SQL> EXECUTE DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( -
> privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ, -
> grantee => 'ix', -
> grant_option => FALSE);
PL/SQL procedure successfully completed.
SQL>
SQL> GRANT CREATE SESSION TO sh;
Grant succeeded.
SQL> GRANT CREATE TABLE TO sh;
Grant succeeded.
SQL> GRANT CREATE VIEW TO sh;
Grant succeeded.
SQL> GRANT CREATE CLUSTER TO sh;
Grant succeeded.
SQL> GRANT ALTER SESSION TO sh;
Grant succeeded.
SQL> GRANT CREATE SEQUENCE TO sh;
Grant succeeded.
SQL> GRANT CREATE SYNONYM TO sh;
Grant succeeded.
SQL> GRANT CREATE DATABASE LINK TO sh;
Grant succeeded.
SQL> GRANT CREATE DIMENSION TO sh;
Grant succeeded.
SQL> GRANT QUERY REWRITE TO sh;
Grant succeeded.
SQL> GRANT CREATE MATERIALIZED VIEW TO sh;
Grant succeeded.
SQL> GRANT CREATE VIEW TO sh;
Grant succeeded.
SQL> GRANT RESOURCE TO sh;
Grant succeeded.
SQL> GRANT select_catalog_role TO sh;
Grant succeeded.
SQL> GRANT cwm_user TO sh;
GRANT cwm_user TO sh
*
ERROR at line 1:
ORA-01919: role 'CWM_USER' does not exist
SQL> GRANT execute ON sys.dbms_stats TO sh;
Grant succeeded.
SQL> rem ALTER USER sh GRANT CONNECT THROUGH olapsvr;
SQL>
SQL> GRANT CREATE SESSION TO bi;
Grant succeeded.
SQL> GRANT CREATE TABLE TO bi;
Grant succeeded.
SQL> GRANT CREATE VIEW TO bi;
Grant succeeded.
SQL> GRANT CREATE CLUSTER TO bi;
Grant succeeded.
SQL> GRANT ALTER SESSION TO bi;
Grant succeeded.
SQL> GRANT CREATE SEQUENCE TO bi;
Grant succeeded.
SQL> GRANT CREATE SYNONYM TO bi;
Grant succeeded.
SQL> GRANT CREATE DATABASE LINK TO bi;
Grant succeeded.
SQL> GRANT RESOURCE TO bi;
Grant succeeded.
SQL>
SQL> --
SQL> -- Restoring database file backup
SQL> -- (Using RMAN works in OMF, OCFS, raw devices and in normal file systems)
SQL> --
SQL>
SQL>
SQL> set echo off;
TO_CHAR(SYSTIMEST
-----------------
20110912 05:31:05
1 row selected.
old 30: dbms_backup_restore.restoreDataFileTo(data_file_id,'&data_file_name');
new 30: dbms_backup_restore.restoreDataFileTo(data_file_id,'/u01/app/oracle/oradata/orcl/example01.dbf');
old 33: dbms_backup_restore.restoreBackupPiece('&dump_path'||'&data_file_backup', done);
new 33: dbms_backup_restore.restoreBackupPiece('/u01/app/oracle/product/11.2.0/dbhome_1/assistants/dbca/templates/'||'example01.dfb', done);
Allocating device....
Specifying datafiles...
Specifing datafiles...
Restoring ...
Restore done.
PL/SQL procedure successfully completed.
1 row selected.
TO_CHAR(SYSTIMEST
-----------------
20110912 05:31:08
1 row selected.
old 1: create or replace directory SS_IMPEXP_DIR as '&dump_path'
new 1: create or replace directory SS_IMPEXP_DIR as '/u01/app/oracle/product/11.2.0/dbhome_1/assistants/dbca/templates/'
Directory created.
Grant succeeded.
old 1: create or replace directory SS_LOGPATH_DIR as '&log_path'
new 1: create or replace directory SS_LOGPATH_DIR as '/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/'
Directory created.
Grant succeeded.
Import: Release 11.2.0.2.0 - Production on Mon Sep 12 17:31:08 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": "sys/******** AS SYSDBA" directory=SS_IMPEXP_DIR logfile=SS_LOGPATH_DIR:tts_example_imp.log dumpfile=example.dmp transport_datafiles=/u01/app/oracle/oradata/orcl/example01.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC
Processing object type TRANSPORTABLE_EXPORT/PROCACT_INSTANCE
ORA-39083: Object type PROCACT_INSTANCE failed to create with error:
ORA-06550: line 2, column 1:
PLS-00201: identifier 'XDB.DBMS_CSX_INT' must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
Failing sql is:
BEGIN
xdb.dbms_csx_int.CreateTokenTables( hextoraw('8F83C217C422721DE040E50A8B8A6824')); COMMIT; END;
Processing object type TRANSPORTABLE_EXPORT/TABLE
ORA-39083: Object type TABLE:"OE"."CUSTOMERS" failed to create with error:
ORA-39218: type check on object type "MDSYS"."SDO_GEOMETRY" failed
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_METADATA_UTIL", line 2521
ORA-01403: no data found
Failing sql is:
BEGIN SYS.DBMS_METADATA.CHECK_TYPE('OE','CUST_ADDRESS_TYP','1','61586D600A48D4094D3D3624540FCCADC8',''); SYS.DBMS_METADATA.CHECK_TYPE('MDSYS','SDO_GEOMETRY','1','61A4350ECC30F5CBFA80B7C4AADB6E918E',''); END;
ORA-39083: Object type TABLE:"OE"."WAREHOUSES" failed to create with error:
ORA-39218: type check on object type "MDSYS"."SDO_GEOMETRY" failed
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_METADATA_UTIL", line 2521
ORA-01403: no data found
Failing sql is:
BEGIN SYS.DBMS_METADATA.CHECK_TYPE('MDSYS','SDO_GEOMETRY','1','61A4350ECC30F5CBFA80B7C4AADB6E918E',''); END;
ORA-39083: Object type TABLE:"PM"."ONLINE_MEDIA" failed to create with error:
ORA-39218: type check on object type "ORDSYS"."ORDIMAGE" failed
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_METADATA_UTIL", line 2521
ORA-01403: no data found
Failing sql is:
BEGIN SYS.DBMS_METADATA.CHECK_TYPE('ORDSYS','ORDIMAGE','1','61A3B30BAEEE1354D15D5AB7A3E96F5B7E',''); SYS.DBMS_METADATA.CHECK_TYPE('ORDSYS','ORDIMAGESIGNATURE','1','61434CBB7CFC39C0B4850F15D8A163B529',''); SYS.DBMS_METADATA.CHECK_TYPE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/COMMENT
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "OE"."ORDERS" ADD CONSTRAINT "ORDERS_CUSTOMER_ID_FK" FOREIGN KEY ("CUSTOMER_ID") REFERENCES "OE"."CUSTOMERS" ("CUSTOMER_ID") ON DELETE SET NULL ENABLE
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "OE"."INVENTORIES" ADD CONSTRAINT "INVENTORIES_WAREHOUSES_FK" FOREIGN KEY ("WAREHOUSE_ID") REFERENCES "OE"."WAREHOUSES" ("WAREHOUSE_ID") ENABLE NOVALIDATE
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/TABLE
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/SECONDARY_TABLE/INDEX
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/INDEX
ORA-39083: Object type INDEX failed to create with error:
ORA-06550: line 2, column 1:
PLS-00201: identifier 'CTXSYS.DRIIMP' must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
ORA-06550: line 8, column 1:
PLS-00201: identifier 'CTXSYS.DRIIMP' must be declared
ORA-06550: line 8, column 1:
PL/SQL: Statement ignored
ORA-06550: line 10, column 1:
PLS-00201: identifier 'CTXSYS.DRIIMP' must be declared
ORA-06550: line 10, column 1:
PL/SQL: Statement ignored
ORA-06550: line 12, column 1:
PLS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCACT_INSTANCE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCDEPOBJ
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" completed with 7 error(s) at 17:31:35
Connected.
TO_CHAR(SYSTIMEST
-----------------
20110912 05:31:36
1 row selected.
Tablespace altered.
TABLESPACE_NAME FILE_NAME STATUS
--------------- ---------------------------------------------- ---------
SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf AVAILABLE
SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf AVAILABLE
UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf AVAILABLE
USERS /u01/app/oracle/oradata/orcl/users01.dbf AVAILABLE
EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf AVAILABLE
5 rows selected.
Creating sequences, views, procedures and objects privileges for HR ...
TO_CHAR(SYSTIMEST
-----------------
20110912 05:31:37
1 row selected.
Connected.
Sequence created.
Sequence created.
Sequence created.
View created.
Procedure created.
Trigger created.
Trigger altered.
Procedure created.
Trigger created.
Commit complete.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Creating synonyms, sequences, views and functions for OE ...
TO_CHAR(SYSTIMEST
-----------------
20110912 05:31:37
1 row selected.
Connected.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Sequence created.
View created.
, warehouses w
*
ERROR at line 7:
ORA-00942: table or view does not exist
, warehouses w
*
ERROR at line 7:
ORA-00942: table or view does not exist
, warehouses w
*
ERROR at line 7:
ORA-00942: table or view does not exist
View created.
FROM customers c, countries cr
*
ERROR at line 7:
ORA-00942: table or view does not exist
Function created.
customers c
*
ERROR at line 30:
ORA-00942: table or view does not exist
View created.
Creating XML schema, XML folders, OC subschema and objects privileges for OE ...
TO_CHAR(SYSTIMEST
-----------------
20110912 05:31:37
1 row selected.
specify password for OE as parameter 1:
PROMPT password for SYS as parameter 2:
Connected.
GRANT xdbadmin TO oe
*
ERROR at line 1:
ORA-01919: role 'XDBADMIN' does not exist
Grant succeeded.
Grant succeeded.
Grant succeeded.
1* GRANT alter session TO oe
Grant succeeded.
View created.
View created.
Grant succeeded.
ERROR:
ORA-01435: user does not exist
Package created.
Warning: Package Body created with compilation errors.
View created.
Warning: Package altered with compilation errors.
View altered.
Grant succeeded.
CREATE OR REPLACE TRIGGER no_dml_operations_allowed
*
ERROR at line 1:
ORA-04089: cannot create triggers on objects owned by SYS
Synonym created.
Grant succeeded.
CALL XDB_CONFIGURATION.folderDatabaseSummary()
*
ERROR at line 1:
ORA-04063: package body "SYS.XDB_CONFIGURATION" has errors
Session altered.
ERROR:
ORA-01435: user does not exist
Function created.
no rows selected
Synonym created.
Grant succeeded.
Package created.
no rows selected
Synonym created.
Grant succeeded.
Warning: Package created with compilation errors.
no rows selected
Warning: Package Body created with compilation errors.
no rows selected
Synonym created.
Grant succeeded.
Package created.
no rows selected
Warning: Package Body created with compilation errors.
no rows selected
Grant succeeded.
Synonym created.
Package created.
no rows selected
Warning: Package Body created with compilation errors.
no rows selected
Synonym created.
Grant succeeded.
IF (DBMS_XDB.existsResource(targetFolder)) THEN
*
ERROR at line 5:
ORA-06550: line 5, column 7:
PLS-00201: identifier 'DBMS_XDB.EXISTSRESOURCE' must be declared
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored
ORA-06550: line 9, column 13:
PLS-00201: identifier 'DBMS_XDB.CREATEFOLDER' must be declared
ORA-06550: line 9, column 3:
PL/SQL: Statement ignored
ORA-06550: line 11, column 13:
PLS-00201: identifier 'DBMS_XDB.CREATEFOLDER' must be declared
ORA-06550: line 11, column 3:
PL/SQL: Statement ignored
ORA-06550: line 12, column 3:
PLS-00201: identifier 'DBMS_XDB.SETACL' must be declared
ORA-06550: line 12, column 3:
PL/SQL: Statement ignored
Connected.
Directory dropped.
Directory created.
Commit complete.
Connected.
Revoke succeeded.
Connected.
Connected.
Session altered.
res := DBMS_XDB.createFolder('/home/OE/xsd');
*
ERROR at line 4:
ORA-06550: line 4, column 10:
PLS-00201: identifier 'DBMS_XDB.CREATEFOLDER' must be declared
ORA-06550: line 4, column 3:
PL/SQL: Statement ignored
ORA-06550: line 5, column 10:
PLS-00201: identifier 'DBMS_XDB.CREATEFOLDER' must be declared
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored
ORA-06550: line 6, column 10:
PLS-00201: identifier 'DBMS_XDB.CREATEFOLDER' must be declared
ORA-06550: line 6, column 3:
PL/SQL: Statement ignored
res := DBMS_XDB.createResource('/home/OE/purchaseOrder.xsd',
*
ERROR at line 4:
ORA-06550: line 4, column 10:
PLS-00201: identifier 'DBMS_XDB.CREATERESOURCE' must be declared
ORA-06550: line 4, column 3:
PL/SQL: Statement ignored
ORA-06550: line 8, column 10:
PLS-00201: identifier 'DBMS_XDB.CREATERESOURCE' must be declared
ORA-06550: line 8, column 3:
PL/SQL: Statement ignored
ORA-06550: line 14, column 10:
PLS-00201: identifier 'DBMS_XDB.CREATERESOURCE' must be declared
ORA-06550: line 14, column 3:
PL/SQL: Statement ignored
DBMS_XMLSCHEMA.registerSchema('http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd',
*
ERROR at line 2:
ORA-06550: line 2, column 3:
PLS-00201: identifier 'DBMS_XMLSCHEMA.REGISTERSCHEMA' must be declared
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored
call xdb_utilities.renameCollectionTable ('PURCHASEORDER','"XMLDATA"."LINEITEMS"."LINEITEM"','LINEITEM_TABLE')
*
ERROR at line 1:
ORA-04063: package body "SYS.XDB_UTILITIES" has errors
call xdb_utilities.renameCollectionTable ('PURCHASEORDER','"XMLDATA"."ACTIONS"."ACTION"','ACTION_TABLE')
*
ERROR at line 1:
ORA-04063: package body "SYS.XDB_UTILITIES" has errors
BEGIN
*
ERROR at line 1:
ORA-04063: package body "SYS.XDB_UTILITIES" has errors
ORA-06508: PL/SQL: could not find program unit being called:
"SYS.XDB_UTILITIES"
ORA-06512: at line 2
Connected.
revoke execute on directory SUBDIR from OE
*
ERROR at line 1:
ORA-04042: procedure, function, package, or package body does not exist
Connected.
Connected.
Revoke succeeded.
Revoke succeeded.
Revoke succeeded.
DROP PACKAGE xdb.xdb_configuration
*
ERROR at line 1:
ORA-01435: user does not exist
DROP PACKAGE xdb.xdb_namespaces
*
ERROR at line 1:
ORA-01435: user does not exist
DROP PACKAGE xdb.xdb_dom_helper
*
ERROR at line 1:
ORA-01435: user does not exist
DROP PACKAGE xdb.xdb_utilities
*
ERROR at line 1:
ORA-01435: user does not exist
DROP PACKAGE xdb.xdb_tools
*
ERROR at line 1:
ORA-01435: user does not exist
DROP TRIGGER xdb.no_dml_operations_allowed
*
ERROR at line 1:
ORA-04080: trigger 'NO_DML_OPERATIONS_ALLOWED' does not exist
DROP VIEW xdb.database_summary
*
ERROR at line 1:
ORA-00942: table or view does not exist
Connected.
Session altered.
...creating subschema OC in OE
Type created.
Type created.
Type created.
Type created.
Type created.
Type created.
Type created.
Type created.
Type created.
Type created.
Type created.
Type created.
Type created.
Type created.
Type created.
Type body created.
Type created.
Type body created.
Type created.
Type body created.
Table created.
FROM inventories i, warehouses w
*
ERROR at line 6:
ORA-00942: table or view does not exist
FROM oc_inventories i
*
ERROR at line 7:
ORA-00942: table or view does not exist
FROM customers c
*
ERROR at line 20:
ORA-00942: table or view does not exist
FROM customers c
*
ERROR at line 20:
ORA-00942: table or view does not exist
FROM customers c
*
ERROR at line 20:
ORA-00942: table or view does not exist
AS SELECT o.order_id, o.order_mode,MAKE_REF(oc_customers,o.customer_id),
*
ERROR at line 2:
ORA-00942: table or view does not exist
ON oc_orders FOR EACH ROW
*
ERROR at line 2:
ORA-00942: table or view does not exist
TABLE order_item_list OF oc_orders FOR EACH ROW
*
ERROR at line 2:
ORA-00942: table or view does not exist
Commit complete.
FROM oc_product_information o
*
ERROR at line 4:
ORA-00942: table or view does not exist
FROM oc_product_information o
*
ERROR at line 4:
ORA-00942: table or view does not exist
FROM oc_product_information o
*
ERROR at line 4:
ORA-00942: table or view does not exist
FROM oc_product_information o
*
ERROR at line 4:
ORA-00942: table or view does not exist
FROM oc_product_information o
*
ERROR at line 5:
ORA-00942: table or view does not exist
FROM oc_product_information o
*
ERROR at line 4:
ORA-00942: table or view does not exist
FROM oc_product_information o
*
ERROR at line 5:
ORA-00942: table or view does not exist
FROM oc_product_information o
*
ERROR at line 5:
ORA-00942: table or view does not exist
FROM oc_product_information o
*
ERROR at line 4:
ORA-00942: table or view does not exist
FROM oc_product_information o
*
ERROR at line 4:
ORA-00942: table or view does not exist
FROM oc_product_information o
*
ERROR at line 4:
ORA-00942: table or view does not exist
FROM oc_product_information o
*
ERROR at line 4:
ORA-00942: table or view does not exist
FROM oc_product_information o
*
ERROR at line 4:
ORA-00942: table or view does not exist
FROM oc_product_information o
*
ERROR at line 4:
ORA-00942: table or view does not exist
FROM oc_product_information o
*
ERROR at line 4:
ORA-00942: table or view does not exist
FROM oc_product_information o
*
ERROR at line 5:
ORA-00942: table or view does not exist
FROM oc_product_information o
*
ERROR at line 4:
ORA-00942: table or view does not exist
FROM oc_product_information o
*
ERROR at line 4:
ORA-00942: table or view does not exist
1 row created.
1 row created.
1 row created.
1 row created.
Type altered.
3 rows updated.
0 rows updated.
0 rows updated.
0 rows updated.
Commit complete.
Type body altered.
Type body altered.
Type body altered.
GRANT SELECT ON bombay_inventory TO bi
*
ERROR at line 1:
ORA-00942: table or view does not exist
GRANT SELECT ON customers TO bi
*
ERROR at line 1:
ORA-00942: table or view does not exist
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
GRANT SELECT ON sydney_inventory TO bi
*
ERROR at line 1:
ORA-00942: table or view does not exist
GRANT SELECT ON toronto_inventory TO bi
*
ERROR at line 1:
ORA-00942: table or view does not exist
GRANT SELECT ON warehouses TO bi
*
ERROR at line 1:
ORA-00942: table or view does not exist
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
GRANT SELECT ON customers TO pm
*
ERROR at line 1:
ORA-00942: table or view does not exist
GRANT SELECT ON warehouses TO pm
*
ERROR at line 1:
ORA-00942: table or view does not exist
Creating dimensions, materialized views, external table and object privileges for SH ...
TO_CHAR(SYSTIMEST
-----------------
20110912 05:31:38
1 row selected.
Connected.
Dimension created.
Commit complete.
PL/SQL procedure successfully completed.
no rows selected
Dimension created.
PL/SQL procedure successfully completed.
no rows selected
Dimension created.
PL/SQL procedure successfully completed.
no rows selected
Dimension created.
PL/SQL procedure successfully completed.
no rows selected
Dimension created.
PL/SQL procedure successfully completed.
no rows selected
TO_CHAR(SYSTIMEST
-----------------
20110912 05:31:40
1 row selected.
View created.
Materialized view created.
Materialized view created.
Table created.
Creating OLAP metadata ...
from all_olap_catalogs
*
ERROR at line 43:
ORA-06550: line 43, column 11:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 42, column 4:
PL/SQL: SQL Statement ignored
ORA-06550: line 45, column 4:
PLS-00201: identifier 'CWM_CLASSIFY.DROP_CATALOG' must be declared
ORA-06550: line 45, column 4:
PL/SQL: Statement ignored
ORA-06550: line 50, column 9:
PLS-00201: identifier 'CWM_EXCEPTIONS.CATALOG_NOT_FOUND' must be declared
ORA-06550: line 41, column 1:
PL/SQL: Statement ignored
ORA-06550: line 53, column 14:
PLS-00201: identifier 'CWM_CLASSIFY.CREATE_CATALOG' must be declared
ORA-06550: line 53, column 2:
PL/SQL: Statement ignored
ORA-06550: line 55, column 2:
PLS-00201: identifier 'CWM_UTILITY.COLLECT_GARBAGE' must be declared
ORA-06550: line 55, column 2:
PL/SQL: Statement ignored
ORA-06550: line 66, column 4:
PLS-00201: identifier 'CWM_OLAP_CUBE.DROP_CUBE' must be declared
ORA-06550: line 66, column 4:
PL/SQL: Statement ignored
ORA-06550: line 69, column 9:
PLS-00201: identifier 'CWM_EXCEPTIONS.CUBE_NOT_FOUND' must be declared
ORA-0655
Commit complete.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Creating views, synonyms for BI ...
TO_CHAR(SYSTIMEST
-----------------
20110912 05:31:40
1 row selected.
specify password for BI as parameter 1:
Connected.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Commit complete.
Connected.
PL/SQL procedure successfully completed.
Connected.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Directory dropped.
Directory dropped.
mkplug.sql DONE
TO_CHAR(SYSTIMEST
-----------------
20110912 05:31:41
1 row selected.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> select count(*) from hr.employees;
COUNT(*)
----------
107
1 row selected.
SQL> select count(*) from sh.sales;
COUNT(*)
----------
918843
1 row selected.
total 289296
-rw-r--r--. 1 oracle oinstall 5104 Sep 5 2010 Data_Warehouse.dbc
-rwxr-xr-x. 1 oracle oinstall 21839872 Sep 7 2010 example01.dfb
-rwxr-xr-x. 1 oracle oinstall 1490944 Sep 7 2010 example.dmp
-rw-r--r--. 1 oracle oinstall 4984 Sep 5 2010 General_Purpose.dbc
-rw-r--r--. 1 oracle oinstall 11489 Feb 15 2010 New_Database.dbt
-rw-r-----. 1 oracle oinstall 11956 Aug 30 19:20 orcl.dbt
-rwxr-xr-x. 1 oracle oinstall 9748480 Sep 7 2010 Seed_Database.ctl
-rwxr-xr-x. 1 oracle oinstall 263118848 Sep 7 2010 Seed_Database.dfb
[oracle@vmxdb03b schema]$ ls -l $ORACLE_HOME/demo/schema
total 84
drwxr-xr-x. 2 oracle oinstall 4096 Aug 30 19:13 bus_intelligence
-rw-r--r--. 1 oracle oinstall 2322 Apr 3 2009 drop_sch.sql
drwxr-xr-x. 2 oracle oinstall 4096 Aug 30 19:13 human_resources
drwxr-xr-x. 2 oracle oinstall 4096 Sep 12 17:22 log
-rw-r--r--. 1 oracle oinstall 1757 Aug 30 19:15 mk_dir.sql
-rw-r--r--. 1 oracle oinstall 1757 Aug 30 19:15 mk_dir.sql.ouibak
-rw-r--r--. 1 oracle oinstall 1600 Aug 16 2006 mk_dir.sql.sbs
-rw-r--r--. 1 oracle oinstall 28267 Jul 13 2010 mkplug.sql
drwxr-xr-x. 3 oracle oinstall 4096 Aug 30 19:15 order_entry
drwxr-xr-x. 2 oracle oinstall 4096 Aug 30 19:13 sales_history
-rw-r--r--. 1 oracle oinstall 16850 Jul 28 2010 sted_mkplug.sql.dbl
[oracle@vmxdb03b schema]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema
[oracle@vmxdb03b schema]$
[oracle@vmxdb03b schema]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.2.0 Production on Mon Sep 12 17:30:02 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL> @mkplug.sql
specify password for SYS as parameter 1:
Enter value for 1: ora123
specify password for HR as parameter 2:
Enter value for 2: ora123
specify password for OE as parameter 3:
Enter value for 3: ora123
specify password for PM as parameter 4:
Enter value for 4: ora123
specify password for IX as parameter 5:
Enter value for 5: ora123
specify password for SH as parameter 6:
Enter value for 6: ora123
specify password for BI as parameter 7:
Enter value for 7: ora123
specify INPUT metadata import file as parameter 8:
Enter value for 8: example.dmp
specify INPUT database backup file for tablespace EXAMPLE as parameter 9:
Enter value for 9: example01.dfb
specify OUTPUT database file for tablespace EXAMPLE as parameter 10:
Enter value for 10: /u01/app/oracle/oradata/orcl/example01.dbf
specify OUTPUT log directory as parameter 11:
Enter value for 11: /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/
specify OUTPUT dump file directory as parameter 12:
Enter value for 12: /u01/app/oracle/product/11.2.0/dbhome_1/assistants/dbca/templates/
Sample Schemas are being plugged in ...
Connected.
TO_CHAR(SYSTIMEST
-----------------
20110912 05:31:05
1 row selected.
old 1: CREATE USER hr IDENTIFIED BY &&password_hr
new 1: CREATE USER hr IDENTIFIED BY ora123
User created.
old 1: CREATE USER oe IDENTIFIED BY &&password_oe
new 1: CREATE USER oe IDENTIFIED BY ora123
User created.
old 1: CREATE USER ix IDENTIFIED BY &&password_ix
new 1: CREATE USER ix IDENTIFIED BY ora123
User created.
old 1: CREATE USER sh IDENTIFIED BY &&password_sh
new 1: CREATE USER sh IDENTIFIED BY ora123
User created.
old 1: CREATE USER pm IDENTIFIED BY &&password_pm
new 1: CREATE USER pm IDENTIFIED BY ora123
User created.
old 1: CREATE USER bi IDENTIFIED BY &&password_bi
new 1: CREATE USER bi IDENTIFIED BY ora123
User created.
SQL> SET FEEDBACK 1
SQL> SET NUMWIDTH 10
SQL> SET LINESIZE 80
SQL> SET TRIMSPOOL ON
SQL> SET TAB OFF
SQL> SET PAGESIZE 100
SQL>
SQL> CREATE OR REPLACE DIRECTORY data_file_dir AS '/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/sales_history/';
Directory created.
SQL> CREATE OR REPLACE DIRECTORY log_file_dir AS '/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/log/';
Directory created.
SQL> CREATE OR REPLACE DIRECTORY media_dir AS '/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/product_media/';
Directory created.
SQL>
SQL> GRANT READ ON DIRECTORY media_dir TO pm;
Grant succeeded.
SQL> GRANT READ ON DIRECTORY log_file_dir TO sh;
Grant succeeded.
SQL> GRANT READ ON DIRECTORY data_file_dir TO sh;
Grant succeeded.
SQL> GRANT WRITE ON DIRECTORY log_file_dir TO sh;
Grant succeeded.
SQL> EXECUTE DBMS_DATAPUMP_UTL.REPLACE_DEFAULT_DIR;
PL/SQL procedure successfully completed.
SQL> EXECUTE ORACLE_OCM.MGMT_CONFIG_UTL.create_replace_dir_obj;
PL/SQL procedure successfully completed.
SQL>
SQL> GRANT CREATE SESSION TO hr;
Grant succeeded.
SQL> GRANT ALTER SESSION TO hr;
Grant succeeded.
SQL> GRANT CREATE DATABASE LINK TO hr;
Grant succeeded.
SQL> GRANT CREATE SEQUENCE TO hr;
Grant succeeded.
SQL> GRANT CREATE SYNONYM TO hr;
Grant succeeded.
SQL> GRANT CREATE VIEW TO hr;
Grant succeeded.
SQL> GRANT RESOURCE TO hr;
Grant succeeded.
SQL> GRANT execute ON sys.dbms_stats TO hr;
Grant succeeded.
SQL>
SQL> GRANT CREATE SESSION TO oe;
Grant succeeded.
SQL> GRANT CREATE DATABASE LINK TO oe;
Grant succeeded.
SQL> GRANT CREATE SYNONYM TO oe;
Grant succeeded.
SQL> GRANT CREATE VIEW TO oe;
Grant succeeded.
SQL> GRANT RESOURCE TO oe;
Grant succeeded.
SQL> GRANT CREATE MATERIALIZED VIEW TO oe;
Grant succeeded.
SQL> GRANT QUERY REWRITE TO oe;
Grant succeeded.
SQL> GRANT execute ON sys.dbms_stats TO oe;
Grant succeeded.
SQL>
SQL> GRANT CONNECT TO pm;
Grant succeeded.
SQL> GRANT RESOURCE TO pm;
Grant succeeded.
SQL> GRANT execute ON sys.dbms_stats TO pm;
Grant succeeded.
SQL> GRANT READ ON DIRECTORY media_dir TO pm;
Grant succeeded.
SQL>
SQL> GRANT CONNECT TO ix;
Grant succeeded.
SQL> GRANT RESOURCE TO ix;
Grant succeeded.
SQL>
SQL> GRANT aq_administrator_role TO ix;
Grant succeeded.
SQL> GRANT aq_user_role TO ix;
Grant succeeded.
SQL>
SQL> GRANT ALTER SESSION TO ix;
Grant succeeded.
SQL> GRANT CREATE CLUSTER TO ix;
Grant succeeded.
SQL> GRANT CREATE DATABASE LINK TO ix;
Grant succeeded.
SQL> GRANT CREATE SEQUENCE TO ix;
Grant succeeded.
SQL> GRANT CREATE SESSION TO ix;
Grant succeeded.
SQL> GRANT CREATE SYNONYM TO ix;
Grant succeeded.
SQL> GRANT CREATE TABLE TO ix;
Grant succeeded.
SQL> GRANT CREATE VIEW TO ix;
Grant succeeded.
SQL> GRANT CREATE CLUSTER TO ix;
Grant succeeded.
SQL> GRANT CREATE INDEXTYPE TO ix;
Grant succeeded.
SQL> GRANT CREATE OPERATOR TO ix;
Grant succeeded.
SQL> GRANT CREATE PROCEDURE TO ix;
Grant succeeded.
SQL> GRANT CREATE SEQUENCE TO ix;
Grant succeeded.
SQL> GRANT CREATE TABLE TO ix;
Grant succeeded.
SQL> GRANT CREATE TRIGGER TO ix;
Grant succeeded.
SQL> GRANT CREATE TYPE TO ix;
Grant succeeded.
SQL> GRANT CREATE SESSION TO ix;
Grant succeeded.
SQL>
SQL> GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE TO ix;
Grant succeeded.
SQL>
SQL> GRANT EXECUTE ON sys.dbms_stats TO ix;
Grant succeeded.
SQL> GRANT EXECUTE ON DBMS_AQ TO ix;
Grant succeeded.
SQL> GRANT EXECUTE ON DBMS_AQADM TO ix;
Grant succeeded.
SQL> GRANT EXECUTE ON DBMS_APPLY_ADM TO ix;
Grant succeeded.
SQL> GRANT EXECUTE ON DBMS_CAPTURE_ADM TO ix;
Grant succeeded.
SQL> GRANT EXECUTE ON DBMS_FLASHBACK TO ix;
Grant succeeded.
SQL> GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO ix;
Grant succeeded.
SQL> GRANT EXECUTE ON DBMS_STREAMS_ADM TO ix;
Grant succeeded.
SQL> GRANT SELECT ANY DICTIONARY TO ix;
Grant succeeded.
SQL>
SQL> EXECUTE DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( -
> privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ, -
> grantee => 'ix', -
> grant_option => FALSE);
PL/SQL procedure successfully completed.
SQL>
SQL> EXECUTE DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( -
> privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ, -
> grantee => 'ix', -
> grant_option => FALSE);
PL/SQL procedure successfully completed.
SQL>
SQL> GRANT CREATE SESSION TO sh;
Grant succeeded.
SQL> GRANT CREATE TABLE TO sh;
Grant succeeded.
SQL> GRANT CREATE VIEW TO sh;
Grant succeeded.
SQL> GRANT CREATE CLUSTER TO sh;
Grant succeeded.
SQL> GRANT ALTER SESSION TO sh;
Grant succeeded.
SQL> GRANT CREATE SEQUENCE TO sh;
Grant succeeded.
SQL> GRANT CREATE SYNONYM TO sh;
Grant succeeded.
SQL> GRANT CREATE DATABASE LINK TO sh;
Grant succeeded.
SQL> GRANT CREATE DIMENSION TO sh;
Grant succeeded.
SQL> GRANT QUERY REWRITE TO sh;
Grant succeeded.
SQL> GRANT CREATE MATERIALIZED VIEW TO sh;
Grant succeeded.
SQL> GRANT CREATE VIEW TO sh;
Grant succeeded.
SQL> GRANT RESOURCE TO sh;
Grant succeeded.
SQL> GRANT select_catalog_role TO sh;
Grant succeeded.
SQL> GRANT cwm_user TO sh;
GRANT cwm_user TO sh
*
ERROR at line 1:
ORA-01919: role 'CWM_USER' does not exist
SQL> GRANT execute ON sys.dbms_stats TO sh;
Grant succeeded.
SQL> rem ALTER USER sh GRANT CONNECT THROUGH olapsvr;
SQL>
SQL> GRANT CREATE SESSION TO bi;
Grant succeeded.
SQL> GRANT CREATE TABLE TO bi;
Grant succeeded.
SQL> GRANT CREATE VIEW TO bi;
Grant succeeded.
SQL> GRANT CREATE CLUSTER TO bi;
Grant succeeded.
SQL> GRANT ALTER SESSION TO bi;
Grant succeeded.
SQL> GRANT CREATE SEQUENCE TO bi;
Grant succeeded.
SQL> GRANT CREATE SYNONYM TO bi;
Grant succeeded.
SQL> GRANT CREATE DATABASE LINK TO bi;
Grant succeeded.
SQL> GRANT RESOURCE TO bi;
Grant succeeded.
SQL>
SQL> --
SQL> -- Restoring database file backup
SQL> -- (Using RMAN works in OMF, OCFS, raw devices and in normal file systems)
SQL> --
SQL>
SQL>
SQL> set echo off;
TO_CHAR(SYSTIMEST
-----------------
20110912 05:31:05
1 row selected.
old 30: dbms_backup_restore.restoreDataFileTo(data_file_id,'&data_file_name');
new 30: dbms_backup_restore.restoreDataFileTo(data_file_id,'/u01/app/oracle/oradata/orcl/example01.dbf');
old 33: dbms_backup_restore.restoreBackupPiece('&dump_path'||'&data_file_backup', done);
new 33: dbms_backup_restore.restoreBackupPiece('/u01/app/oracle/product/11.2.0/dbhome_1/assistants/dbca/templates/'||'example01.dfb', done);
Allocating device....
Specifying datafiles...
Specifing datafiles...
Restoring ...
Restore done.
PL/SQL procedure successfully completed.
1 row selected.
TO_CHAR(SYSTIMEST
-----------------
20110912 05:31:08
1 row selected.
old 1: create or replace directory SS_IMPEXP_DIR as '&dump_path'
new 1: create or replace directory SS_IMPEXP_DIR as '/u01/app/oracle/product/11.2.0/dbhome_1/assistants/dbca/templates/'
Directory created.
Grant succeeded.
old 1: create or replace directory SS_LOGPATH_DIR as '&log_path'
new 1: create or replace directory SS_LOGPATH_DIR as '/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/'
Directory created.
Grant succeeded.
Import: Release 11.2.0.2.0 - Production on Mon Sep 12 17:31:08 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": "sys/******** AS SYSDBA" directory=SS_IMPEXP_DIR logfile=SS_LOGPATH_DIR:tts_example_imp.log dumpfile=example.dmp transport_datafiles=/u01/app/oracle/oradata/orcl/example01.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC
Processing object type TRANSPORTABLE_EXPORT/PROCACT_INSTANCE
ORA-39083: Object type PROCACT_INSTANCE failed to create with error:
ORA-06550: line 2, column 1:
PLS-00201: identifier 'XDB.DBMS_CSX_INT' must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
Failing sql is:
BEGIN
xdb.dbms_csx_int.CreateTokenTables( hextoraw('8F83C217C422721DE040E50A8B8A6824')); COMMIT; END;
Processing object type TRANSPORTABLE_EXPORT/TABLE
ORA-39083: Object type TABLE:"OE"."CUSTOMERS" failed to create with error:
ORA-39218: type check on object type "MDSYS"."SDO_GEOMETRY" failed
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_METADATA_UTIL", line 2521
ORA-01403: no data found
Failing sql is:
BEGIN SYS.DBMS_METADATA.CHECK_TYPE('OE','CUST_ADDRESS_TYP','1','61586D600A48D4094D3D3624540FCCADC8',''); SYS.DBMS_METADATA.CHECK_TYPE('MDSYS','SDO_GEOMETRY','1','61A4350ECC30F5CBFA80B7C4AADB6E918E',''); END;
ORA-39083: Object type TABLE:"OE"."WAREHOUSES" failed to create with error:
ORA-39218: type check on object type "MDSYS"."SDO_GEOMETRY" failed
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_METADATA_UTIL", line 2521
ORA-01403: no data found
Failing sql is:
BEGIN SYS.DBMS_METADATA.CHECK_TYPE('MDSYS','SDO_GEOMETRY','1','61A4350ECC30F5CBFA80B7C4AADB6E918E',''); END;
ORA-39083: Object type TABLE:"PM"."ONLINE_MEDIA" failed to create with error:
ORA-39218: type check on object type "ORDSYS"."ORDIMAGE" failed
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_METADATA_UTIL", line 2521
ORA-01403: no data found
Failing sql is:
BEGIN SYS.DBMS_METADATA.CHECK_TYPE('ORDSYS','ORDIMAGE','1','61A3B30BAEEE1354D15D5AB7A3E96F5B7E',''); SYS.DBMS_METADATA.CHECK_TYPE('ORDSYS','ORDIMAGESIGNATURE','1','61434CBB7CFC39C0B4850F15D8A163B529',''); SYS.DBMS_METADATA.CHECK_TYPE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/COMMENT
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "OE"."ORDERS" ADD CONSTRAINT "ORDERS_CUSTOMER_ID_FK" FOREIGN KEY ("CUSTOMER_ID") REFERENCES "OE"."CUSTOMERS" ("CUSTOMER_ID") ON DELETE SET NULL ENABLE
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "OE"."INVENTORIES" ADD CONSTRAINT "INVENTORIES_WAREHOUSES_FK" FOREIGN KEY ("WAREHOUSE_ID") REFERENCES "OE"."WAREHOUSES" ("WAREHOUSE_ID") ENABLE NOVALIDATE
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/TABLE
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/SECONDARY_TABLE/INDEX
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/INDEX
ORA-39083: Object type INDEX failed to create with error:
ORA-06550: line 2, column 1:
PLS-00201: identifier 'CTXSYS.DRIIMP' must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
ORA-06550: line 8, column 1:
PLS-00201: identifier 'CTXSYS.DRIIMP' must be declared
ORA-06550: line 8, column 1:
PL/SQL: Statement ignored
ORA-06550: line 10, column 1:
PLS-00201: identifier 'CTXSYS.DRIIMP' must be declared
ORA-06550: line 10, column 1:
PL/SQL: Statement ignored
ORA-06550: line 12, column 1:
PLS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCACT_INSTANCE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCDEPOBJ
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" completed with 7 error(s) at 17:31:35
Connected.
TO_CHAR(SYSTIMEST
-----------------
20110912 05:31:36
1 row selected.
Tablespace altered.
TABLESPACE_NAME FILE_NAME STATUS
--------------- ---------------------------------------------- ---------
SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf AVAILABLE
SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf AVAILABLE
UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf AVAILABLE
USERS /u01/app/oracle/oradata/orcl/users01.dbf AVAILABLE
EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf AVAILABLE
5 rows selected.
Creating sequences, views, procedures and objects privileges for HR ...
TO_CHAR(SYSTIMEST
-----------------
20110912 05:31:37
1 row selected.
Connected.
Sequence created.
Sequence created.
Sequence created.
View created.
Procedure created.
Trigger created.
Trigger altered.
Procedure created.
Trigger created.
Commit complete.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Creating synonyms, sequences, views and functions for OE ...
TO_CHAR(SYSTIMEST
-----------------
20110912 05:31:37
1 row selected.
Connected.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Sequence created.
View created.
, warehouses w
*
ERROR at line 7:
ORA-00942: table or view does not exist
, warehouses w
*
ERROR at line 7:
ORA-00942: table or view does not exist
, warehouses w
*
ERROR at line 7:
ORA-00942: table or view does not exist
View created.
FROM customers c, countries cr
*
ERROR at line 7:
ORA-00942: table or view does not exist
Function created.
customers c
*
ERROR at line 30:
ORA-00942: table or view does not exist
View created.
Creating XML schema, XML folders, OC subschema and objects privileges for OE ...
TO_CHAR(SYSTIMEST
-----------------
20110912 05:31:37
1 row selected.
specify password for OE as parameter 1:
PROMPT password for SYS as parameter 2:
Connected.
GRANT xdbadmin TO oe
*
ERROR at line 1:
ORA-01919: role 'XDBADMIN' does not exist
Grant succeeded.
Grant succeeded.
Grant succeeded.
1* GRANT alter session TO oe
Grant succeeded.
View created.
View created.
Grant succeeded.
ERROR:
ORA-01435: user does not exist
Package created.
Warning: Package Body created with compilation errors.
View created.
Warning: Package altered with compilation errors.
View altered.
Grant succeeded.
CREATE OR REPLACE TRIGGER no_dml_operations_allowed
*
ERROR at line 1:
ORA-04089: cannot create triggers on objects owned by SYS
Synonym created.
Grant succeeded.
CALL XDB_CONFIGURATION.folderDatabaseSummary()
*
ERROR at line 1:
ORA-04063: package body "SYS.XDB_CONFIGURATION" has errors
Session altered.
ERROR:
ORA-01435: user does not exist
Function created.
no rows selected
Synonym created.
Grant succeeded.
Package created.
no rows selected
Synonym created.
Grant succeeded.
Warning: Package created with compilation errors.
no rows selected
Warning: Package Body created with compilation errors.
no rows selected
Synonym created.
Grant succeeded.
Package created.
no rows selected
Warning: Package Body created with compilation errors.
no rows selected
Grant succeeded.
Synonym created.
Package created.
no rows selected
Warning: Package Body created with compilation errors.
no rows selected
Synonym created.
Grant succeeded.
IF (DBMS_XDB.existsResource(targetFolder)) THEN
*
ERROR at line 5:
ORA-06550: line 5, column 7:
PLS-00201: identifier 'DBMS_XDB.EXISTSRESOURCE' must be declared
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored
ORA-06550: line 9, column 13:
PLS-00201: identifier 'DBMS_XDB.CREATEFOLDER' must be declared
ORA-06550: line 9, column 3:
PL/SQL: Statement ignored
ORA-06550: line 11, column 13:
PLS-00201: identifier 'DBMS_XDB.CREATEFOLDER' must be declared
ORA-06550: line 11, column 3:
PL/SQL: Statement ignored
ORA-06550: line 12, column 3:
PLS-00201: identifier 'DBMS_XDB.SETACL' must be declared
ORA-06550: line 12, column 3:
PL/SQL: Statement ignored
Connected.
Directory dropped.
Directory created.
Commit complete.
Connected.
Revoke succeeded.
Connected.
Connected.
Session altered.
res := DBMS_XDB.createFolder('/home/OE/xsd');
*
ERROR at line 4:
ORA-06550: line 4, column 10:
PLS-00201: identifier 'DBMS_XDB.CREATEFOLDER' must be declared
ORA-06550: line 4, column 3:
PL/SQL: Statement ignored
ORA-06550: line 5, column 10:
PLS-00201: identifier 'DBMS_XDB.CREATEFOLDER' must be declared
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored
ORA-06550: line 6, column 10:
PLS-00201: identifier 'DBMS_XDB.CREATEFOLDER' must be declared
ORA-06550: line 6, column 3:
PL/SQL: Statement ignored
res := DBMS_XDB.createResource('/home/OE/purchaseOrder.xsd',
*
ERROR at line 4:
ORA-06550: line 4, column 10:
PLS-00201: identifier 'DBMS_XDB.CREATERESOURCE' must be declared
ORA-06550: line 4, column 3:
PL/SQL: Statement ignored
ORA-06550: line 8, column 10:
PLS-00201: identifier 'DBMS_XDB.CREATERESOURCE' must be declared
ORA-06550: line 8, column 3:
PL/SQL: Statement ignored
ORA-06550: line 14, column 10:
PLS-00201: identifier 'DBMS_XDB.CREATERESOURCE' must be declared
ORA-06550: line 14, column 3:
PL/SQL: Statement ignored
DBMS_XMLSCHEMA.registerSchema('http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd',
*
ERROR at line 2:
ORA-06550: line 2, column 3:
PLS-00201: identifier 'DBMS_XMLSCHEMA.REGISTERSCHEMA' must be declared
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored
call xdb_utilities.renameCollectionTable ('PURCHASEORDER','"XMLDATA"."LINEITEMS"."LINEITEM"','LINEITEM_TABLE')
*
ERROR at line 1:
ORA-04063: package body "SYS.XDB_UTILITIES" has errors
call xdb_utilities.renameCollectionTable ('PURCHASEORDER','"XMLDATA"."ACTIONS"."ACTION"','ACTION_TABLE')
*
ERROR at line 1:
ORA-04063: package body "SYS.XDB_UTILITIES" has errors
BEGIN
*
ERROR at line 1:
ORA-04063: package body "SYS.XDB_UTILITIES" has errors
ORA-06508: PL/SQL: could not find program unit being called:
"SYS.XDB_UTILITIES"
ORA-06512: at line 2
Connected.
revoke execute on directory SUBDIR from OE
*
ERROR at line 1:
ORA-04042: procedure, function, package, or package body does not exist
Connected.
Connected.
Revoke succeeded.
Revoke succeeded.
Revoke succeeded.
DROP PACKAGE xdb.xdb_configuration
*
ERROR at line 1:
ORA-01435: user does not exist
DROP PACKAGE xdb.xdb_namespaces
*
ERROR at line 1:
ORA-01435: user does not exist
DROP PACKAGE xdb.xdb_dom_helper
*
ERROR at line 1:
ORA-01435: user does not exist
DROP PACKAGE xdb.xdb_utilities
*
ERROR at line 1:
ORA-01435: user does not exist
DROP PACKAGE xdb.xdb_tools
*
ERROR at line 1:
ORA-01435: user does not exist
DROP TRIGGER xdb.no_dml_operations_allowed
*
ERROR at line 1:
ORA-04080: trigger 'NO_DML_OPERATIONS_ALLOWED' does not exist
DROP VIEW xdb.database_summary
*
ERROR at line 1:
ORA-00942: table or view does not exist
Connected.
Session altered.
...creating subschema OC in OE
Type created.
Type created.
Type created.
Type created.
Type created.
Type created.
Type created.
Type created.
Type created.
Type created.
Type created.
Type created.
Type created.
Type created.
Type created.
Type body created.
Type created.
Type body created.
Type created.
Type body created.
Table created.
FROM inventories i, warehouses w
*
ERROR at line 6:
ORA-00942: table or view does not exist
FROM oc_inventories i
*
ERROR at line 7:
ORA-00942: table or view does not exist
FROM customers c
*
ERROR at line 20:
ORA-00942: table or view does not exist
FROM customers c
*
ERROR at line 20:
ORA-00942: table or view does not exist
FROM customers c
*
ERROR at line 20:
ORA-00942: table or view does not exist
AS SELECT o.order_id, o.order_mode,MAKE_REF(oc_customers,o.customer_id),
*
ERROR at line 2:
ORA-00942: table or view does not exist
ON oc_orders FOR EACH ROW
*
ERROR at line 2:
ORA-00942: table or view does not exist
TABLE order_item_list OF oc_orders FOR EACH ROW
*
ERROR at line 2:
ORA-00942: table or view does not exist
Commit complete.
FROM oc_product_information o
*
ERROR at line 4:
ORA-00942: table or view does not exist
FROM oc_product_information o
*
ERROR at line 4:
ORA-00942: table or view does not exist
FROM oc_product_information o
*
ERROR at line 4:
ORA-00942: table or view does not exist
FROM oc_product_information o
*
ERROR at line 4:
ORA-00942: table or view does not exist
FROM oc_product_information o
*
ERROR at line 5:
ORA-00942: table or view does not exist
FROM oc_product_information o
*
ERROR at line 4:
ORA-00942: table or view does not exist
FROM oc_product_information o
*
ERROR at line 5:
ORA-00942: table or view does not exist
FROM oc_product_information o
*
ERROR at line 5:
ORA-00942: table or view does not exist
FROM oc_product_information o
*
ERROR at line 4:
ORA-00942: table or view does not exist
FROM oc_product_information o
*
ERROR at line 4:
ORA-00942: table or view does not exist
FROM oc_product_information o
*
ERROR at line 4:
ORA-00942: table or view does not exist
FROM oc_product_information o
*
ERROR at line 4:
ORA-00942: table or view does not exist
FROM oc_product_information o
*
ERROR at line 4:
ORA-00942: table or view does not exist
FROM oc_product_information o
*
ERROR at line 4:
ORA-00942: table or view does not exist
FROM oc_product_information o
*
ERROR at line 4:
ORA-00942: table or view does not exist
FROM oc_product_information o
*
ERROR at line 5:
ORA-00942: table or view does not exist
FROM oc_product_information o
*
ERROR at line 4:
ORA-00942: table or view does not exist
FROM oc_product_information o
*
ERROR at line 4:
ORA-00942: table or view does not exist
1 row created.
1 row created.
1 row created.
1 row created.
Type altered.
3 rows updated.
0 rows updated.
0 rows updated.
0 rows updated.
Commit complete.
Type body altered.
Type body altered.
Type body altered.
GRANT SELECT ON bombay_inventory TO bi
*
ERROR at line 1:
ORA-00942: table or view does not exist
GRANT SELECT ON customers TO bi
*
ERROR at line 1:
ORA-00942: table or view does not exist
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
GRANT SELECT ON sydney_inventory TO bi
*
ERROR at line 1:
ORA-00942: table or view does not exist
GRANT SELECT ON toronto_inventory TO bi
*
ERROR at line 1:
ORA-00942: table or view does not exist
GRANT SELECT ON warehouses TO bi
*
ERROR at line 1:
ORA-00942: table or view does not exist
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
GRANT SELECT ON customers TO pm
*
ERROR at line 1:
ORA-00942: table or view does not exist
GRANT SELECT ON warehouses TO pm
*
ERROR at line 1:
ORA-00942: table or view does not exist
Creating dimensions, materialized views, external table and object privileges for SH ...
TO_CHAR(SYSTIMEST
-----------------
20110912 05:31:38
1 row selected.
Connected.
Dimension created.
Commit complete.
PL/SQL procedure successfully completed.
no rows selected
Dimension created.
PL/SQL procedure successfully completed.
no rows selected
Dimension created.
PL/SQL procedure successfully completed.
no rows selected
Dimension created.
PL/SQL procedure successfully completed.
no rows selected
Dimension created.
PL/SQL procedure successfully completed.
no rows selected
TO_CHAR(SYSTIMEST
-----------------
20110912 05:31:40
1 row selected.
View created.
Materialized view created.
Materialized view created.
Table created.
Creating OLAP metadata ...
from all_olap_catalogs
*
ERROR at line 43:
ORA-06550: line 43, column 11:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 42, column 4:
PL/SQL: SQL Statement ignored
ORA-06550: line 45, column 4:
PLS-00201: identifier 'CWM_CLASSIFY.DROP_CATALOG' must be declared
ORA-06550: line 45, column 4:
PL/SQL: Statement ignored
ORA-06550: line 50, column 9:
PLS-00201: identifier 'CWM_EXCEPTIONS.CATALOG_NOT_FOUND' must be declared
ORA-06550: line 41, column 1:
PL/SQL: Statement ignored
ORA-06550: line 53, column 14:
PLS-00201: identifier 'CWM_CLASSIFY.CREATE_CATALOG' must be declared
ORA-06550: line 53, column 2:
PL/SQL: Statement ignored
ORA-06550: line 55, column 2:
PLS-00201: identifier 'CWM_UTILITY.COLLECT_GARBAGE' must be declared
ORA-06550: line 55, column 2:
PL/SQL: Statement ignored
ORA-06550: line 66, column 4:
PLS-00201: identifier 'CWM_OLAP_CUBE.DROP_CUBE' must be declared
ORA-06550: line 66, column 4:
PL/SQL: Statement ignored
ORA-06550: line 69, column 9:
PLS-00201: identifier 'CWM_EXCEPTIONS.CUBE_NOT_FOUND' must be declared
ORA-0655
Commit complete.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Creating views, synonyms for BI ...
TO_CHAR(SYSTIMEST
-----------------
20110912 05:31:40
1 row selected.
specify password for BI as parameter 1:
Connected.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Commit complete.
Connected.
PL/SQL procedure successfully completed.
Connected.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Directory dropped.
Directory dropped.
mkplug.sql DONE
TO_CHAR(SYSTIMEST
-----------------
20110912 05:31:41
1 row selected.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> select count(*) from hr.employees;
COUNT(*)
----------
107
1 row selected.
SQL> select count(*) from sh.sales;
COUNT(*)
----------
918843
1 row selected.
Workaround to let non-oracle account access datapump (expdp) dumpfiles
donghua:donghua is my application account, and it's able to read dump files and delete if necessary
[root@vmxdb01 ~]# rm -rf /u01/dumpfiles
[root@vmxdb01 ~]# mkdir /u01/dumpfiles
[root@vmxdb01 ~]# chown oracle:donghua /u01/dumpfiles
[root@vmxdb01 ~]# chmod 770 /u01/dumpfiles
[root@vmxdb01 ~]# chmod g+s /u01/dumpfiles
[root@vmxdb01 ~]# ls -ld /u01/dumpfiles
drwxrws---. 2 oracle donghua 4096 Sep 12 12:37 /u01/dumpfiles
[root@vmxdb01 ~]# exit
logout
[oracle@vmxdb01 u01]$ expdp donghua/donghua@orcl directory=dumpdir dumpfile=test1.dmp logfile=test1.log REUSE_DUMPFILES=y
Export: Release 11.2.0.2.0 - Production on Mon Sep 12 12:38:58 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "DONGHUA"."SYS_EXPORT_SCHEMA_01": donghua/********@orcl directory=dumpdir dumpfile=test1.dmp logfile=test1.log REUSE_DUMPFILES=y
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 4 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
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/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
. . exported "DONGHUA"."TBL_C" 913.3 KB 9997 rows
. . exported "DONGHUA"."TBL_P" 874.0 KB 9997 rows
Master table "DONGHUA"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DONGHUA.SYS_EXPORT_SCHEMA_01 is:
/u01/dumpfiles/test1.dmp
Job "DONGHUA"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:39:17
[oracle@vmxdb01 u01]$ ls -l /u01/dumpfiles
total 2004
-rw-r-----. 1 oracle donghua 2048000 Sep 12 12:39 test1.dmp
-rw-r--r--. 1 oracle donghua 1993 Sep 12 12:39 test1.log
[oracle@vmxdb01 u01]$ su -
Password:
[root@vmxdb01 ~]# su - donghua
[donghua@vmxdb01 ~]$ cd /u01/dumpfiles
[donghua@vmxdb01 dumpfiles]$ ls
test1.dmp test1.log
[donghua@vmxdb01 dumpfiles]$ file test1.dmp
test1.dmp: DBase 3 data file (1728092032 records)
[donghua@vmxdb01 dumpfiles]$ rm test1.dmp
rm: remove write-protected regular file `test1.dmp'? y
[donghua@vmxdb01 dumpfiles]$ rm test1.log
rm: remove write-protected regular file `test1.log'? y
[donghua@vmxdb01 dumpfiles]$ ls -l
total 0
[donghua@vmxdb01 dumpfiles]$ exit
logout
[root@vmxdb01 ~]# exit
logout
[root@vmxdb01 ~]# rm -rf /u01/dumpfiles
[root@vmxdb01 ~]# mkdir /u01/dumpfiles
[root@vmxdb01 ~]# chown oracle:donghua /u01/dumpfiles
[root@vmxdb01 ~]# chmod 770 /u01/dumpfiles
[root@vmxdb01 ~]# chmod g+s /u01/dumpfiles
[root@vmxdb01 ~]# ls -ld /u01/dumpfiles
drwxrws---. 2 oracle donghua 4096 Sep 12 12:37 /u01/dumpfiles
[root@vmxdb01 ~]# exit
logout
[oracle@vmxdb01 u01]$ expdp donghua/donghua@orcl directory=dumpdir dumpfile=test1.dmp logfile=test1.log REUSE_DUMPFILES=y
Export: Release 11.2.0.2.0 - Production on Mon Sep 12 12:38:58 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "DONGHUA"."SYS_EXPORT_SCHEMA_01": donghua/********@orcl directory=dumpdir dumpfile=test1.dmp logfile=test1.log REUSE_DUMPFILES=y
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 4 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
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/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
. . exported "DONGHUA"."TBL_C" 913.3 KB 9997 rows
. . exported "DONGHUA"."TBL_P" 874.0 KB 9997 rows
Master table "DONGHUA"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DONGHUA.SYS_EXPORT_SCHEMA_01 is:
/u01/dumpfiles/test1.dmp
Job "DONGHUA"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:39:17
[oracle@vmxdb01 u01]$ ls -l /u01/dumpfiles
total 2004
-rw-r-----. 1 oracle donghua 2048000 Sep 12 12:39 test1.dmp
-rw-r--r--. 1 oracle donghua 1993 Sep 12 12:39 test1.log
[oracle@vmxdb01 u01]$ su -
Password:
[root@vmxdb01 ~]# su - donghua
[donghua@vmxdb01 ~]$ cd /u01/dumpfiles
[donghua@vmxdb01 dumpfiles]$ ls
test1.dmp test1.log
[donghua@vmxdb01 dumpfiles]$ file test1.dmp
test1.dmp: DBase 3 data file (1728092032 records)
[donghua@vmxdb01 dumpfiles]$ rm test1.dmp
rm: remove write-protected regular file `test1.dmp'? y
[donghua@vmxdb01 dumpfiles]$ rm test1.log
rm: remove write-protected regular file `test1.log'? y
[donghua@vmxdb01 dumpfiles]$ ls -l
total 0
[donghua@vmxdb01 dumpfiles]$ exit
logout
[root@vmxdb01 ~]# exit
logout
Subscribe to:
Posts (Atom)