Oracle Stuff

Oracle. Yum. A non-free software product you'd actually want to pay for. (But if you're cash-strapped, look into PostgreSQL...)

Install Oracle 9i on Fedora Core 3

Enough to fill its own page...

Create a tablespace:

Run this as user system, logged onto the Oracle SID where you need this tablespace to be. Run this *before* creating any user that will use this tablespace.

That is, from the command line

sqlplus system/mypassword@MYSID

and then, once you are in SQL*Plus,

create tablespace petstore_tablespace
datafile
	'/opt/oracle/oradata/db1/petstore_01.dbf' size 100 M 
autoextend on next 10 M maxsize unlimited logging online permanent;

Create a user to use the tablespace created above:

Run this as user system, logged onto the Oracle SID where you need this tablespace to be. Run this *before* creating any user that will use this tablespace.

That is, from the command line

sqlplus system/mypassword@MYSID

and then, once you are in SQL*Plus,

create user petstore_user identified by petstore_password
 default tablespace petstore_tablespace
 temporary tablespace temp
 quota unlimited on petstore_tablespace;

grant connect to petstore_user;
grant dba to petstore_user;
grant resource to petstore_user;

Destroy a user and all that user's objects:

Run this as user system, logged onto SID where the tablespace exists.

drop user petstore_user cascade;

Destroy a tablespace:

Run this as user system, logged onto SID where the tablespace exists.

drop tablespace petstore_tablespace
    including contents and datafiles cascade constraints;

Use SQL to create SQL to empty a user/schema of all its objects:

This is a work in progress, but essentially like what is shown here. Run this from inside SQL*Plus logged on as the user/schema you want to empty.

-- Will create a script called
-- nuke_database.sql
-- which, when run through sqlplus,
-- will nuke the database.
-- To create the script, run this
-- through sqlplus.
set feedback off
set heading off
set termout off
set linesize 1000
set trimspool on
set verify off
spool nuke_database.sql
prompt set define off
select 'DROP ' || object_type || ' ' || object_name || case object_type when 'TABLE' then ' CASCADE CONSTRAINTS' end || ';' as nuke_statement 
from user_objects;
prompt /
prompt set define on
spool off
set feedback on
set heading on
set termout on
set linesize 100

Import/export an oracle dump file:

Generically,

Export:

exp user/password file=petstore_database.dump full=y statistics=none

or, to ensure just for one user/schema:

exp user/password owner=petstore_user file=petstore_database.dump statistics=none

Import:

imp user/password file=petstore_database.dump full=y destroy=y

Importing when the export user is different from the import user:

imp user/password file=petstore_database.dump destroy=y \
    fromuser=another_user touser=petstore_user

Interestingly, the destroy=y doesn't seem to work so well; imp complains about all the constraints still existing, for instance. What you can do is probe the data dictionary for all tables and constraints and use the names of all the objects to create a nuke database sql script that you can feed to sqlplus. There's a simple, incomplete one above.

Get the first 10 lines of an Oracle query:

Use "where rownum < 11" in your query.

select * from tab where rownum < 11;