copy from elec_mgmt/elecmgmt@stockroom2 to shop_mgmt/shopmgmt@stockroom2 create shop_mgmt.temp
using select * from elec_mgmt.orders;

 

copy from sde_user/(password for whiterim)@WHITERIM -
to sde_user/(password for scooter)@scooter -(this line is optional)
create HYDNOSD -
using -
select * from SDE_USER.HYDNOSD;

 

copy from scott/tiger @ORCL92 -
to scott/tiger @ORCL92-
create new_emp –
using select * from emp;

 

ORACLE CHEAT SHEET

Written by

This is not meant to be a tutorial, and is not even yet complete, but merely meant to be a way to quickly find information needed to do the most common DBMS specific tasks. Eventually I hope to have an SQL cheat sheet that, with this, will cover Oracle basics.

DEFINITIONS(SPECIFIC TO ORACLE!!!!)
 

CLUSTERED INDEX	The indexes of several related tables physically tied together. This improves space and speed efficiency when the tables are generally used together. If the tables aren't generally used together, it may be a good idea to keep them separate.
DATABASE	An instance of the SQL server engine. May contain several schemas! (normally called an instance or a server)
SCHEMA	A collection of tables (normally called a database)
SYNONYM	An object that redirects reference to another object.  An object in database A might look like a table, but refer data inserted into it to a table in database B.  THAT table, in database a, is a synonym.
TABLE SPACE	A file that contains the objects defined.

 

ARCHITECHTURE

PAGE SIZE 8K
PHYSICAL STRUCTURESEPARATE FILES called TABLE SPACE.
BASED ON
MINIMUM SIZE
MAXIMUM SIZE
ALLOCATION
PAGE SPANNING
MINIMUM SIZE OF OBJECT
MINIMUM SIZE OF TABLE
MAXIMUM SIZE OF TABLE

HIERARCHY OF COLLECTIONS

DATABASE&SCHEMA.TABLE

SCHEMA.TABLE

TABLE

This means that if you are in the NUMONE schema, and want to insert values into NUMONEM from the NUMTWOJ table in the NUMTWO schema on the JUPITER database, you can type:

insert into NUMONEN select * from JUPITER&NUMTWO.NUMTWOJ

SHOW THE TABLES DEFINITION

desc TABLE_NAME;

SHOW THE NAMES OF TABLES MATCHING A PATTERN

select distinct table_name from all_tables where table_name like '%PATTERN%';

SHOW ALL OBJECT NAMES MATCHING A PATTERN

select distinct object_name from all_objects where object_name like '%PATTERN%';

SHOW ALL CONSTRAINTS FOR TABLE NAMES MATCHING A PATTERN

select * from all_CONS_COLUMNS where table_name like '%PATTERN%';

SHOW ALL STORED PROCEDURES/PACKAGES THAT MATCH A PATTERN

select distinct name, type from user_source where name like '%PATTERN%';

SHOW THE TEXT FOR A GIVEN STORED PROCEDURE/PACKAGE

select text from user_source where name='PROCEDURE NAME';

JOIN TWO ALPHA FIELDS

Ok, somebody should have realized that most languages that use || (such as C and PERL and JAVA) define it as meaning OR. Oh well, here it means concatenate.

||

ENSURE A NULL FIELD IS RETURNED AS 0

nvl(FIELD_NAME)

READ 10 ROWS FROM A TABLE

Unfortunately, nobody bothered to include a command to do this in SQL.  Oracle has a neat way of doing this and similar things!  They simply have several "psuedo columns".

select * from TABLE_NAME where rownum<=10

COPY THE STRUCTURE AND DATA OF A TABLE

create TABLE_NAME as select ...

Oh, you didn't want to copy the data as well?  Simply add a where clause onto the create stateent that is always false!

EXECUTE AN SQL BATCH FILE

@FILE_NAME

SQL*PLUS COMMANDS

Everything up to this point has been commands that would run under the Oracle server.  The following ones run only under SQL*PLUS.  SQL*PLUS is Oracles interactive SQL client.

Commands given in SQL*PLUS are treated as transactions, like in most similar products.  These transactions, unlike most other products, are not committed, except under certain circumstances.  Until it is commited, or rolled back, the session will treat the data as if it HAD been committed.

COMMIT A COMMANDS RESULTS

COMMIT

ROLLBACK A COMMANDS RESULTS

ROLLBACK

SPOOL OUTPUT TO A FILE

spool FILENAME

COMMANDS TO LOG/SPOOL....

spool off

TURN THE HEADING OFF/ON

set heading OFFON

