oracle 新增配额,ORACLE 配额的使用

conn system/manager

----------------------------建立用户，分配配额，建立表---------------------

DROP USER test2 CASCADE;

create user test2 identified by test2;

select username,password,profile,default_tablespace from dba_users;

alter user test2 default tablespace users;

grant create session,create table to test2;

select * from dba_sys_privs where grantee='test2';

conn system/manager

ALTER USER test2 QUOTA 1M ON USERS;

CONN test2/test2

CREATE TABLE T1 AS SELECT * FROM USER_USERS;

SELECT * FROM DBA_TS_QUOTAS;

ANALYZE TABLE T1 COMPUTE STATISTICS;

SELECT TABLE_NAME,BLOCKS,EMPTY_BLOCKS FROM USER_TABLES;

-------------------------配额为0-------------------------------------------

CONN test2/test2

DROP TABLE T1;

CREATE TABLE T1 AS SELECT * FROM USER_USERS;

SELECT TABLE_NAME FROM USER_TABLES;

CONN SYSTEM/MANAGER

SELECT * FROM DBA_TS_QUOTAS;

ALTER USER test2 QUOTA 0 ON USERS;

SELECT * FROM DBA_TS_QUOTAS;

CONN test2/test2

INSERT INTO T1 SELECT * FROM T1;

COMMIT;

CREATE TABLE T2 AS SELECT * FROM USER_USERS;

-----------------------------OS认证用户------------------------------------

conn system/manager

select  distinct OSUSER from v$session where username is not null;

show parameter os

select distinct ISSPECIFIED from v$spparameter;--用的是什么初始化参数文件

select instance_name from v$instance;--查看实例的名称

remote_os_authent    =  TRUE

os_authent_prefix    =  OPS$

select  distinct OSUSER as uname from v$session where username is not null;

--注释掉%oracle_home%\network\admin\sqlnet.ora 中(nts)一行--------------------------

select p.value||u.uname from v$parameter p,

(select  distinct OSUSER as uname from v$session where username is not null) u

where name='os_authent_prefix';

CREATE USER "OPS$ZHANGLIE\ADMINISTRATOR" IDENTIFIED BY AA;

create user os_administrator identified externally;

grant create session to os_ADMINISTRATOR;

conn /

注意：配额用户一定要去除DBA权限