Oracle on Solaris: 32-bit or 64-bit

It is important for optimal performance to make sure you match up your Oracle RDBMS installation with your OS. Running a 32-bit version of Oracle on a 64-bit OS is may not give you peak performance, but also will not be able to address large segments of RAM and large files. So how do you know what your OS supports? How can you tell if that Oracle install from before you started is 64-bit? Here’s how:

Is my Operating System 64-bit?

In Solaris, from the command line (you don’t have to be root in most cases) run this command:

/usr/bin/isainfo -kv

If your OS is 64-bit, you will see output like:

64-bit sparcv9 kernel modules

If your OS is 32-bit, you will get this output:

32-bit sparc kernel modules

For Linux users

If you are running Linux, you can check your distribution with the uname command:

uname -m

The output will read x86_64 for 64-bit and i686 or similar for 32-bit.

How about this Oracle install? Is it 64-bit?

The question here is weather your Oracle binaries are 64-bit. While some of the binaries associated with Oracle may be 32-bit, the important ones will be 64 bit. To check those, follow these steps from the command line:

cd $ORACLE_HOME/bin
file oracl*

This will display the file type of your oracle binaries. If you are running 64-bit binaries, the output should look like this:

oracle: ELF 64-bit MSB executable SPARCV9 Version 1, dynamically linked, not stripped
oracleO: ELF 64-bit MSB executable SPARCV9 Version 1, dynamically linked, not stripped

If your binaries are 32-bit, the output will look like this:

oracle: ELF 32-bit MSB executable SPARC Version 1, dynamically linked, not stripped

If you find you are running 32-bit and decide to go to 64 be careful. The switch can be a bit tricky. Read the documentation closely and make sure your service contract is payed up!

oracle, dba, database administration, database, solaris, linux, sun, sun microsystems, 32-bit, 64-bit

w3schools on SQL

The blog entry I was going to write here would have covered the format and most of the major options for the SELECT statement. While doing a little checking on the internet before posting I realized I am a complete idiot! W3Schools.com, one of the best resources on the web for programming tutorials has a fantastic tutorial on SQL.

It covers the basics of getting data into and out of your database. Beyond the tutorial information, the site includes an interactive demonstration which lets you try out your newly found SQL knowledge. There’s even this SQL Quick Reference to help you remember what you learned.

So check out this tutorial on SQL and don’t miss out on W3Schools.com other fine tutorials.

sql, oracle, dba, database, database administration

Table Creation Examples

Here is a simple example of the SQL used to create a couple tables, populate them, create a view, and grant access to view them.

Create the two tables:

create table name
(
id number not null,
first_name varchar2(30),
last_name varchar2(30) not null,
constraint pk_name primary key (id)
);

create table office_location
(
name_id number not null,
room_number varchar2(6 byte),
building varchar2(20 byte),
constraint person_exists foreign key(name_id)
references name,
constraint room_number_check check (room_number between 0 and 799)
);

Insert some values into each:

insert into name (id, first_name, last_name)
values (1, 'Jon', 'Emmons');

insert into name (id, first_name, last_name)
values (2, 'Matt', 'Batchelder');

insert into name (id, first_name, last_name)
values (3, 'Zach', 'Tirrell');

insert into name (id, first_name, last_name)
values (4, 'Jon', 'Graton');

insert into office_location (name_id, room_number, building)
values (1, '334', 'Hyde');

insert into office_location (name_id, room_number, building)
values (2, '334', 'Hyde');

insert into office_location (name_id, room_number, building)
values (3, '222', 'Hyde');

insert into office_location (name_id, room_number, building)
values (4, '333', 'Hyde');

Extract some meaningful data with a simple join:

select first_name, last_name, building, room_number
from name, office_location
where id=name_id;

Create a view based on that join:

create view name_office as
(
select first_name, last_name, building, room_number
from name, office_location
where id=name_id
);

Select from our new view:

select * from name_office;

Grant access to jemmons to the tables and view (note that you do not have to grant access to the underlying tables for a user to be able to select from the view):

grant select on name to jemmons;

grant select on office_location to jemmons;

grant select on name_office to jemmons;

Technorati tags: , , ,

Useful Oracle Security Views

One of the cool things about oracle is that if you have the right privileges you can learn all it’s secrets from the so-called “data dictionary.”

If you’re interested in this then you probably already know what that’s about, so here are what I consider the important data dictionary views for Oracle security:

DBA_SYS_PRIVS – Probably most important, this show system privileges granted to users and roles

DBA_TAB_PRIVS – While the name implies table privileges this actually incorporates view privileges, procedures, packages and functions

DBA_COL_PRIVS – Not always used, but this view tracks grants on columns

DBA_ROLE_PRIVS – This shows who (or what roles) a role has been granted to

These views don’t seem to have changed much from 9i to 10g (or in 8i at that matter) but it is always best to check documentation for your version of Oracle.

Stay tuned for some handy queries to run on these to get quick reports on who can see what.

Technorati tags: , , , , , ,

Oracle CONNECT and RESOURCE Roles

So what exactly is in those CONNECT and RESOURCE Oracle roles? The ship with every Oracle database and many apps require they be granted. I did a little digging and found the following:

In Oracle 10gR2 things are fairly sane:
CONNECT role has only CREATE SESSION
RESOURCE has CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER and CREATE TYPE

In Oracle 9iR2 things get a little scary:
CONNECT has ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE and CREATE VIEW. Rather a scary lot for a role called ‘connect’
RESOURCE has CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER and CREATE TYPE

The admin option would allow the users to grant the privlelge to another user. Thankfully neither of these roles have the admin option in the versions of Oracle I checked.

To find these privileges you can query the DBA_SYS_PRIVS table with a query like this:

select grantee, privilege, admin_option from dba_sys_privs where grantee='CONNECT';

Of course an oracle role could also have table or column privileges granted to it, so to be thorough you should also check for entries in DBA_TAB_PRIVS and DBA_COL_PRIVS.

NOTE: You should always check these privileges in your database before granting roles as a script, application or previous DBA may have granted or revoked additional roles.

Technorati tags: , , , , , ,