EDIT BUFFER

This is almost as bad as the || discrepancy. Oracle asks you to end lines with ";" everywhere but in the editor. In the editor it will create an error condition. In the editor, you must place a "/" at the end of the batch instead.

ed

RUN BUFFER

When you edit the query, you edit what is in the buffer. The "/" command can be used as much as you want to run want is in the buffer.

/

Files in an Oracle Database

• Data Files - the datafiles hold the tables, indexes, views, and procedures and other objects contained in the database.
   
• Control Files - the control files hold the information about file names needed for the database to startup and shutdown.
   
• Redo Log Files - these hold the record of all completed transactions in the database.

 
 
 

What is a Database backup?

A copy of all the files necssary to reconstruct the database in the case of a physical or media failure.  A complete database backup will be able to reconstruct the data files, control files and redo logs of a database.

Oracle database recovery will result in all files in the database restored to the same point in time.

In our DB2 environment we are able to recover at a tablespace level and because we have chosen to create only one table in a tablespace we are able to recover at the table level.  Tablespaces in the DB2 database may be restored to different points in time.
 

Table of Contents
 
 

Types of Backup

There are two kinds of database backups available to us physical backups and logical backups.

Physical Backup is an actual physical copy of the files of the database copied from one location to another.

Logical Backup is a copy of the data in the database but not a copy of the physical files.

 

Cold Backup

A cold backup is a physical backup.

During a cold backup the database is closed and not available to users.  All files of the database are copied (image copy).  The datafiles do not change during the copy so the database is in sync upon restore.
 

Used when:

Service level allows for some down time for backup

 

Hot Backup

A hot backup is a physical backup.  In a hot backup the database remains open and available to users. All files of the database are copied (image copy).  There may be changes to the database as the copy is made and so all log files of changes being made during the backup must be saved too.  Upon a restore, the changes in the log files are reapplied to bring the database in sync.

Used when:

A full backup of a database is needed
Service level allows no down time for the backup

Logical Backup

A logical backup is an extract of the database.  All SQL statements to create the objects and all SQL statements to populate the objects are included in the extract.  Oracle provides a utility export, to create the extract.   A partner utility, import, is used to bring the data back into the database.

A logical backup can be done at the table, schema(or proxy owner), or database level.  That is, we can extract only a list of specified tables, a list of specified schemas or the full database.

Used to:

Move or archive a database
Move or archive a table(s)
Move or archive a schema(s)
Verify the structures in the database

 
 

Table of Contents

Application Backup Options

 
 DBA's have the responsibility of restoring the database in the case of a media failure.  Oracle backups that we do now achieve that result.

Application Level Backup and Recovery may also be needed.  This kind of recovery must be planned for by the application developer and the DBA together.   Work with the DBA and have a plan for this from the beginning when designing your application.

Why application backups?

• To recover data after running an errant batch program.

 

• To recover data after user error.

What are the options?

• Export/Import Utility
• Application Logic
• Separate Oracle Databases

Options:
 

Export/Import Utility

Export can be run for :
 

• A list of tables
• All tables owned by a certain oracle user id (proxy schema)
• The entire database.

The export would be done before a batch job is run.  If batch job fails or is incorrect, table data can be imported from the export.
 

• Data is restored to point in time when the export was taken
• Transactions completed after that time are lost
• Data integrity may be compromised if there is a relationship with a table or other system (DB2, other Oracle database) that is not restored to the same point in time.

Example 1

 
 
 
 
 
 
 
 
 

Application Logic

Design your application to allow for reversing destructive functions.

• Use an effective date method of deleting records
• Use a status flag on a record

Design your batch programs to allow for restarting in an elegant fashion.

• Log the changes you've made during a batch run.
• Use restart logic in a batch program.
   
   
   
   
   
   
   
   
   
   
   
   

Using a Separate Oracle Database for a System

In some cases a system may warrant its own separate database.

Evolution of DRMT's Thinking:

• DRMT initially thought all systems would share one Oracle production database.
• Then Oracle Financials came along.  That was put in a separate database, to ease backup, recovery and upgrade issues.  The same happened for PeopleSoft, for UAI, for Clarify, etc.
• Now we think that vendor designed applications go in a separate database, but the ones that we build share a database.
• Then we started thinking through the issues of application level recovery and
• Realize that some DoIT developed applications will have a separate database too.

 
Points to Consider:

• Database can be restored to any point in time
• Additional resources in disk and server memory will be used
• Sharing data with tables in other databases becomes more complicated than having all tables in one database.
• Transactions completed after that time are lost
• Data integrity can not be maintained across databases with Oracle constraints.

 
 
 

