When you do an import sometimes you will find you will need to also create a user for this new set of data. Today I find myself in that situation as I imported data from Bell Canada and set up a new user for that data.

1. Create a new user for the data.

   This example creates a user named "foo" with the password "foo".  Foo is a typical user that can create sessions, synonyms, procedures and tables.

   CREATE USER FOO IDENTIFIED BY FOO
     DEFAULT TABLESPACE users
     TEMPORARY TABLESPACE temp
     QUOTA UNLIMITED ON users;
   
   GRANT CREATE SESSION to FOO;
   GRANT CREATE SYNONYM TO FOO;
   GRANT CREATE PROCEDURE TO FOO;
   GRANT CREATE TABLE TO FOO;
   GRANT CREATE VIEW TO FOO;

   For more information please read Oracle’s CREATE USER documentation.

   If you want this user to be a DBA you can grant that to her too

   GRANT DBA TO FOO;

2. Import the dump using the new user.

   This example imports the dump from the file "dump.dmp". This dump was created using the user "foo" and is being imported to the user "foo". The dump will be logged in the file "dump.log".

   imp system/system@example file=dump.dmp log=dump.log fromuser=foo touser=foo

   For more information please read Oracle’s Import Export FAQ.

3. To redo recreate user and then import again.

   If you need to redo an import the easiest thing to do is to drop the user, recreate her and then do the import again. When you drop the user specify CASCADE to drop all the objects in the user’s schema.

   DROP USER FOO CASCADE;

   For more information please read Oracle’s DROP USER documentation.

To list all the users in a database log in as the sysadmin and query the dba_users table.

$ sqlplus sysman/123456@dev01

SQL> select username from dba_users where username like 'Frank%';

Recently our DBA recommended the following for creating tables.

1. All constraints (primary keys, unique keys, foreign keys, etc….) should be declared outside of the CREATE TABLE …. statements, and instead done as ALTER TABLE statements.
2. All tables must have table and column comments to provide information for the data dictionary/schema metadata.

Not Best Practice:

CREATE TABLE items (
  id   VARCHAR2(40) NOT NULL,
  type NUMBER(5)    NOT NULL,
  PRIMARY KEY (id)
);

Best Practice:

CREATE TABLE items (
  id   VARCHAR2(40) NOT NULL,
  type NUMBER(5)    NOT NULL
);
ALTER TABLE items ADD CONSTRAINT items_pk PRIMARY KEY (id);
COMMENT ON TABLE items IS 'repository items';
COMMENT ON COLUMN items.id IS 'primary key (repository id)';
COMMENT ON COLUMN items.type IS 'item type';

I asked the DBA why this is considered best practice and this is what he said.

The DBA’s put indexes into a different tablespace than the table itself for storage, admin, and somewhat performance reasons (NetApp spreads out the I/O so does not quite apply to Upromise environment).

If the PK is part of the table create statement, they have to break out the statement in order to put the PK into a different tablespace or different storage parameters than the table. Having the statements separate from the start makes things smoother.

– Jeff Janousek, Upromise DBA

This morning I was unable to access my Oracle database which runs on my laptop. It’s the first time I have had this problem.

When I tried to login I saw this.

$ sqlplus foo/foo@dev01

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 3 06:38:20 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

When I tried to login without the TNS alias I saw this:

$ sqlplus foo/foo

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 3 06:38:20 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

On the internet I saw recommendations to set your ORACLE_SID and ORACLE_HOME environment variables. But on my system ORACLE_SID is not set and ORACLE_HOME is blank. Finally I just tried restarting the OracleServiceDEV01 service and that worked.

(Photo: Dolphin Crest by jurvetson)

I was wondering how to do this and thankfully someone wrote up a nice article on the SQL update statement.

Here is their example of how to do this.

UPDATE suppliers
SET name =
  ( SELECT customers.name
  FROM customers
  WHERE customers.id = suppliers.id)
WHERE EXISTS
  ( SELECT customers.name
  FROM customers
  WHERE customers.id = suppliers.id);

Thanks to this article, Oracle/PLSQL: Change a user’s password in Oracle, it was quite simple to change a user’s password.

SQL> alter user frank identified by password;