General |
Note |
This page is the setup for
examples in the PSOUG Library. It gives the syntax for creating the
library's default tablespace and user.
|
|
Setup |
Change Schema |
conn / as sysdba
CREATE TABLESPACE uwdata
DATAFILE 'C:\ORACLE\ORADATA\ORABASE\UWDATA01.DBF' SIZE 250M
LOGGING FORCE LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
SEGMENT SPACE MANAGEMENT AUTO;
CREATE USER uwclass
IDENTIFIED BY uwclass
DEFAULT TABLESPACE uwdata
TEMPORARY TABLESPACE temp
QUOTA 0 ON SYSTEM
QUOTA 0 ON SYSAUX
QUOTA UNLIMITED ON uwdata;
GRANT create session TO uwclass;
GRANT create cluster TO uwclass;
GRANT create database link TO uwclass;
GRANT create operator TO uwclass;
GRANT create procedure TO uwclass;
GRANT create sequence TO uwclass;
GRANT create synonym TO uwclass;
GRANT create table TO uwclass;
GRANT create trigger TO uwclass;
GRANT create type TO uwclass;
GRANT create view TO uwclass;
GRANT select any dictionary TO uwclass;
conn uwclass/uwclass |
Create Class Schema |
CREATE TABLE all_objs AS
SELECT * FROM all_objects;
Create the airplanes tables: Click Here
-- save this file as
c: emp\airplanes.sql
Create the servers and serv_inst tables: Click
Here
-- save this file as
c: emp\servers.sql
Load this file to create the postal_code table: postal_code.zip
-- unzip and save the code in this file as
c: emp\postal.sql
-- logged on as uwclass create the airplanes table
SQL> @c: emp\airplanes.sql
-- logged on as uwclass create the servers and
serv_inst tables
SQL> @c: emp\servers.sql
-- logged on as uwclass create the postal_code table
SQL> @c: emp\postal.sql |
Demo Schema Access |
conn scott/tiger
-- if you get this message:
ORA-28000: the account is locked
Warning: You are no longer connected to ORACLE.
SQL>
then
conn / as sysdba
ALTER USER scott ACCOUNT UNLOCK IDENTIFIED BY tiger;
-- try the same thing with the other demo schemas we
will use
conn hr/hr
conn oe/oe
conn pm/pm
conn sh/sh
-- if any fail use the appropriate command, that
follows to unlock
-- the account and assign a password
ALTER USER hr ACCOUNT UNLOCK IDENTIFIED BY hr;
ALTER USER oe ACCOUNT UNLOCK IDENTIFIED BY oe;
ALTER USER pm ACCOUNT UNLOCK IDENTIFIED BY pm;
ALTER USER sh ACCOUNT UNLOCK IDENTIFIED BY sh; |
glogin.sql |
set pagesize 25
set linesize 121
col name format a30
col value format a30
col object_name format a30 |