Oracle Users and Permissions

One of the confusing things about Oracle is that while the SYSTEM user is often set during the install, frequently the SYS user is not. However, the SYS user can have its password set, while logged in as the SYSTEM user with the following command:

ALTER USER SYS IDENTIFIED BY ‘password’;

Something interesting about the SYS user, is because it is the most powerful user in Oracle, its necessary to login “as” one of two roles. Either SYSDBA or SYSOPER. These are not actually users, they are roles, so that Oracle provides different levels of permission to one user.

Oracle Enterprise Manager for 10g Download

Interestingly, during our 10g installs the OEM does not appear to install. (did we miss a selection during the installation process) However, OEM for 10g can be downloaded from Oracle’s website.

http://www.oracle.com/technology/software/products/oem/htdocs/winsoft10g.html

Introduction to Oracle Enterprise Manager

Oracle Enterprise Manager has somewhat of a clunky interface, but it is very powerful. A few of the things it can do include:

1. “DBA Tools such as Backup and Data Management wizards, SQL*Plus Worksheet, and Data Guard Manager help you administer databases.
2. Optional Management Packs provide tools for advanced diagnostics, tuning, change management, and administration of Oracle Concurrent Managers and SAP R/3 servers. (Management packs are actually designed to assist in the administration of ERP systems generally and the SAP management pack is only one flavor of this software)
3. Additional Applications, such as Oracle Net Manager and Oracle Directory Manager can also be launched from the Console.” – From Oracle Console Overview

Useful SQL Commands

Not original content. From:

http://www.csb7.com/blogs/media/users/chris/Useful%20SQL%20Commands.pdf

Useful SQL Commands (ORACLE)

Clearer info on errors.

SELECT * FROM user_errors;

Your user name.

SELECT user FROM dual;

Lists data kept on tables.

DESC all_tables;

Lists data kept on objects.

DESC all_objects;

Lists data kept on tables for ‘owner’.

SELECT * FROM all_tables
WHERE owner = ‘OWNER’;
– OR –
SELECT * FROM user_tables
WHERE user = ‘OWNER’;

Select query to return all the lines of a

procedure, function or package.

SELECT TEXT FROM user_source
WHERE name = ‘SUM_SL_COMM’
AND type = ‘FUNCTION’
ORDER BY line;

Add length to a column. You cannot

make it smaller.

ALTER TABLES table_name
MODIFY(column_name,datatype(length));

Runs function as part of a select

query.

SELECT column_name, function_name FROM
table_name;

Returns all objects created by

‘owner’.

SELECT object_name, object_type, created
FROM all_objects
WHERE owner = ‘OWNER’;

Returns the name and type of procedures and functions.

SELECT DISTINCT name, type
FROM all_users;

Returns name and type of constraints.

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
Drop a constraint.
SELECT * FROM user_constraints;

Returns columns

Constraint types include PK, FK and
Check.
SELECT * FROM user_cons_columns;

Oracle Stopped from Running by Windows DEP

This is a serious problem with Windows Server 2003 that stops Oracle from running. It requires an adjustment to the boot.ini file. See the end of the text written below. The essential part is “noexecute=alwaysoff”

boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(1)\WINDOW S
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS=”Windows Server 2003, Enterprise” /fastdetect /noexecute=alwaysoff

Multiple Database Schema and Server Pointing

The concept of having mutiiple schemas (as addressed in this blog posting – Importing and Exporting Oracle DMP Files), is very powerful. The second part of this is being able to “point” the application server to the correct database. Tomcat is a great and free application server.

The second part of this is pointing the browser to the correct application server. In this post we will describe how to reroute Tomcat. But first a brief definition of Tomcat:

Tomcat Web Server

“Jakarta Tomcat is not only the most commonly used open source servlet engine today, it’s become the de facto standard by which other servlet engines are measured. Powerful and flexible, it can be used as a stand-alone web server or in conjunction with another server, like Apache or IIS, to run servlets or JSPs. But mastery of Tomcat is not easy: because it’s as complex as it is complete.”

Also, here is a link to Tomcat 5.5 documentation

http://tomcat.apache.org/tomcat-5.5-doc/index.html

A listing of web / app servers can be found here. The principle of web server rerouting is the same in each, but of course the method of doing is different per web server. If anyone knows of a free webserver that is easy to configure and can be rerouted with a simple front end http page, please respond to this post. WebLogic, which is a very expensive server which does about the same thing as Tomcat has a console which allows you to route to different Oracle users/schemas.

First you must have different application folders to switch between. This is where environmental settings as well as the application logic (stored in compressed JAR files) are located.

For most apps multiple folders will be housed in:

C:\Computer\Program Files\Apache Software Foundation\Tomcat 5.5\webapps\(app folder)\

You can have different application folders to select from by copying an already existing application folder…..

C:\Computer\Program Files\Apache Software Foundation\Tomcat 5.5\webapps\(app folder2)\

…..and making a change to the context.xml file. In many applications it will be located here.

C:\Computer\Program Files\Apache Software Foundation\Tomcat 5.5\webapps\(app folder)\META-INF

The context.xml file will need several the text changed to reflect the correct schema name.

In addition there is typically a setting within the application directory. We will demonstrate with the application we are using. Here are the instructions:

1. Find SPO_HOME directory and then\apps\resources\spo.properties.sample
2. Make a copy of this file and rename it to be:
3. spo.properties
4. Open the spo.properties file for editing.
5. Set the dbURL variable to the correct value. The format to be followed is jdbc:oracle:thin:@host:port:instance.
6. EXAMPLE: dbURL= jdbc:oracle:thin:@transit:1521:SPOUNIT
7. Set the userName variable to the correct value. This is the database schema owner name.
8. EXAMPLE: userName= SPOUSER1

Save the file.

Next we need to….(to be continued)

The final step is restarting the web browser and pointing the browser to the correct URL. The web server will only be able to represent one schema and one folder of application logic at a time.

Oracle Object Browser in Oracle Express and Oracle Enterprise

Oracle has a simplified frontend for the Oracle Express product. There is a separate login page per database that you have installed on Oracle.

It looks like the following screen shot.

Here we are going to select the Object Browser in order to create a few tables:

This is how to create a table:

The next step is selecting a primary key.

After selecting “Not Populated” the following screen comes up:

Here you can see we have selected the PARTS_PK field as the primary key. Below the SQL that is created from this is shown.