Oracle Certification Notes: Part IX

A continuation of my notes as I study for the Oracle Certified Associate credential.

In this post, I've simply referenced available Oracle Functions and associated examples. For a more appropriate description, please view the Oracle Documentation Library.

Function:ROUND(number, precision)

select round(1631.7344,−3) from dual is 2000.

select round(1331.7344,−3) from dual is 1000.

select round(1631.7344,−4) from dual is 0.

select round(5631.7344,−4) from dual is 10000.

select round(123456.78) from dual is 123457. (rounds to the nearest whole number)

Function:TRUNC(number, precision)

select trunc(1631.7394,−3) from dual is 1000.

select trunc(1631.7394,2) from dual is 1631.73.

select trunc(1631.7394) from dual is 1631. (cuts decimal precision; creates whole number)

Function:MOD(dividend, divisor)

select mod(15,4) from dual is 3.

select mod(5,50) from dual is 5.

select mod(4.9,4) from dual is 0.9.

Posted via Woodland Blog (http://dramse01.blogspot.com)

Nuevasync gets a TechCrunch nod

Back in July '08, as I was trying to figure out how best to sync my newly purchased iPhone 3G with Gmail and MS Outlook, I scoured the Apple Discussion Forums. I constantly came across posts recommending nuevasync.

After checking out nuevasync's short and to-the-point site, I contacted TechCrunch. Nuevasync seemed to show as much promise and dedication as other startups that had been mentioned previously on their site. Not to mention, this was all done for free.

Sure enough... seven months later.... a posting on TechCrunch about neuvasync! And it looks like they're well on there way to passing the 100k user mark. Well done. While I eventually chose ActiveSync for my iPhone integration, I may just take another look at nuevasync, given their ability to now support multiple iPhone calendars.

Posted via Woodland Blog (http://dramse01.blogspot.com)

Oracle Certification Notes: Part VIII

A continuation of my notes as I study for the Oracle Certified Associate credential:

Comparison Operators

For character and date comparisons, each side of the comparison operator is converted into its numeric representation, and then compared. The numeric value is based on the database characterset.

!= and <> are equivalent inequality operators

LIKE command has two wildcards: % for 0 or more characters; _ for 1 character.

escape command can be used for Oracle to interpret literal value and not a function (e.g., select val from test_table where val like 'PA\%' escape '\';)

AND operator returns NULL only for the situation in which all conditions are NULL, or at least one of the statements are TRUE. (A FALSE and NULL comparison in the AND operator results in FALSE)

OR operator returns NULL only for the situation in which all conditions are NULL, or at least one of the statements is FALSE. (A TRUE or NULL comparison in the OR operator results in TRUE)

NOT condition against a NULL returns a NULL. (Another use of NOT, SELECT 'Y' FROM dual WHERE NOT ('Hello World' = 'Hello World!');)

ORDER BY

ORDER BY has the NULLS FIRST LAST option

NULL columns have the highest value (i.e., ORDER BY DESC will naturally return NULLs first, unless otherwise specified)

Unless positional sorting (e.g., ORDER BY 1) is used, ORDER BY statements are independent of the SQL projection)

SUBSTITUTION

Reading Oracle documentation, any options in italics means it can be substituted (using an ampersand '&')

Ampersand (&) substitution is executed at runtime and referred to as runtime binding or runtime substitution

Single ampersand substitution prompts the user for a value each time

Double ampersand substitution the first time prompts the user and retains the specified value for single ampersand references throughout the remaining session.

SESSION VARIABLES

UNDEFINE [variable]; command removes a variable and its value from the session.

DEFINE command can list all variables that have been defined within a session (i.e., DEFINE;).

DEFINE [variable]; command can create persistence for a particular variable throughout a session.(e.g., DEFINE v_name='JOE';).

SET DEFINE ON | OFF determines whether session variables can be created, as well as whether ampersand (&) is used as a literal or substitution character.

SET VERIFY ON | OFF allows the user to view the substitution changes (old vs. new) within the client window.

Posted via Woodland Blog (http://dramse01.blogspot.com)

Oracle Certification Notes: Part VII

This is a continuation of my notes as I prepare for the Oracle Certified Associate credential:

SQL SELECT

projection - the ability to limit the columns returned within a result set

selection - the ability to limit the number of rows (or tuples) returned within a result set

joining - the ability to match against related tables (or relations or entities) as part of retrieving a result set

Alias Projection

aliases - a user-friendly tag may be applied to a projected column by providing an alias.

Aliases not enclosed within double-quotes will revert to uppercase. To keep case sensitivity, use double-quotes around the alias name.

The option AS tag may also be used before the alias name, as it is good SQL coding practice (although not required).

Quote (q) Operator: q'[delimiter][string][delimiter]'

Literal values used within SQL are enclosed by single quotes. (e.g., SELECT 'Y' FROM dual;)

For plural's, add an additional single quote (e.g., SELECT 'Y''s' FROM dual;)

For several plural's, use the quote (q) operator delimited with any character or bracket (e.g., SELECT q'!It's Joe's Rows!' FROM dual; or SELECT q'XIt's Joe's RowsX' FROM dual; or SELECT q'[It's Joe's Rows]' FROM dual;)

NULL

NULL is the absence of data

Anything arithmetically executed against NULL will result in NULL

A primary key cannot contain a NULL value; whereas, a foreign key may contain a NULL value.

Datatypes

NUMBER(x,y) - A number may have up to x-positions, where at least y-digits are required to the right of the decimal point. (e.g., NUMBER(8,2) contains valid numbers such as 123456.78, 12345.678 (resulting in 12345.68) & 123456 (resulting in 123456.00). But not 12345678 or 1234567.8.

DATE arithmetic results in a NUMBER value being returned. (e.g., END_DT - START_DT = NUMBER)

DATE minus another DATE represents the days BETWEEN (think inclusive) less one day (e.g., 12/31/2008 - 12/30/2008 is inclusively 2 days, and less one day = 1)

DATE formats: CC (2 digit century), RR (2 digit year where CC is calculated based on current year and specified year)

Posted via Woodland Blog (http://dramse01.blogspot.com)

It's getting easier to unclog your computer

Remember the feeling of having a brand new computer and having it be faster than lightning? Then remember the frustration a few months later when it plods along slower than molasses?

It's a tradition that started with my roommate back in college. Every few months, we would reformat our computers, reinstall the OS of the month, reinstall all of our drivers (well, son, back in my day there was very little of this plug-and-play technology!), and reload all two dozen of the most essential applications (including Command & Conquer). Why would we do this? At the time, we felt (psychologically and in truth) that our computers were reborn and running as smooth as ever.

Fast forward to today. As an alternative twist on yesteryear, my friends often complain of their computers being terribly slow, and recall the nostalgic moments of reformatting/reinstalling. They ask, can you do that "thing" for me too? However, today I've found that unclogging your computer is getting a whole lot easier.

As an example, my latest request involved an annoying ad ware program on Windows XP that wasn't recognized through McAfee virus scan. Anytime a new IE session was open, it was hijacked (with an ad about weight loss, online dating, or the like). It loaded other web pages so slowly, you'd rather just go back to reading a book or watching TV.

If this were several years back, I'd spend a lot of nights scouring the interwebs for registry fixes, constantly booting in safe mode, removing virus executables, etc. And most of the time, I was successful. But today, there's a whole host of software applications that can do all the work for you.

In this case, while I couldn't access the download for Windows Defender on the infected machine, I downloaded it from another computer onto a USB stick, installed it on the infected machine, and successfully cleaned the infected computer of the ad ware - all without a hitch. The uninstallation of a few unnecessary programs and a service pack upgrade later (XP SP3 is a must!)... and it was as good as new.

I continue to be impressed by the advances of software tools (some of which are open source and/or FREE!). The ease with which end consumers can take advantage is truly remarkable. The hardest part is figuring out which tool to use for which situation.

Posted via Woodland Blog (http://dramse01.blogspot.com)

Oracle Certification Notes: Part VI

A continuation of my notes as I study for the Oracle Certified Associate credential:

Tablespace

A tablespace contains one or more segments (e.g., table segment, index segment, undo segment), which are contained on one or more database files

Tablespaces are a method of organizing physical database files with logical segments

At a minimum, 2 tablespaces must be created as part of Oracle 10g: SYSTEM and SYSAUX

Data Dictionary:

Data about data. (e.g., table definitions, integrity constraints, etc.)

Created as part of the CREATE DATABASE command

PL/SQL Packages are stored as part of the data dictionary even though owned by users

Stored in SYSTEM and SYSAUX tablespaces

Instance Parameter File

Exists for Instance-specific parameters. Not to be confused with the database installation parameter file.

Can be static-ASCII based (init.ora - maintained through a text editor) or dynamic-Binary based (spfileSID.ora - maintained through Oracle tools).

The parameter Database Block Size cannot be changed once the database has been created. All other parameters can be adjusted.

Oracle Password File: While OS authentication is required to access Oracle. Oracle also has authentication abilities even when instance is down, through use of an optional, encrypted password file.

Posted via Woodland Blog (http://dramse01.blogspot.com)

Oracle Certification Notes: Part V

A continuation of my notes as I study for Oracle's Certified Associate credential:

Instance vs. Database

Traditionally a one-to-one relationship between instance and database

Oracle Real Application Clusters (RAC): Multiple instances on different servers pointing to a single database.

An instance may point to multiple databases through the use of database links.

Instance: Memory structure and processes. Temporary existence within memory and CPU.

Database: Physical files and disk usage. Infinite existence on disk storage.

DBA manages the relationship between the physical and logical layer of the Oracle Database architecture.

Instance: System Global Area

SGA described as part of the Oracle Instance.

3 required elements: shared pool, database buffer cache, log buffer

Getting a good balance as to the allocated size of SGA can have a significant impact on performance. Too much wasted memory can degrade performance.

SGA: Shared Pool

Shared Pool: Broken down further into segments, including library cache and data dictionary cache

Library Cache: Oracle parses developer code into executable language. Recently executed code is stored within the library cache for faster memory-access performance as opposed to re-parsing the developer code.

Data Dictionary Cache: Indexes, users, table metadata, etc. are stored in memory for faster access (as opposed to going to disk)

SGA: Database Buffer Cache and Log Buffer

Used for SQL execution. Users do not write updates directly to disk. Necessary data is copied from disk to the database buffer cache and then updates are made. Recently retrieved or changed data can now be referenced directly from memory and not disk, minimizing I/O.

The log buffer is used in conjunction with the database buffer cache as part of editing data and storing changes within memory.

Instance: 5 Major Background Processes

System Monitor (SMON): confirms the operation of the database

Process Monitor (PMON): confirms the operations of the user (e.g., computer reboots while in an Oracle session. Oracle gracefully exits user session)

Database Writer (DBW): May have up to 10 concurrent processes. Users always update data within memory. DBW responsible for writing data from memory onto physical disk. Goal: Minimize I/O usage for DBW.

Log Writer (LGWR): Data is written to memory not to disk, minimizing I/O; however, as data is changed, changes are also kept in the Online Redo Logs via the LGWR. Goal: Maximum I/O usage for LGWR.

Checkpoint (CKPT): The physical database is often not up-to-date as compared with data in memory (online redo logs are up-to-date, though). CKPT evaluates the appropriate time to bring physical disk up-to-date, utilizing DBW.

Database: Physical Files

Control File: Stores pointers to other physical files (e.g., online redo logs). Stores items that ensure database integrity (e.g., critical sequence information). Stores backup information. Be sure to specify multiple copies of the control file should one become corrupt. Oracle automatically synchronizes multiple copies.

Redo Log Files: Maintains information necessary to recreate database data. Fixed size files. Consist of a group of log files, each file called a member. Oracle requires at least 2 groups, with 1 member each. Multiple members within a group provides fault tolerance, as Oracle automatically synchronizes multiple members within a group.

Redo Log File Writing: Redo log files are written in a circular fashion (i.e., Log group 1 written to until full. Switch to Log group 2. Log group 2 then written to while Log file 1 is archived. Once Log group 2 is full, switch to Log group 1. Log group 1 is overwritten with new data as Log group 2 is archived, and so on.).

Datafiles: Store physical database data. At least 2 datafiles are required; however, several datafiles are typical. Datafile size only limited to hardware and operating system.

Posted via Woodland Blog (http://dramse01.blogspot.com)

Oracle Certification Notes: Part IV

A continuation of my notes as I study for Oracle's Certified Associate credential:

Different Oracle Database Editions

Express Edition (1 CPU, 1GB Memory, 4GB Database)

Standard Edition One (2 Sockets CPU, Max Memory, No Limit DB)

Standard Edition (4 Sockets CPU, Max Memory, No Limit DB)

Enterprise Edition (No Limit CPU, Max Memory, No Limit DB)

Difference example: Enterprise Edition has N-tier authentication whereas other editions do not

Difference example: Enterprise Edition allows for data partitioning whereas other editions do not

Different Oracle Configuration Assistants

Initially used as part of the initial Oracle database installation

SQL*Plus Configuration Assistant: configures Oracle Containers for Java (OC4J) in order to connect to the database)

Oracle Net Configuration Assistant(NETCA): configure basic networking for the Oracle database

Oracle Database Configuration Assistant (DBCA): configure the actual database

Posted via Woodland Blog (http://dramse01.blogspot.com)

Oracle Certification Notes: Part III

This is a continuation of my notes, as I prepare for the Oracle Certified Associated credential:

Oracle Users and Groups:

One user should own the Oracle DB install (e.g., oracle).

Two groups should be created, one to own the install (e.g., oinstall) and the other to manage the database and it's users (e.g., dba).

Windows database adminstratrion group must be ORA_DBA

Oracle Environment Variables

While Oracle doesn't specifically recommend creating variables before install, still a good idea to at least create ORACLE_HOME before install.

ORACLE_BASE: specifies the root level directory containing Oracle files

ORACLE_HOME: usually a directory within ORACLE_BASE, where a specific instance of Oracle DB is being installed

ORACLE_SID: Oracle instance identifier. 8 alphanumeric characters or less (RAC requires 5 alphnumeric characters or less).

DISPLAY: specify where you want Oracle to display graphics. Will inherit DISPLAY environment variable in UNIX unless otherwise specified. If displaying to an X Window Terminal, be sure oracle user has rights as assigned by root (e.g., xhost command).

Oracle Universal Installer (OUI)

OUI is java and web-based, used for all Oracle installations (applications, database, etc.)

runInstaller is the command to execute OUI in a Linux/UNIX environment

runInstaller -parameterFile /home/oracle/oraparam.ini allows a user to run the installation with user-defined file-based parameters

runInstaller -ignoreSysPrereqs allows a user to skip the system prerequisite checks (oraparam.ini)

OUI requires the use of an inventory directory (e.g., $ORACLE_BASE/oraInventory) to maintain multiple homes, keep track of products and versions installed, etc.

Linux/Unix based installations require root to execute oraInst.sh on first-time Oracle installation servers. This creates the Oracle inventory points file (oraInst.loc) in the $ORACLE_BASE, which then points to a similar file within the /etc on the server.

Importance of oratab file

A list of all Oracle Homes installed on a server is created and stored within /etc/oratab

oratab is necessary for Oracle Enterprise Manager to manage installed databases

Linux/Unix based installations require the execution of $ORACLE_HOME/root.sh as root in order to populate oratab.

Oracle Enterprise Manager (OEM) and other administrative components use oratab to determine where databases are located, and whether or not they should be automatically started when the operating system starts.

Oracle database file storage options:

Oracle database files can be stored using: local file system (easiest and includes Oracle Cluster File System, as well as 3rd party storage arrays), Automated Storage Management (stripping and mirroring benefits), raw devices (advanced).

Posted via Woodland Blog (http://dramse01.blogspot.com)