How to install Oracle Example Schemas

Hello,

I have been looking everywhere for a simple way to install the HR, OE, etc... sample schemas. If you do not have the files already somewhere on your system, this is a pretty good way to get these schemas:

1. Download the Oracle Examples from:

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

Choose your version and operating system and click on "See All". On the next page select and download the "Examples".

2. Copy the archive to your system

3. Extract the archive, you will have an "examples" folder now.

4.  Go to examples/stage/Components/oracle.rdbms.companion/11.2.0.1.0/1/DataFiles

There you find a lot of filegroupX.jar files.

5. For the specific schema you want, extract the filegroupX.jar file:

9 - BI
10 - HR
11 - IX
12 - mksample.sql
13 - OE
14 - PM
15 - SH

For example, for the HR schema, do: unzip filegroup10.jar

This will create a "demo" folder.

6. Copy the whole "human_resources" folder to ORACLE_HOME/demo/schemas/

7. Go into the $ORACLE_HOME/demo/schema/human_resources folder

7. In your SQL-Tool (SQLPLus, SQL Developer, etc..) run the hr_main.sql as SYS-User!!! out of the human_resources folder. This will install the schema for you.

Oracle import / export Grant source database grantee does not exist in target database

Hi there,

today I was confronted with the following problem:

I had to export a schema from one database and import to another database. The schema contained grants granted to some user/grantee, who only existed in the source database. These grants should be replaces by another grantee for the target database. A normal export and import was failing with the following errors:

GRANT ON ... ON "SCHEMA"."TABLE" TO "GRANTEE"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'GRANTEE' does not exist

A solution for this scenario is:

Do the normal import and ignore the errors. Then do the import again with the sqlfile parameter:

impdp system/peBa4ci9 schemas=... dumpfile=xxx.dmp sqlfile=getddl.sql

When you run this import command, it will not execute an import. Rather it will only create the sql file which will contain all the DDL statements that the import consists of.

Once you have the file, open it and search for the grants DDL's that affect the user/grantee you want to replace. All DDL's for this will be at the same place such as:

GRANT DEBUG ON "SCHEMA"."TABLE1" TO "GRANTEE";
GRANT QUERY REWRITE ON "SCHEMA"."TABLE2" TO "GRANTEE";
GRANT ON COMMIT REFRESH ON "SCHEMA"."TABLE3" TO "GRANTEE";
GRANT REFERENCES ON "SCHEMA"."TABLE4" TO "GRANTEE";
GRANT UPDATE ON "SCHEMA"."TABLE5" TO "GRANTEE";
GRANT SELECT ON "SCHEMA"."TABLE6" TO "GRANTEE";

Replace the GRANTEE with the one you want to have in the target database and run the GRANT commands again on the target database so that the GRANTS are set correctly. FINISH :)

ORA-30372: fine grain access policy conflicts with materialized view

Hello, I was getting the following error when creating and refreshing a materialized view:

ORA-30372 -  "fine grain access policy conflicts with materialized view"
*Cause:    A fine grain access control procedure has applied a non-null policy
           to the query for the materialized view.

This error occurs, when you are creating/refreshing a materialized view on the base of a remote table which is located on another database where Virtual Private Database (VPD) is enabled.

The simplest workaround is to use the "USING TRUSTED CONSTRAINTS" keyword in the syntax:

ORA-01619: thread 1 is mounted by another instance

The following problem may appear when patching the Oracle Grid Infrastructure:

If so, the solution for the problem is the following. You need to find out which node of the RAC is failing to startup. Once you have found out, you need to run the following command on that node (probably in nomount/mount state, locally direct on the specific instance):

alter system set thread=4 scope=spfile sid=’orcl4';

The thread parameter should be one of the available threads in the cluster. You can find that information from the following query:

SQL> select thread#, sequence#, status from v$log;

Then you should  shutdown the instance and retry:

[oracle@dbnodt1 ~]$ srvctl start database -d dbtiso

Opatch version check passed for oracle home but Opatch version check failed Grid Infrastructure

Hi,

if you ever run into the problem, that during patching the Grid Infrastructure the OPatch version check fails although you have updated the OPatch in the GI-HOME correctly and the console says:

The opatch minimum version  check for patch <Patch> failed  <DB_HOME>
The opatch minimum version  check for patch ... failed  for ...
Opatch version check failed for oracle home ...
Opatch version  check failed
ERROR: update the opatch version for the failed homes and retry

And the logs say the following:

Opatch version check passed for oracle home  <$GI_HOME>
Opatch version  check failed

The problem probably that you have only updated the OPatch of the GI-HOME before the running the GI-Patch. You also have to update the OPatch of the DB-Home to the latest version before patching the GI-Home.

Grant user access to several tables easy way

Hi, if you want to grant a database user access (insert, select, delete, update) to several tables of a view, you can use the following PL/SQL snippet:

Grant SELECT / INSERT / UPDATE rights to user for all tables in a schema

The following loop can be used to grant a user privileges for all tables of a schema: