Basic Administrative SQL commands

From Peter Pap's Technowiki
Revision as of 22:33, 3 January 2017 by Ppapa (talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

Create tablespace

 create tablespace tablespacename
  datafile '/path/to/file/filename.dbf'
  size 10m autoextend on
  next 5m maxsize 1G;

This will create a 10mb datafile that will extend by 5mb until it gets to 1gb max.

Add Datafile to Tablespace

 alter tablespace tablespacename
  add datafile '/path/to/file/filename.dbf'
  size 10m autoextend on
  next 5m maxsize 1G;

Create a Role

You need to create a role that will give the users privileges to do things

 create role rolename;

Give the role privileges:

 grant connect to rolename;
 grant resource to rolename;

Create a User

Create the account:

 create user username
  identified by password
  default tablespace tablespacename
  temporary tablespace temp
  quota unlimited on tablespacename
  account unlock;

Give the account priveleges using the role you created before:

 grant rolename to username;