Oracle New Environment Setup

Post date: 20-May-2010 13:44:01

Technically, a schema is a collection of database objects owned by a specific user. Those objects include tables, indexes, views, stored procedures, etc. In Oracle, a schema requires a user to be created. But you can create a user that has no schema (i.e, no objects). So in Oracle, the user is the account and the schema is the objects. 

A schema is collection of database objects, including logical structures such as tables, views, sequences, stored procedures, synonyms, indexes, clusters, and database links. 

A user owns a schema. 

A user and a schema have the same name. 

The CREATE USER command creates a user. It also automatically creates a schema for that user. 

The CREATE SCHEMA command does not create a "schema" as it implies, it just allows you to create multiple tables and views and perform multiple grants in your own schema in a single transaction. 

For all intents and purposes you can consider a user to be a schema and a schema to be a user. 

Furthermore, a user can access objects in schemas other than their own, if they have permission to do so.

Think of a user as you normally do (username/password with access to log in and access some objects in the system) and a schema as the database version of a user's home directory. User "foo" generally creates things under schema "foo" for example, if user "foo" creates or refers to table "bar" then Oracle will assume that the user means "foo.bar".

http://toolkit.rdbms-insight.com/sample_create.php

http://www.getyourcontent.com/1/6565-0/Oracle-Database-XE----SQL.aspx

/*************** Creating Tablespace ***************/

For windows:

create tablespace TS_LOCAL_DW datafile 'C:\ORACLEXE\ORADATA\XE\TS_LOCAL_DW' size  50m autoextend on  next 10m maxsize 100m;

ALTER TABLESPACE TS_LOCAL_DW add DATAFILE 'C:\ORACLEXE\ORADATA\XE\TS_LOCAL_DW01' SIZE 1024M AUTOEXTEND ON next 10m MAXSIZE UNLIMITED;

For Unix: 

create tablespace  users datafile  ‘/ora01/oracle/oradata/booktst_users_01.dbf’ 

size 50m autoextend on  next 10m  maxsize 100m;

/**************** Creating User *****************/

http://www.psoug.org/reference/user.html

CREATE USER LOCAL_DW

IDENTIFIED BY LOCAL_DW

DEFAULT TABLESPACE TS_LOCAL_DW

TEMPORARY TABLESPACE temp

QUOTA 10M ON TS_LOCAL_DW;

/************** Grant Permissions ***************/

GRANT create session TO LOCAL_DW;

GRANT create table TO LOCAL_DW;

GRANT create view TO LOCAL_DW;

grant sysdba to LOCAL_DW;

grant CREATE DATABASE LINK TO LOCAL_DW;

grant CREATE PUBLIC DATABASE LINK TO LOCAL_DW;

/****************Creating DB Links and Testing*******/

Userful link for DB Link creation 

http://download-uk.oracle.com/docs/cd/B28359_01/server.111/b28310/ds_admin002.htm

Creating DB Links Between Local and Development Environmet

create database link DEV_LOCAL_DBLINK

connect to GCRMDW_D_RPT

identified by "<pwd>"

using 'D_GCRMDW_D';

select * from W_ORG_D@dev_local_dblink where rownum < 5;

select * from W_MONTH_D@dev_local_dblink where rownum < 5;

Create table W_MONTH_D as select * from W_MONTH_D@dev_local_dblink;

/**** Command to get a table name ***********/

SELECT   segment_name table_name, SUM (BYTES) / (1024 * 1024) table_size_meg

    FROM user_extents

   WHERE segment_type = 'TABLE' AND segment_name = 'W_MONTH_D'

GROUP BY segment_name;

/***** To get current Workin directory ****/

SQL> !pwd

SQL>host cd

SQL>host pwd

/**** To get Spool Directory Set *****/

SQL> Spool on

SQL> set heading off <===

SQL> Spool c:\spooltext.txt

SQL> Query

SQL> Spool off