Oracle

Introduction
The Oracle Database deliver Grid Computing, gives Oracle customers the agility to respond faster to changing business conditions, gain competitive advantage through technology innovation, and reduce costs.

Some references mentioned here are based on the pages of the book "Oracle 9i para Desenvolvedores (Oracle Developer 6i)" from Lúcia Fernandes.

Links

 * Oracle Database
 * Documentation

Configuring the system environment
We can alter the mode the data are displayed or stored in the database in 3 different scopes:


 * To change the default value for all database users modifying the.
 * To change the value only for the user computer modifying the environment variables registered in the operational system. In Windows for example you will find the variables in the registry HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME. You can also add or change Oracle variables if you modify the values located on "Panel Control->System->System Variables"
 * To change only the value for the actual Oracle session. In this case, you have to use the command

The following commands verify the actual system configuration and display the date and currency format.

SELECT SYS_CONTEXT ('USERENV','NLS_DATE_FORMAT') FROM DUAL; SELECT SYS_CONTEXT ('USERENV','NLS_CURRENCY') FROM DUAL;

The command below change the territory and then the date format:

ALTER SESSION SET NLS_TERRITORY=AMERICA; ALTER SESSION SET NLS_DATE_FORMAT='DD/MM/RRRR HH24:MI';

In Oracle Forms you shall use forms_ddl('ALTER SESSION SET NLS_NUMERIC_CHARACTERS=,.');

Consult the pages 91, 92 and 93 to know more about the symbols and modification of date and currency.

Exporting

 * Open a console window and execute the command to export an Oracle database. You have to enter the following information:
 * username
 * password
 * array (normally, 4096)
 * output file or dump file
 * what will be exported? Answer 2 for everything including users, grant, table
 * compress (normally, yes)

Example using some parameters: exp system/PASSWORD@XE file=E:\web\XE.dmp owner=myuser

XE refers to the database server(Oracle SID).

Importing
To import an Oracle database follow the instructions below:

set ORACLE_SID=DBSERVER
 * Open the Oracle Console Management, go to the tab "memory" and create a tablespace. If possible, create a separated tablespace to be used by the indexation.
 * Create a user.
 * Associate the tablespace to the user.
 * Click on the tab Role and add the roles Connect and Resource.
 * Click on the tab System and add the option Unlimited tablespace.
 * Open a console window:
 * Set the used ORACLE_SID to the PC where the Oracle database is installer. Example under windows:
 * Execute the command to import the Oracle database. Enter the username, password, and the filename to import.

Verify if there was any error message and try to fix the errors.

Example using some parameters: imp system/PASSWORD@XE file=D:\temp\XE.dmp fromuser=user1 touser=user2

XE refers to the database server(Oracle SID). user1 and user2 can be equal.

How to create a new Schema
Ready! Now you can connect to the Oracle database using this user and create any object. The schema will be displayed in the Oracle Enterprise Manager.
 * Create a new user / password
 * Select the Tablespace for data and temporary data. It is better to create them separately
 * The user shall have the roles “Connect” and “Resource”
 * The user shall have the rights for “Unlimited Tablespace”

Sequences
To use a sequence in one sql statement use the command. Example: Select mysequence.NEXTVAL from dual;

.

Conversions
TO_DATE (‘10031990’, ‘DDMMYYYY’)
 * Number:
 * Text:
 * Date: . Example:

Data Manipulation

 * To extract values:   - The object can be : Year, Month, Day, Hour, Minute, Second, Timezone_Hour, Timezone_Minute, Timezone_Region or Timezone_ABBR
 * To add months:
 * Current date:
 * Current date and time:
 * Last day:
 * Quantity of months between two date:
 * Date and time of the server: . Example:

Select sysdate from dual;

Ignoring accents
Use the following commands in order to ignore accents in search statements:

ALTER SESSION SET NLS_COMP=LINGUISTIC; ALTER SESSION SET NLS_SORT=GENERIC_BASELETTER;

Sure you can add this to the tnsnames.ora or as environment variable to the OS.

That means that the following sql statements will return the same result:

SELECT person_id, epi_id, first_name, last_name, acronym, room_number, extension FROM persons WHERE UPPER(first_name) LIKE '%ANTONIO%' SELECT person_id, epi_id, first_name, last_name, acronym, room_number, extension FROM persons WHERE UPPER(first_name) LIKE '%ANTÓNIO%'

How to delete all table data
To delete all table data use the command. Its performance is better than.

Start/Stop Scripts
To stop the Oracle database Express Edition under unix use the following commands:

oradim -shutdown -sid XE >nul 2>&1 net stop OracleServiceXE lsnrctl stop

To start the Oracle database Express Edition under unix use the following commands:

lsnrctl start net start OracleServiceXE oradim -startup -sid XE >nul 2>&1

You could create scripts to accelerate the process.