Working with the Oracle SQL Developer 3.0 DBA Navigator

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldev/r30/SQLdev3.0_DBANavigator/sqldev3.0_Navigator.htm

Implicit COMMIT occurs with

DCL (Data Control Language) and DDL (Data Definition Language) statements cause an implicit commit when issued.

DCL statements consist of commands such as GRANT and REVOKE and are used to control access to the database and data.

DDL statements are used to create database objects and consist of statements like CREATE, DROP, ALTER, and RENAME.

classes12.jar, ojdbc14.jar, ojdbc5.jar and ojdbc6.jar

Differences between classes12.jar, ojdbc14.jar, ojdbc5.jar and ojdbc6.jar
We are planning to upgrade our application servers to a new version you need to look at our Oracle JDBC drivers as well.

classes12.jar - for Java 1.2 and 1.3

ojdbc14.jar - for Java 1.4 and 1.5

ojdbc5.jar - for Java 1.5

ojdbc6.jar - for Java 1.6

Transporting Spatial Data Between Oracle Databases

Oracle provides a variety of ways to perform such exchanges.
These include the Import/Export utilities and the transportable tablespace mechanisms.

The easiest method to load data is through the use of Oracle’s platform-independent .dmp files.
These files are used by Oracle’s Import/Export utilities.
You can export the customers table from the one schema.
Exporting the customers Table into the customers.dmp File

exp oneschema/password FILE=customers.dmp TABLES=customers.

You can later import this data (that is, the .dmp file) into another schema using Oracle’s Import utility.

Importing the customers Table into another Schema using the fromuser and touser Arguments

imp SYSTEM/password FROMUSER=user_old TOUSER=new_user FILE=customers.dmp

If the customers table has a spatial index, this will be re-created on import.
You want to import just the table data without any indexes.
You can then import the data by specifying indexes=n on the command line.

The regs are insert into table MDSYS.SDO_GEOM_METADATA_TABLE on import.
This information for each spatial layer is populated in the USER_SDO_GEOM_METADATA dictionary
view. Oracle Spatial provides the USER_SDO_GEOM_METADATA updatable view to store metadata for spatial
layers.

Spatial indexes

Spatial indexes cannot be transported across different endian platforms.

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
1 Solaris[tm] OE (32-bit) Big
10 Linux IA (32-bit) Little
16 Apple Mac OS Big

SELECT * FROM V$TRANSPORTABLE_PLATFORM;

SDO_GEOMETRY

Type SDO_GEOMETRY is object

SDO_GTYPE is NUMBER

SDO_SRID is NUMBER

SDO_POINT is SDO_POINT_TYPE

SDO_ELEM_INFO is SDO_ELEM_INFO_ARRAY

SDO_COORDINATES is SDO_ORDINATE_ARRAY

Endian Format

In computing, endianness is the ordering of individually addressable sub-units (words, bytes, or even bits) within a longer data word stored in external memory.

The pattern for byte ordering in native types is called endianness.

Joins can be used to connect any number of tables.

You use the following formula to calculate the number of joins you will need in your WHERE clause:

Number of joins = the number of tables used in the query – 1

SQL DELETE Statement

The DELETE statement is used to delete records in a table.

SQL DELETE Syntax

DELETE FROM table_name
WHERE column=value

Delete All Rows

DELETE FROM table_name
or
DELETE * FROM table_name

Be very careful when deleting records.

ORACLE SQL concatenation operator

A concatenation operator:
• Links columns or character strings to other columns
• Is represented by two vertical bars (||)

Create a query to display all the data from EMPLOYEES table.
Separate each column output by a comma.
Name the column title THE_OUTPUT.

SELECT employee_id ||', '|| first_name ||', '|| last_name ||', '|| email ||', '|| phone_number ||', '|| hire_date ||', ' || job_id ||', '|| salary ||', '|| commission_pct ||', '|| manager_id ||', '|| department_id as "The Output"
FROM employees;

Verify the existents of user with username oracle on the system:

inux-suse:~ # finger oracle
Login: oracle Name: Oracle user
Directory: /opt/oracle Shell: /bin/false
Never logged in.
No Mail.
No Plan.
linux-suse:~ #

Datafile information from the control file.

Enter user-name: SYS as sysdba
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT NAME
2 FROM V$DATAFILE;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORA11DEV/system01.dbf
/u01/app/oracle/oradata/ORA11DEV/sysaux01.dbf
/u01/app/oracle/oradata/ORA11DEV/undotbs01.dbf
/u01/app/oracle/oradata/ORA11DEV/users01.dbf
/u01/app/oracle/oradata/ORA11DEV/example01.dbf
/u01/app/oracle/oradata/ORA11DEV/examsql01.dbf

6 rows selected.

SQL> SELECT NAME, STATUS, BYTES, BLOCK_SIZE, BLOCKS
2 FROM V$DATAFILE;

V$DATAFILE_HEADER

SQL> SELECT NAME, TABLESPACE_NAME
2 FROM V$DATAFILE_HEADER;

How to start Oracle Enterprise manager

On Server:

emctl status dbconsole

here you get also your http-adress to call

if it's not running:

emctl start dbconsole

to start oracle enterprise manager database control 10g
emctl start dbconsole(this will start dbcontrol on
http://hostname:1158/em
to get the status of dbconsole
emctl status dbconsole
to stop the dbconsole
emctl stop dbconsole

NOTE: if you got the message FAILED TO SHUTDOWN DBCONSOLE GRACEFULLY on linux machine, it means that emctl cannot recognize the localhost ip address check your network configuration, check your network config

OC4J Bascic Configuration

Oracle Application Server Containers for J2EE 10g (10.1.2.0.2)

When installing OC4J be sure to go to {OC4J_HOME}/j2ee/home/config and check the server.xml file for the settings.

edit server.xml
vi server.xml
<log>

<file path="../log/server.log">

</file>

</log>
<transaction-config timeout="500000"></transaction-config>

<java-compiler name="javac" process="false" bindir="C:\j2sdk1.4.2_15\bin" extdirs="C:\j2sdk1.4.2_15\jre\lib\ext">

</java-compiler>



After:
# java -jar oc4j.jar
09/08/25 10:27:05 Oracle Application Server Containers for J2EE 10g (10.1.2.0.2) initialized

Size of the datafiles

The size of the datafiles (no redo, no tempfiles) can be calculated as shown:

select sum(bytes) from dba_data_files
how many does your datafiles take place in operating system
select sum(bytes) from dba_segments
allocated data (but not fully used by data)
select sum(bytes) from dba_free_space
datafiles are larger than data stored in it - this is unused space in datafiles

Oracle Application Server 10g R2 for AIX5L

I get it downloaded and unzipped, but receive the following error when trying to explode the cpio file:

cpio -idmv < *.cpio

cpio: 0511-903 Out of phase!
cpio attempting to continue...


The extractions instructions correct is "-idcmv".

To check if port 1521 is in use

# netstat -an | grep 1521

Oracle Database startup

# sqlplus /nolog

SQL> connect / as sysdba
SQL> startup

------------------

# lsnrctl start

Oracle Applications Server install kernel parameters

I am trying to install oracle application server on redhat advance server 4.
The installer checks the installaiton requirements and following is the output of that check in which its showing failure while testing the values of msgmnb, msgmni, file-max, hardnofiles.

Example:
Checking for hardnofiles=65536; found hardnofiles=16384.
Failed <<<<

How to set correct values of these???

Executed this action:
Add the following lines to /etc/security/limits.conf file:
* soft nproc 4096
* hard nproc 16384
* soft nofile 16384
* hard nofile 65536

After
Delete "Oracle" user, recreate "Oracle" user again and restart the installation.

Oracle Sequence

create sequence id
increment by 1
start with 15
maxvalue 999999999999999999
nocycle;

insert into tbl_TTTT (id, name)
values (id.nextval, 'ORACLE');

select id, name from tbl_TTTT
id name
-- -------
15 ORACLE

select id.currval from tbl_TTTT;
CURRVAL
---------
15

drop sequence id;