Oracle

This web page contains survival notes on Oracle. As I get a chance I will place my survival notes here.

Resources

Oracle Docs
- http://docs.oracle.com - Home page for Oracles documentation.
- Oracle 12c R1 docs - http://docs.oracle.com/cd/E16655_01/index.htm
Oracle Acronyms
- XE (Express Edition)
- EE (Enterprise Edition)
Other Resources
- How to Install Oracle 12c EE - http://www.youtube.com/watch?v=juLD9T5GAck

Download & Install Oracle

Example: Oracle 12c for Windows 64bit - as of 4/22/2014
Note: I didn't see an XE version for 12c.

Steps to Download
- Go to: www.oracle.com
- Register with Oracle (if you don't have an account).
  Click "Sign In/Register" link to create a new account.
  Note: You will need to create an account with Oracle to be able to download files.
  NOTE: At one time, it took 1 to 2 days to get the email so that you can setup your account.
  The email's subject line was titled: Export Validation Hold Cleared Notification
- Hoover over the "Downloads" tab and choose "Oracle Database"
- Select - Accept License Agreement
- For Microsoft Windows x64 (64-bit) download "File 1" and "File 2" - each may take 45min to 1 hour. 2.5 Gig total or about 1.3 Gig per file.
  Examples:
  winx64_12c_database_1of2.zip
  winx64_12c_database_2of2.zip
- Once downloaded, extract the files from both zip files into one directory (not 2 directories).
  Example: winx64_12c_database_software
Steps to Install Oracle
- Run setup.exe
- Screen "Configure Security Updates" - skip this step.Uncheck the check box and leave fields empty.
- Screen "Download Software Updates" - skip this step.
- Screen "Select Installation Option" - select "Create and Configure a Database".
- Screen "System Class" - select "Desktop class"
- Screen "Specify Oracle Home User" - select "Create New Windows User"
- Screen "Typical Install Configuration"
  Leave the defaults
  Make note of the administrative password. This will be the password for the "sys" DB user.
  (Pswd: Suggest 8 or more characters with at least one capital and one numeric so you don't get Oracle's password warning messages.)
  - Make a note of the character set which you may want to use with Toad for Oracle:
    Character set: Default ( WE8MSWIN1252 )
- Note: Be very patient why installing. So of the screens may be very slow to populate.
- Installation is done when you see the popup "Database Configuration Assistant"
  Make note of the following values:
  Global Database Name: orcl
  System Identifier (SID): orcl
  EM Database Express URL: https://localhost:5500/em
- Click "Password Management" button to set the passwords for the users:
  sys
  system
- Open up your browser and go to: https://localhost:5500/em
  User: sys
  Pswd: <the password you gave in the install steps>
- Click "exit". You are finished.
- Note:
  https://thomasma-VAIO:5501/em

Windows Services:

General:
Oraclec_app_dbadmin_product_121~1.0_dbhome_1ConfigurationManager - default startup: automatic
OracleOraDB12Home1MTSRecoveryService - default startup: automatic
OracleOraDB12Home1TNSListener - default startup: automatic
For Each DB:
OracleJobScheduler<DBName> - default startup: disabled.
OracleService<DBName> - default startup: automatic
OracleVssWriter<DBName> - default startup: automatic (note: start the OracleService for the DB first)

My Notes

Default Admin account & password
- Username: system
  Password: manager
- Username: sys

Terms

AWR (Automatic Workload Repository) report - performance gathering and reporting tool.
- http://www.oracle-base.com/articles/10g/automatic-workload-repository-10g.php - home website.
- The AWR is used to collect performance statistics including:
  •Wait events used to identify performance problems.
  •Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.
  •Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view.
  •Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.
  •Object usage statistics.
  •Resource intensive SQL statements.
- History of performance gathering and reporting tools over the years.
  - Originally the UTLBSTAT/UTLESTAT scripts were used to monitor performance metrics.
  - Oracle8i introduced the Statspack functionality which Oracle9i extended.
  - Oracle 10g statspack has evolved into the Automatic Workload Repository (AWR).

Oracle specific SQL Notes

Topic

Notes

Oracle SQL Plus

Connect to a DB:

connect <username>/<pswd> @<DBName or Connect Identifier>

TAB Table

This table contains a list of all of the tables in a database.

List Tables in a Database

List Tables in Database

select * from TAB;

List Structure

List the Structure of a Table

desc [table name]; //List the schema (structure) of a table.
ex: desc TAB;

For Oracle: (desc TAB;)

TNAME	not null	varchar2 (30)
TABTYPE	not nul	varchar2(7)
CLUSTERID		number

Operators

% - wildcard for any number of characters
_ - wildcard for one character.
like "%CAT%" //like = having matching characters.

Comment

rem - comment

Tables

create table mytable1 (myvarchar1 varchar(10) NOT NULL, mydate date NULL)
insert into mytable1 (myvarchar1, mydate ) values ('Hello Test', '01-Jan-04')
insert into mytable1 (myvarchar1, mydate ) values ('Hello Test 2', sysdate)
drop table mytable1;

Temporary Tables

Temp Tables

create global temporary table tmptable (myvarchar1 varchar(10) NOT NULL) - suppose to work in Oracle 8.1 or greater. Got this from a friend.

Date

Dates

sysdate - the current system date.
select to_char (mydate, 'yyyymmdd') from mytable1
Converts a date to 'yyyymmdd' format

Functions

substr

Oracle DB Tools - Oracle 12c

Start
All Programs
Find Directory: Oracle - OraDB12Home1
Application Development
* Oracle ODBC Help
* Oracle Provider for OLE DB Readme
* SQL Developer
* SQL Plus
Configuration and Migration Tools
*Administration Assistant for Windows
* Database Configuration Assistant
* Database Migration Assistant for Unicode
* Database Upgrade Assistant
* Locale Builder
* Microsoft ODBC Administrator
* Net Configuration Assistant
* Net Manager
* Update Password for Oracle Home User
Integrated Management Tools
* Wallet Manager
Oracle Installation Products
* Universal Installer

How to Create A DB - Oracle 12c

Launch the "Database Configuration Assistant"
Start, search for: Database Configuration Assistant
Launch
Select: Create Database
Click Next
Screen: Creation Mode
Select the default: Create a database with default configuration
Keep the defaults and add:
* Global Database Name: demo
* Administrative Password: <enter your pswd>
* "<Oracle Home User>": <enter your Oracle home user pswd>
* Check - Create as Container Database
* Pluggable Database Name: pdbdemo
Click Next
Click Finish
Screen: Progress Page
WARNING - wait for up to 5 min for progress bar to start and the screen to be populated so be patient and do not click the button "Close". Also, it may take up to 30 min to create the DB.
Click "Password Management" button to set the passwords for the users:
sys
system
Take note of the values on the screen.
Once the completion bar is 100% then you are finished.
Click "exit". You are finished.

Containers & Pluggable DBs - Oracle 12c

Terms:
CDB - Container DB
PDB - Pluggable DB - multi-tenancy

alter session set container = <con_name - container name>
SQL> show con_id;
SQL> show con_name
SQL> select name from v$active_services;

YouTube resources

* http://www.youtube.com/watch?v=KKi37EIQ5-8

dual - testing selects with dual.

select sysdate from dual;
results: 26-MAY-14

group by

group by - see SQL examples

-- ******************************************
-- Display all items in an order for all customers
-- ******************************************
select
cust.custnum,
cust.custname,
ord.ordernum,
to_char(ord.orderdatetime, 'mm/dd/yyyy') as "orderdatetime"
from mycustomer cust
left join myorder ord on ord.custnum = cust.custnum
left join myorderitem orditm on orditm.ordernum = ord.ordernum
order by cust.custnum, cust.custname, ord.ordernum, orderdatetime;

/*
"CUSTNUM" "CUSTNAME" "ORDERNUM" "orderdatetime"
"CMP001" "AAA Company" "ORD101" "05/26/2014"
"CMP001" "AAA Company" "ORD101" "05/26/2014"
"CMP001" "AAA Company" "ORD101" "05/26/2014"
"CMP001" "AAA Company" "ORD102" "05/27/2014"
"CMP001" "AAA Company" "ORD103" "05/27/2014"
"CMP002" "ABC Company" "ORD201" "05/27/2014"
"CMP003" "XYZ Company" "ORD301" "05/27/2014"
*/

-- ******************************************
-- Business Spec:
-- Display the # of items in an order.
-- ******************************************

-- group by & count added.
select
cust.custnum,
cust.custname,
ord.ordernum,
to_char(ord.orderdatetime, 'mm/dd/yyyy') as "orderdatetime",
--ord.orderdatetime,
count(orditm.itemnum) as "itemsinorder"
-- select *
from mycustomer cust
left join myorder ord on ord.custnum = cust.custnum
left join myorderitem orditm on orditm.ordernum = ord.ordernum
group by cust.custnum, cust.custname, ord.ordernum, orderdatetime
order by cust.custnum, cust.custname, ord.ordernum, orderdatetime;

/*
"CUSTNUM" "CUSTNAME" "ORDERNUM" "orderdatetime" "itemsinorder"
"CMP001" "AAA Company" "ORD101" "05/26/2014" 3
"CMP001" "AAA Company" "ORD102" "05/27/2014" 1
"CMP001" "AAA Company" "ORD103" "05/27/2014" 1
"CMP002" "ABC Company" "ORD201" "05/27/2014" 1
"CMP003" "XYZ Company" "ORD301" "05/27/2014" 1
*/
/*
Results if using ord.orderdatetime vs to_char()
"CUSTNUM" "CUSTNAME" "ORDERNUM" "ORDERDATETIME" "itemsinorder"
"CMP001" "AAA Company" "ORD101" 26-MAY-14 11.21.03.000000000 PM 3
"CMP001" "AAA Company" "ORD102" 27-MAY-14 11.21.03.000000000 PM 1
"CMP001" "AAA Company" "ORD103" 27-MAY-14 11.21.03.000000000 PM 1
"CMP002" "ABC Company" "ORD201" 27-MAY-14 11.21.03.000000000 PM 1
"CMP003" "XYZ Company" "ORD301" 27-MAY-14 11.21.03.000000000 PM 1
*/

-- ******************************************
-- Business Spec:
-- Display the # of items in an order ONLY if the customer has more than 1 order.
-- ******************************************
-- added having clause
select
cust.custnum,
cust.custname,
ord.ordernum,
to_char(ord.orderdatetime, 'mm/dd/yyyy') as "orderdatetime",
--ord.orderdatetime,
count(orditm.itemnum) as "itemsinorder"
-- select *
from mycustomer cust
left join myorder ord on ord.custnum = cust.custnum
left join myorderitem orditm on orditm.ordernum = ord.ordernum
group by cust.custnum, cust.custname, ord.ordernum, orderdatetime
having count(orditm.itemnum) > 1
order by cust.custnum, cust.custname, ord.ordernum, orderdatetime;

/*
"CUSTNUM" "CUSTNAME" "ORDERNUM" "orderdatetime" "itemsinorder"
"CMP001" "AAA Company" "ORD101" "05/26/2014" 3
*/