Oracle create user and Authorization creation Table
--================================
--Oracle User, Object Permission, System Permission
--================================
Steps to create tablespaces and users:
user
create user ID identified by "password";
grant create session to username;
grant create table to username;
grant create tablespace to username;
grant create view to username;
grant resource to
grant connect,resource,dba to user;
select * from user_sys_privs; Query current user permissions
grant connect,resource,dba to user; grant dba permissions
grant connect,resource to user;
grant unlimited tablespace to users;
grant create database link to user;
grant select any sequence,create materialized view to user;
grant unlimited tablespaces to users;//grant unlimited tablespaces
grant select any table to user;//grant query any table
grant is keyword, select any table is permission,to is followed by user account
[sql] view plain copy
tablespace
Create table spaces (generally N table spaces for storing data and an index space):
create tablespace name
datafile 'path (path to be built first)\***.dbf ' size *M
tempfile 'path\***.dbf ' size *M
autoextend on --autoextend
--There are also some commands that define the size, depending on the needs
default storage(
initial 100K,
next 100k,
);
[sql] view plain copy
Example: Creating Tablespaces
create tablespace DEMOSPACE
datafile 'E:/oracle_tablespaces/DEMOSPACE_TBSPACE.dbf'
size 1500M
autoextend on next 5M maxsize 3000M;
Delete Tablespace
drop tablespace DEMOSPACE including contents and datafiles
[sql] view plain copy
user rights
Grant users permission to use tablespaces:
alter user username quota unlimited on tablespaces;
or alter user username quota *M on table space;
Complete example:
[sql] view plain copy
--Tablespace
CREATE TABLESPACE sdt
DATAFILE 'F:\tablespace\demo' size 800M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
--Index Tablespace
CREATE TABLESPACE sdt_Index
DATAFILE 'F:\tablespace\demo' size 512M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
--2. Create users
create user demo identified by demo
default tablespace demo;
--3. Empowerment
grant connect,resource to demo;
grant create any sequence to demo;
grant create any table to demo;
grant delete any table to demo;
grant insert any table to demo;
grant select any table to demo;
grant unlimited tablespace to demo;
grant execute any procedure to demo;
grant update any table to demo;
grant create any view to demo;
[sql] view plain copy
--Import Export Command
ip Export Method: exp demo/demo@127.0.0.1:1521/orcl file=f:/f.dmp full=y
exp demo/demo@orcl file=f:/f.dmp full=y
imp demo/demo@orcl file=f:/f.dmp full=y ignore=y
Big-faced cat operation blog More postures www.itlinux.cn