Questions  and Answers

1. Why does an Oracle database have to be recovered all together to the same point in time?
 A:  This is just the way it is in Oracle.  The referential integrity in a relational database can only be guaranteed if the entire database is consistant in relation to the last committed transaction.

2. How long does it take to do a forward recovery?
A: It depends on how much we lost and how long since the last physical backup.  The first step is to restore the last backup of what was lost.  If it's a 2GB file and our backup is on ADSM we have to
wait first for that to be restored (maybe 1 hour or more?) then we apply transaction logs to redo transactions.  If the backup is on disk, then the restore is quicker.  Our goal is to minimize the time of recovery.  For any production system we want to keep unplanned down time to under 2 hours.

3. Do we use hot or cold backup for production databases?
A: UWP1 is now using cold.  ISIS is now using cold.  Wiscworld email database used hot.  Clarify uses hot. It depends on the service level requirements.

4. Does a logical backup contain the data as well as the SQL needed to recreate the objects?
A:  Yes.  Both the DDL (data definition language, ie: CREATE TABLE etc.) to create the objects and the SQL to populate the object with data is included in an export.

5. Are there tools to see what integrity constraints and other relationships exisit between tables?
A: The Oracle system catalogs can give us some information.  However, there may be systems that you don't know about using your data.  This is not necessarily a relationship that's stored in the database.

6. Does PoepleSoft support batch programs undoing what they did?
A: PeopleSoft does use effective dates in most of their records.  I'm not familiar with specifics on the programming styles of batch programs in the system.   That's something we all have to learn.

  Table of Contents

 

Example 1
 

1. Export of Table A and Table B is taken at 10:30pm.

2. Batch job to update Table A and Table B is run at 11:00 pm.

3.  Users arrive at 7am and begin doing other daily updates and changes to system.

4. Users report problem with database at 8am.

5. Table A and Table B are restored from export at 8:30am.  Data is returned to its state as of 10:30pm the previous night.

6. Users must redo work between 7 and 8.  Batch job must be fixed and rerun.

Cheats

 

http://radio.weblogs.com/0128037/stories/2003/10/21/oracleScmInstallationCheatSheet.html

http://www.osmosislatina.com/oracle/part1.htm

http://www.osmosislatina.com/oracle/index.htm

http://home.earthlink.net/~adamshalon/oracle_password_cracker/

http://www.informit.com/isapi/product_id~%7B9AF62809-408D-401A-9144-B0A695B72424%7D/element_id~%7BAF8F47AB-B8E8-46DB-846D-97CF8155304D%7D/st~%7B340C91CD-6221-4982-8F32-4A0A9A8CF080%7D/content/articlex.asp

Pre-requisites: The copy is between 2 machines, both have to be on the same OS and have to have exactly the same database version installed.

1. Make sure the database you want to copy was closed with a SHUTDOWN IMMEDIATE, SHUTDOWN NORMAL or SHUTDOWN TRANSACTIONAL.
2. Copy init.ora and control files to create instance and be able to go in mount mode. Check the init.ora for the locations where the controlfiles have
to be, if those locations are not valid on the machine put the control files on
different places and adjust the init.ora accordingly.
3.a. Copy the datafiles (all of them).
  b. Copy the redo-logfiles (all of them).
4.a. (Unix only) Set the environment variables:
        ORACLE_SID - set to the database name you wish to create
        ORACLE_HOME - set to full pathname of the Oracle system home directory
        PATH - needs to include $ORACLE_HOME/bin
  b. (NT/2000 only) Do 'set ORACLE_SID='
        Use oradim to create the service for the instance.   For more information
on oradim please refer to (the part that refers to creating a new instance):

Creating a new 7.3, 8.0, 8.1 Instance/Database

5. Use servermanager (check the name to use for your version of oracle) or sqlplus (version 9i and above) to startup the database in mount mode.  
Do  
      CONNECT INTERNAL/
then
      STARTUP MOUNT    
Then do a rename of the copied datafiles if they are not in the same path as on the other machine. For all the files that are in the result of the query:newfullpath>\';
      SELECT NAME FROM V$DATAFILE;
do
      ALTER DATABASE RENAME FILE '\' to <

6.   Query the datadictionary for the old location of the redolog files using:
      SELECT MEMBER FROM V$LOGFILE;
If the new place is not the same as the old do:
    ALTER DATABASE RENAME FILE '\' to &
lt;newfullpath>\';
7.   Now open the database:
      ALTER DATBASE OPEN;