Difference between revisions of "Basic Administrative SQL commands"

From Peter Pap's Technowiki
Jump to: navigation, search
(Create a user)
 
(One intermediate revision by the same user not shown)
Line 6: Line 6:
 
   next 5m maxsize 1G;
 
   next 5m maxsize 1G;
  
This will create a 20mb datafile that will extend by 5mb until it gets to 1gb max.
+
This will create a 10mb datafile that will extend by 5mb until it gets to 1gb max.
  
== Add datafile to tablespace ==
+
== Add Datafile to Tablespace ==
  
 
   alter tablespace '''''tablespacename'''''
 
   alter tablespace '''''tablespacename'''''
Line 15: Line 15:
 
   next 5m maxsize 1G;
 
   next 5m maxsize 1G;
  
 +
== Create a Role ==
  
== Create a user ==
+
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 the account:
Line 27: Line 38:
 
   account unlock;
 
   account unlock;
  
Give the account priveleges:
+
Give the account priveleges using the role you created before:
  
   grant connect to '''''username''''';
+
   grant '''''rolename''''' to '''''username''''';

Latest revision as of 22:33, 3 January 2017

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;