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 tablespace report – SQL script
In this article I’ll share a SQL script that I probably use most often. The SQL will show Oracle database tablespace information. Many Oracle DBAs and developers use GUI tools nowadays and me also, but sometimes you need to see some extras. The SQL script will list Oracle database tablespaces including tablespace status and type, counts of files and objects and the most important – proper tablespace sizes.
Before looking into the SQL script see my notes below:
- Script should work for Oracle 10g and 11g
- “Max Size” means the maximum size of tablespaces that can be achieved including data files auto-extention.
- “TS type” column meaning: DM – Dictionary Managed; LM-UNI/SYS – Locally managed with UNIFORM/SYSTEM extent management
- “Used size” for TEMP tablespace is HWM (largest ever used size) Read more »
Oracle RMAN full backup script to disk with compression
In this article I’ll share an RMAN script to backup the entire Oracle database including archived redo logs to disk using compression. Before looking into the script see my notes below:
- Script should work for Oracle 10g and 11g
- Oracle database name = ORCL
- I use 4 RMAN channels to speed up the backup
- I backup intentionally control file manually and automatically
- I limit the size of backup sets by number of files per set and size of backup sets
- I use backup compression in RMAN script (Oracle Advanced Compression license is required)
- The average compression factor can be different but to give you an idea it can be around 7.
- This script is independent from RMAN configuration parameters
Read more »
Oracle RMAN full backup script for EMC NetWorker
In this article I’ll share an RMAN script to backup the entire Oracle database including archived redo logs to tape library using EMC NetWorker. Before looking into the script see my notes below:
- Script should work for Oracle database 10g and 11g
- Oracle database name = ORCL
- I use 2 RMAN channels to speed up the backup
- I backup intentionally control file manually and automatically
- I limit the size of backup sets by number of files per set and size of a backup set
- PARMS section includes instructions for EMC NetWorker server
- After the backup I allocate a maintenance channel crosschecking the backups
- I do not use backup compression in RMAN script, since it’s done on NetWorker server
- This script is independent from RMAN configuration parameters
- The script can be scheduled by EMC NetWorker server daily
Read more »
Oracle 12c Pluggable Database (PDB) – SQL code examples
Oracle 12c database is still not there and last time I predicted it will be released in March. Let’s see if Oracle can fix all the release issues and provide the nice and clean new major database release 12c that will greatly help enterprises in their consolidation strategies and enable them building Private Database Clouds.
With this I’ll continue my series of articles on Oracle 12c Pluggable Database (PDB) feature including SQL code. I strongly recommend first reading my previous articles on this topic to understand the rest of the material:
Oracle 12c Pluggable Database is a fundamental architectural change
Oracle 12c Pluggable Database feature insights
Oracle 12c Pluggable Database (PDB) feature hands-on experience
Oracle 12c Pluggable Database – Plug Unplug and Clone
Oracle 12c Pluggable Database (PDB) – FAQ
This time I focus on a few interesting SQL code examples. I’ll show how to create lots of empty PDBs programmatically from scratch and from the Gold images, how to open all Pluggable Databases (PDBs) automatically after CDB startup as well as to drop all PDBs. Once again I’d like to mention that the below code should work on Oracle database 12.1 Beta2. So let’s move on. Read more »
