How to create Sample Schemas in Oracle 11g database – ORA-19613

The Oracle Database Sample Schemas provide a common platform for examples in each release of Oracle Database. They are easy to use for testing and training purposes. All Oracle Database documentation and training materials refer to the Sample Schemas environment.

Following are Oracle Sample Schemas

– OE (Order Entry schema) – useful for dealing with matters of intermediate complexity. Many data types are available in this schema, including non-scalar data types.

– HR (Human Resources schema) – useful for introducing basic topics. An extension to this schema supports Oracle Internet Directory demos.

– PM (Product Media schema) is dedicated to multimedia data types.

– IX (Information Exchange main schema) includes a set of schemas for demonstrate Oracle Advanced Queuing capabilities

– SH (Sales History schema) is designed to allow for demos with large amounts of data. An extension to this schema provides support for advanced analytic processing.

– SCOTT – old-fashioned famous schema with its two prominent tables EMP and DEPT that is used by Oracle for many years.

– BI – includes only synonyms on SH schema

With this simplicity I personally found difficulties in creating Sample Schemas in Oracle 11g database. In fact I could not do it in a standard way with Oracle installation binaries before 11.2.0.3. If somebody experienced similar issues, I give below the way how to install Sample Schemas manually. Read more »

Oracle database 12c wish list

In spite I’m an Oracle database expert I work also partly with Microsoft SQL Server and Sybase.
This gives me the opportunity to compare different RDBMSs and on the other hand identify weak areas in Oracle compared to others.

This article is about missing functionality or features that probably every Oracle DBA would like to have in the future Oracle database releases (Oracle Server 12c for example). And I’ll start with the database access control that is not so flexible on Oracle as in Microsoft SQL Server or Sybase. Read more »

How to export public database objects with Oracle Data Pump

Neither original Oracle Export (exp) utility nor Data Pump (expdp) can capture related public Oracle database objects during schema(s) export. During the schema(s) copy or refresh you have to transfer them to the target database manually BEFORE the import to avoid related import errors. These public objects are: Read more »

Oracle database restrictions and workarounds at daily DBA work

Oracle introduces hundreds of new database features in every database release. At the same time some tiny small restrictions in the code exist for a long time that bother database developers and DBAs at their daily work. Below I explore some of the common Oracle restrictions I often encounter during database administration. I give the workarounds for most of them and compare some with Microsoft SQL Server implementations. Read more »

Consider security using One Big Application User model

Oracle recommends that, where possible, you build applications in which application users are database users. In this way, you can leverage the security mechanisms of the database.

Unfortunately from my experience for a lot of commercial packaged software, application users are not database users. For these applications, multiple users authenticate themselves to the application, and the application then connects to the database as a single, highly-privileged schema user containing all the database objects. This is known as so-called One Big Application User model. Why, in spite of Oracle recommendation, the software vendors use in development that database authentication model? Read more »

Next Page »

DBMS Blog Updates : Subscribe RSS RSS: Subscribe to Articles · Subscribe to Comments Subscribe RSS Receive site updates via email