oracle创建用户quota,创建用户(不明确指定quota)后，oracle给它的默认quota是多大呢...

最新推荐文章于 2021-12-20 11:38:20 发布
最新推荐文章于 2021-12-20 11:38:20 发布
阅读量496 收藏
点赞数
文章标签： oracle创建用户quota
这篇博客探讨了创建Oracle用户时不指定quota的情况，默认quota为0。作者通过实例展示了用户权限如何影响表空间配额，从unlimitedtablespace权限被撤销到设置具体quota的过程。关键点包括资源角色的作用、quota在不同权限下的行为以及错误01536的出现原因。
摘要由CSDN通过智能技术生成

===============================================================================

1.问题

===============================================================================

创建用户(不明确指定quota)后，oracle给它的默认quota是多大呢？

为什么开始可写数据，但是到后来写数据报ora-01536错误？

-------------------------------------------------------------------------------

01536, 00000, "space quota exceeded for tablespace '%s'"

// *Cause:  The space quota for the segment owner in the tablespace has

//          been exhausted and the operation attempted the creation of a

//          new segment extent in the tablespace.

// *Action: Either drop unnecessary objects in the tablespace to reclaim

//          space or have a privileged user increase the quota on this

//          tablespace for the segment owner.

===============================================================================

2.环境

===============================================================================

DBSVR:

RHEL 5.8 64bit

11.1.0.6.0 64bit

===============================================================================

3.排查验证

===============================================================================

==>创建表空间和用户

-------------------------------------------------------------------------------

点击(此处)折叠或打开

create tablespace quotatest

datafile '/u01/app/oracle/oradata/db11g/quotatest.dbf' size 100m;

create user quotatest identified by quotatest

default tablespace quotatest;

-------------------------------------------------------------------------------

==>用户所拥有权限、配额与授予角色测试

-------------------------------------------------------------------------------

点击(此处)折叠或打开

select * from dba_ts_quotas where username = 'QUOTATEST';

select * from dba_sys_privs where grantee = 'QUOTATEST';

select * from dba_role_privs where grantee = 'QUOTATEST';

SYS@db11g> select * from dba_ts_quotas where username = 'QUOTATEST';

no rows selected

SYS@db11g> select * from dba_sys_privs where grantee = 'QUOTATEST';

no rows selected

SYS@db11g> select * from dba_role_privs where grantee = 'QUOTATEST';

no rows selected

点击(此处)折叠或打开

grant connect to quotatest;

select * from dba_ts_quotas where username = 'QUOTATEST';

select * from dba_sys_privs where grantee = 'QUOTATEST';

select * from dba_role_privs where grantee = 'QUOTATEST';

SYS@db11g> grant connect to quotatest;

Grant succeeded.

SYS@db11g> select * from dba_ts_quotas where username = 'QUOTATEST';

no rows selected

SYS@db11g> select * from dba_sys_privs where grantee = 'QUOTATEST';

no rows selected

SYS@db11g> select * from dba_role_privs where grantee = 'QUOTATEST';

GRANTEE                        GRANTED_ROLE                   ADM DEF

------------------------------ ------------------------------ --- ---

QUOTATEST                      CONNECT                        NO  YES

点击(此处)折叠或打开

conn quotatest/quotatest

select * from user_ts_quotas;

select * from user_sys_privs;

select * from user_role_privs;

QUOTATEST@db11g> select * from user_ts_quotas;

no rows selected

QUOTATEST@db11g> select * from user_sys_privs;

no rows selected

QUOTATEST@db11g> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_

------------------------------ ------------------------------ --- --- ---

QUOTATEST                      CONNECT                        NO  YES NO

点击(此处)折叠或打开

grant resource to quotatest;

select * from dba_ts_quotas where username = 'QUOTATEST';

select * from dba_sys_privs where grantee = 'QUOTATEST';

select * from dba_role_privs where grantee = 'QUOTATEST';

SYS@db11g> grant resource to quotatest;

Grant succeeded.

SYS@db11g> select * from dba_ts_quotas where username = 'QUOTATEST';

no rows selected

SYS@db11g> select * from dba_sys_privs where grantee = 'QUOTATEST';

GRANTEE                        PRIVILEGE                                ADM

------------------------------ ---------------------------------------- ---

QUOTATEST                      UNLIMITED TABLESPACE                     NO

SYS@db11g> select * from dba_role_privs where grantee = 'QUOTATEST';

GRANTEE                        GRANTED_ROLE                   ADM DEF

------------------------------ ------------------------------ --- ---

QUOTATEST                      RESOURCE                       NO  YES

QUOTATEST                      CONNECT                        NO  YES

点击(此处)折叠或打开

select * from user_ts_quotas;

select * from user_sys_privs;

select * from user_role_privs;

QUOTATEST@db11g> select * from user_ts_quotas;

no rows selected

QUOTATEST@db11g> select * from user_sys_privs;

USERNAME                       PRIVILEGE                                ADM

------------------------------ ---------------------------------------- ---

QUOTATEST                      UNLIMITED TABLESPACE                     NO

QUOTATEST@db11g> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_

------------------------------ ------------------------------ --- --- ---

QUOTATEST                      CONNECT                        NO  YES NO

QUOTATEST                      RESOURCE                       NO  YES NO

用户有 unlimited tablespace 权限，未明确设置 quota, quota = 0

-------------------------------------------------------------------------------

==>测试表空间配额

-------------------------------------------------------------------------------

用户有 unlimited tablespace 权限，未明确设置 quota

点击(此处)折叠或打开

create table t1 as select * from all_objects;

select * from user_ts_quotas;

select * from user_sys_privs;

select * from user_role_privs;

QUOTATEST@db11g> create table t1 as select * from all_objects;

Table created.

QUOTATEST@db11g> select * from user_ts_quotas;

TABLESPACE_NAME                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO

------------------------------ ---------- ---------- ---------- ---------- ---

QUOTATEST                         7340032          0        896          0 NO

QUOTATEST@db11g> select * from user_sys_privs;

USERNAME                       PRIVILEGE                                ADM

------------------------------ ---------------------------------------- ---

QUOTATEST                      UNLIMITED TABLESPACE                     NO

QUOTATEST@db11g> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_

------------------------------ ------------------------------ --- --- ---

QUOTATEST                      CONNECT                        NO  YES NO

QUOTATEST                      RESOURCE                       NO  YES NO

点击(此处)折叠或打开

select * from dba_ts_quotas where username = 'QUOTATEST';

select * from dba_sys_privs where grantee = 'QUOTATEST';

select * from dba_role_privs where grantee = 'QUOTATEST';

SYS@db11g> select * from dba_ts_quotas where username = 'QUOTATEST';

no rows selected

SYS@db11g> select * from dba_sys_privs where grantee = 'QUOTATEST';

GRANTEE                        PRIVILEGE                                ADM

------------------------------ ---------------------------------------- ---

QUOTATEST                      UNLIMITED TABLESPACE                     NO

SYS@db11g> select * from dba_role_privs where grantee = 'QUOTATEST';

GRANTEE                        GRANTED_ROLE                   ADM DEF

------------------------------ ------------------------------ --- ---

QUOTATEST                      RESOURCE                       NO  YES

QUOTATEST                      CONNECT                        NO  YES

在插入数据后，dba_ts_quotas无记录，user_ts_quotas有记录。quota为0

用户有 unlimited tablespace 权限，设置 quota 为 0

点击(此处)折叠或打开

alter user quotatest quota 0 on quotatest;

select * from dba_ts_quotas where username = 'QUOTATEST';

select * from dba_sys_privs where grantee = 'QUOTATEST';

select * from dba_role_privs where grantee = 'QUOTATEST';

SYS@db11g> alter user quotatest quota 0 on quotatest;

User altered.

SYS@db11g> select * from dba_ts_quotas where username = 'QUOTATEST';

no rows selected

SYS@db11g> select * from dba_sys_privs where grantee = 'QUOTATEST';

GRANTEE                        PRIVILEGE                                ADM

------------------------------ ---------------------------------------- ---

QUOTATEST                      UNLIMITED TABLESPACE                     NO

SYS@db11g> select * from dba_role_privs where grantee = 'QUOTATEST';

GRANTEE                        GRANTED_ROLE                   ADM DEF

------------------------------ ------------------------------ --- ---

QUOTATEST                      RESOURCE                       NO  YES

QUOTATEST                      CONNECT                        NO  YES

点击(此处)折叠或打开

select * from user_ts_quotas;

select * from user_sys_privs;

select * from user_role_privs;

QUOTATEST@db11g> select * from user_ts_quotas;

TABLESPACE_NAME                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO

------------------------------ ---------- ---------- ---------- ---------- ---

QUOTATEST                         7340032          0        896          0 NO

QUOTATEST@db11g> select * from user_sys_privs;

USERNAME                       PRIVILEGE                                ADM

------------------------------ ---------------------------------------- ---

QUOTATEST                      UNLIMITED TABLESPACE                     NO

QUOTATEST@db11g> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_

------------------------------ ------------------------------ --- --- ---

QUOTATEST                      CONNECT                        NO  YES NO

QUOTATEST                      RESOURCE                       NO  YES NO

点击(此处)折叠或打开

create table t2 as select * from all_objects;

select * from user_ts_quotas;

select * from user_sys_privs;

select * from user_role_privs;

QUOTATEST@db11g> create table t2 as select * from all_objects;

Table created.

QUOTATEST@db11g> select * from user_ts_quotas;

TABLESPACE_NAME                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO

------------------------------ ---------- ---------- ---------- ---------- ---

QUOTATEST                        14680064          0       1792          0 NO

QUOTATEST@db11g> select * from user_sys_privs;

USERNAME                       PRIVILEGE                                ADM

------------------------------ ---------------------------------------- ---

QUOTATEST                      UNLIMITED TABLESPACE                     NO

QUOTATEST@db11g> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_

------------------------------ ------------------------------ --- --- ---

QUOTATEST                      CONNECT                        NO  YES NO

QUOTATEST                      RESOURCE                       NO  YES NO

点击(此处)折叠或打开

select * from dba_ts_quotas where username = 'QUOTATEST';

select * from dba_sys_privs where grantee = 'QUOTATEST';

select * from dba_role_privs where grantee = 'QUOTATEST';

SYS@db11g> select * from dba_ts_quotas where username = 'QUOTATEST';

no rows selected

SYS@db11g> select * from dba_sys_privs where grantee = 'QUOTATEST';

GRANTEE                        PRIVILEGE                                ADM

------------------------------ ---------------------------------------- ---

QUOTATEST                      UNLIMITED TABLESPACE                     NO

SYS@db11g> select * from dba_role_privs where grantee = 'QUOTATEST';

GRANTEE                        GRANTED_ROLE                   ADM DEF

------------------------------ ------------------------------ --- ---

QUOTATEST                      RESOURCE                       NO  YES

QUOTATEST                      CONNECT                        NO  YES

用户吊销了 unlimited tablespace 权限，设置 quota 为 0

点击(此处)折叠或打开

revoke unlimited tablespace from quotatest;

select * from dba_ts_quotas where username = 'QUOTATEST';

select * from dba_sys_privs where grantee = 'QUOTATEST';

select * from dba_role_privs where grantee = 'QUOTATEST';

SYS@db11g> revoke unlimited tablespace from quotatest;

Revoke succeeded.

SYS@db11g> select * from dba_ts_quotas where username = 'QUOTATEST';

no rows selected

SYS@db11g> select * from dba_sys_privs where grantee = 'QUOTATEST';

no rows selected

SYS@db11g> select * from dba_role_privs where grantee = 'QUOTATEST';

GRANTEE                        GRANTED_ROLE                   ADM DEF

------------------------------ ------------------------------ --- ---

QUOTATEST                      RESOURCE                       NO  YES

QUOTATEST                      CONNECT                        NO  YES

点击(此处)折叠或打开

select * from user_ts_quotas;

select * from user_sys_privs;

select * from user_role_privs;

QUOTATEST@db11g> select * from user_ts_quotas;

TABLESPACE_NAME                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO------------------------------ ---------- ---------- ---------- ---------- ---QUOTATEST                        14680064          0       1792          0 NO

QUOTATEST@db11g> select * from user_sys_privs;

no rows selected

QUOTATEST@db11g> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_------------------------------ ------------------------------ --- --- ---QUOTATEST                      CONNECT                        NO  YES NOQUOTATEST                      RESOURCE                       NO  YES NO

点击(此处)折叠或打开

create table t3 as select * from all_objects;

select * from user_ts_quotas;

select * from user_sys_privs;

select * from user_role_privs;

QUOTATEST@db11g> create table t3 as select * from all_objects;

create table t3 as select * from all_objects

*

ERROR at line 1:

ORA-01536: space quota exceeded for tablespace 'QUOTATEST'此时quota起作用了，报1536错

QUOTATEST@db11g> select * from user_ts_quotas;

TABLESPACE_NAME                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO

------------------------------ ---------- ---------- ---------- ---------- ---

QUOTATEST                        14680064          0       1792          0 NO

QUOTATEST@db11g> select * from user_sys_privs;

no rows selected

QUOTATEST@db11g> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_

------------------------------ ------------------------------ --- --- ---

QUOTATEST                      CONNECT                        NO  YES NO

QUOTATEST                      RESOURCE                       NO  YES NO

点击(此处)折叠或打开

select * from dba_ts_quotas where username = 'QUOTATEST';

select * from dba_sys_privs where grantee = 'QUOTATEST';

select * from dba_role_privs where grantee = 'QUOTATEST';

SYS@db11g> select * from dba_ts_quotas where username = 'QUOTATEST';

no rows selected

SYS@db11g> select * from dba_sys_privs where grantee = 'QUOTATEST';

no rows selected

SYS@db11g> select * from dba_role_privs where grantee = 'QUOTATEST';

GRANTEE                        GRANTED_ROLE                   ADM DEF

------------------------------ ------------------------------ --- ---

QUOTATEST                      RESOURCE                       NO  YES

QUOTATEST                      CONNECT                        NO  YES

用户吊销了 unlimited tablespace 权限，设置 quota 为 -1

点击(此处)折叠或打开

alter user quotatest quota -1 on quotatest;

SYS@db11g> alter user quotatest quota -1 on quotatest;

alter user quotatest quota -1 on quotatest

*

ERROR at line 1:

ORA-02187: invalid quota specification

不能手工设置quota为 -1

用户吊销了 unlimited tablespace 权限，设置 quota 为 20m

点击(此处)折叠或打开

alter user quotatest quota 20m on quotatest;

select * from dba_ts_quotas where username = 'QUOTATEST';

select * from dba_sys_privs where grantee = 'QUOTATEST';

select * from dba_role_privs where grantee = 'QUOTATEST';

SYS@db11g> alter user quotatest quota 20m on quotatest;

User altered.

SYS@db11g> select * from dba_ts_quotas where username = 'QUOTATEST';

TABLESPACE_NAME                USERNAME                            BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO

------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---

QUOTATEST                      QUOTATEST                        14680064   20971520       1792         2560 NO

SYS@db11g> select * from dba_sys_privs where grantee = 'QUOTATEST';

no rows selected

SYS@db11g> select * from dba_role_privs where grantee = 'QUOTATEST';

GRANTEE                        GRANTED_ROLE                   ADM DEF

------------------------------ ------------------------------ --- ---

QUOTATEST                      RESOURCE                       NO  YES

QUOTATEST                      CONNECT                        NO  YES

quota设置为非0值，可在dba_ts_quotas中查到记录

点击(此处)折叠或打开

select * from user_ts_quotas;

select * from user_sys_privs;

select * from user_role_privs;

QUOTATEST@db11g> select * from user_ts_quotas;

TABLESPACE_NAME                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO

------------------------------ ---------- ---------- ---------- ---------- ---

QUOTATEST                        14680064   20971520       1792       2560 NO

QUOTATEST@db11g> select * from user_sys_privs;

no rows selected

QUOTATEST@db11g> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_

------------------------------ ------------------------------ --- --- ---

QUOTATEST                      CONNECT                        NO  YES NO

QUOTATEST                      RESOURCE                       NO  YES NO

点击(此处)折叠或打开

create table t4 as select * from all_objects;

select * from user_ts_quotas;

select * from user_sys_privs;

select * from user_role_privs;

QUOTATEST@db11g> create table t4 as select * from all_objects;

create table t4 as select * from all_objects

*

ERROR at line 1:

ORA-01536: space quota exceeded for tablespace 'QUOTATEST'

QUOTATEST@db11g> select * from user_ts_quotas;

TABLESPACE_NAME                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO

------------------------------ ---------- ---------- ---------- ---------- ---

QUOTATEST                        14680064   20971520       1792       2560 NO

QUOTATEST@db11g> select * from user_sys_privs;

no rows selected

QUOTATEST@db11g> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_

------------------------------ ------------------------------ --- --- ---

QUOTATEST                      CONNECT                        NO  YES NO

QUOTATEST                      RESOURCE                       NO  YES NO

点击(此处)折叠或打开

select * from dba_ts_quotas where username = 'QUOTATEST';

select * from dba_sys_privs where grantee = 'QUOTATEST';

select * from dba_role_privs where grantee = 'QUOTATEST';

SYS@db11g> select * from dba_ts_quotas where username = 'QUOTATEST';

TABLESPACE_NAME                USERNAME                            BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO

------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---

QUOTATEST                      QUOTATEST                        14680064   20971520       1792         2560 NO

SYS@db11g> select * from dba_sys_privs where grantee = 'QUOTATEST';

no rows selected

SYS@db11g> select * from dba_role_privs where grantee = 'QUOTATEST';

GRANTEE                        GRANTED_ROLE                   ADM DEF

------------------------------ ------------------------------ --- ---

QUOTATEST                      RESOURCE                       NO  YES

QUOTATEST                      CONNECT                        NO  YES

用户重新授予特定表空间 unlimited quota 权限

点击(此处)折叠或打开

alter user quotatest quota unlimited on quotatest;

select * from dba_ts_quotas where username = 'QUOTATEST';

select * from dba_sys_privs where grantee = 'QUOTATEST';

select * from dba_role_privs where grantee = 'QUOTATEST';

SYS@db11g> alter user quotatest quota unlimited on quotatest;

User altered.

SYS@db11g> select * from dba_ts_quotas where username = 'QUOTATEST';

TABLESPACE_NAME                USERNAME                            BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO

------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---

QUOTATEST                      QUOTATEST                        14680064         -1       1792           -1 NO

SYS@db11g> select * from dba_sys_privs where grantee = 'QUOTATEST';

no rows selected

SYS@db11g> select * from dba_role_privs where grantee = 'QUOTATEST';

GRANTEE                        GRANTED_ROLE                   ADM DEF

------------------------------ ------------------------------ --- ---

QUOTATEST                      RESOURCE                       NO  YES

QUOTATEST                      CONNECT                        NO  YES

点击(此处)折叠或打开

select * from user_ts_quotas;

select * from user_sys_privs;

select * from user_role_privs;

QUOTATEST@db11g> select * from user_ts_quotas;

TABLESPACE_NAME                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO

------------------------------ ---------- ---------- ---------- ---------- ---

QUOTATEST                        14680064         -1       1792         -1 NO

QUOTATEST@db11g> select * from user_sys_privs;

no rows selected

QUOTATEST@db11g> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_

------------------------------ ------------------------------ --- --- ---

QUOTATEST                      CONNECT                        NO  YES NO

QUOTATEST                      RESOURCE                       NO  YES NO

授予unlimited 后，quota值为-1

点击(此处)折叠或打开

create table t5 as select * from all_objects;

select * from user_ts_quotas;

select * from user_sys_privs;

select * from user_role_privs;

QUOTATEST@db11g> create table t5 as select * from all_objects;

Table created.

QUOTATEST@db11g> select * from user_ts_quotas;

TABLESPACE_NAME                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO

------------------------------ ---------- ---------- ---------- ---------- ---

QUOTATEST                        22020096         -1       2688         -1 NO

QUOTATEST@db11g> select * from user_sys_privs;

no rows selected

QUOTATEST@db11g> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_

------------------------------ ------------------------------ --- --- ---

QUOTATEST                      CONNECT                        NO  YES NO

QUOTATEST                      RESOURCE                       NO  YES NO

点击(此处)折叠或打开

select * from dba_ts_quotas where username = 'QUOTATEST';

select * from dba_sys_privs where grantee = 'QUOTATEST';

select * from dba_role_privs where grantee = 'QUOTATEST';

SYS@db11g> select * from dba_ts_quotas where username = 'QUOTATEST';

TABLESPACE_NAME                USERNAME                            BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO

------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---

QUOTATEST                      QUOTATEST                        22020096         -1       2688           -1 NO

SYS@db11g> select * from dba_sys_privs where grantee = 'QUOTATEST';

no rows selected

SYS@db11g> select * from dba_role_privs where grantee = 'QUOTATEST';

GRANTEE                        GRANTED_ROLE                   ADM DEF

------------------------------ ------------------------------ --- ---

QUOTATEST                      RESOURCE                       NO  YES

QUOTATEST                      CONNECT                        NO  YES

-------------------------------------------------------------------------------

==>清理

-------------------------------------------------------------------------------

点击(此处)折叠或打开

drop user quotatest cascade;

drop tablespace quotatest including contents and datafiles drop quota;

select * from dba_ts_quotas where username = 'QUOTATEST';

select * from dba_sys_privs where grantee = 'QUOTATEST';

select * from dba_role_privs where grantee = 'QUOTATEST';

User dropped.

SYS@db11g>

Tablespace dropped.

SYS@db11g> SYS@db11g> select * from dba_ts_quotas where username = 'QUOTATEST';

no rows selected

SYS@db11g> select * from dba_sys_privs where grantee = 'QUOTATEST';

no rows selected

SYS@db11g> select * from dba_role_privs where grantee = 'QUOTATEST';

no rows selected

===============================================================================

4.小结

===============================================================================

创建用户(不明确指定quota)后，oracle给它的默认quota是多大呢？

是0。

为什么开始可写数据，但是到后来写数据报ora-01536错误？

用户开始可能有unlimited tablespace权限，而到后来吊销了该权限。

当用户拥有 unlimited tablespace权限时，quota限制不起作用。

而授予resource角色，会同时隐含授予unlimited tablespace权限，

吊销resource时，会同时吊销unlimited tablespace权限。

(12.1.0.1起，已取消此隐含授权。)

===============================================================================

5.参考信息

===============================================================================

侯圣文

【Quota】获取及调整用户表空间配额方法

http://blog.itpub.net/519536/viewspace-664347/

shaochenshuo

oracle表空间配额(quota)

http://blog.51cto.com/589985/1336658

Ask TOM

User Quota

Ask TOM

Resource Role and "Unlimited Tablespace" privilege

docs.oracle.com

Database Security Guide

戚琳
关注
创建数据库表空间用户及数据备份.rar_oracle_创建数据对象
09-24
首先，**表空间（Tablespace）**是Oracle数据库中存储数据的基本单位，它是由一个或多个数据文件组成的逻辑存储区域。表空间用于组织和管理数据库中的数据，不同类型的数据库对象（如表、索引、视图等）可以被分配到...
oracle表空间配额和unlimited tablespace权限
热门推荐
blue huang的专栏
02-08 2万+
对于一个新建的用户，如果没有分配给unlimitedtablespace系统权限的用户，必须先给他们指定限额，之后他们才能在表空间中创建对象，限额可以是： A、以兆字节或者千字节为单位的特定值 B、无限制的 限额是指定标空间中允许的空间容量，默认的情况下，用户在任何表空间中都是没有限额的，可以使用一下三个选项来为用户提供表空间限额： A、无限制的：允许用户最大限度的使用表空间中的可用空间
oracle创建表空间和修改用户默认表空间
wdyliuxingfeiyang的博客
12-20 4686
--表空间 CREATE TABLESPACE sdt DATAFILE 'F:\tablespace\demo' size 800M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; --索引表空间 CREATE TABLESPACE sdt_Index DATAFILE 'F:\tablespace\demo' size 512M EXTENT MANAGEMENT LOCAL SEGM.
Oracle用户管理
江小洋
07-09 325
创建一个用户user3，口令为user3，默认表空间为USERS，在该表空间的配额为10MB。CREATE USER user3 IDENTIFIED BY user3 DEFAULT TABLESPACE USERS QUOTA 10M ON USERS;为用户user3授予C
ORACLE表空间大小限制
烟沉往逝
06-30 1万+
后来查了查资料，发现表空间数据文件容量与DB_BLOCK_SIZE有关，在初始建库时，DB_BLOCK_SIZE要根据实际需要，设置为 4K,8K、16K、32K、64K等几种大小，ORACLE的物理文件最大只允许4194304个数据块（由操作系统决定），表空间数据文件的最大值为 4194304×DB_BLOCK_SIZE/1024M。即：4k最大表空间为：16384M8K最大表空间为：32768M16k最大表空间为：65536M32K最大表空间为：131072M64k最大表空间为：262144M
oracle10g角色,oracle10g和11g关于角色口令的区别
weixin_42494160的博客
04-09 153
角色是一组相关权限的命名集合，使用角色最主要的目的是简化权限管理，而一旦这个集合的权限超过了用户的最低需求，就可能带来的安全风险。角色口令测试oracle 10g中，无论角色是否有口令，只要你将角色grant给某个用户，那么，默认的情况下，这些角色中的权限，用户都拥有。oracle 11g中，角色的口令略有修正，当某个角色是拥有口令的话，当你将带有口令的角色grant给某个用户的话，那么默认的情况...
ORACLE quota 表空间限制
08-16
Quota 主要用于限制用户在一个或多个表空间内所能使用的最大空间量。通过设置合适的 Quota，不仅可以有效地防止用户滥用存储资源，还能确保数据安全性，提高数据库整体性能。 #### 二、设置 Quota 配额 ##### 2.1 ...
Oracle创建表空间.创建用户.创建表
03-18
### Oracle 创建表空间、创建用户、创建表 #### 一、创建表空间 表空间是Oracle数据库中的逻辑存储单元，它由一个或多个数据文件组成。表空间是Oracle数据库存储结构的基本单位，用来组织和管理数据库中的数据。在...
oracle 创建用户和数据库
09-17
### Oracle 创建用户与数据库知识点详解 #### 一、Oracle创建表空间 在Oracle数据库管理中，**表空间**是逻辑存储单元，它由一个或多个数据文件组成，用于存储数据库对象（如表、索引等）。创建表空间是管理数据库...
28.Oracle用户和权限管理1
08-08
Oracle 数据库用户和权限管理是数据库管理员（DBA）日常工作中不可或缺的部分。Oracle 提供了丰富的功能来确保数据库的安全性和高效运行。以下是关于 Oracle 用户和权限管理的详细讲解： 首先，Oracle 在创建新...
【笔记】QUOTA的使用--创建用户
cikaoqin5248的博客
11-12 250
CREATE USER usernameIDENTIFIED [DEFAULT TABLESPACE tablespace][TEMPORARY TABLESPACE tablcspace][QUOTA |UNLIMITED O...
oracle对表空间 USERS 无权限
wangjian0310的专栏
04-04 2592
权限赋予即可：alter user 用户名 quota unlimited on users; 下面是我SQLPLUS实现users权限的赋予： SQL> connect daqing/thinking 已连接。 SQL> create table stud(name nvarchar2(10),age integer); create table stud(name nva...
详解oracle用户创建(create user)
jtwmy_lb的博客
12-26 1万+
详解oracle用户创建(create user)(上) 转载 http://blog.itpub.net/26110315/viewspace-715882/ 用户能访问数据库前必须要有获得相应授权的账号，oracle中创建一个用户并为其分配密码很简单，但是在创建用户的同时其实还可以指定很多该用户的属性,另外还有用户的特权授予。 以便用户去执行相应的操作。当创建了一个的同时也创建了一个通的she...
ORA-01950: no privileges on tablespace ，ORA-01536: space quota exceeded for table space 'CYYD'
蓦然回首，择城终老
03-04 1505
Oracle的表空间quota详解   当你创建用户的时候， 如下：   SQL> CREATE USER user01 IDENTIFIED BY oracle DEFAULT TABLESPACE tbs1 TEMPORARY TABLESPACE temp PROFILE default SQL> GRANT create session, create table TO
oracle创建用户'表空间配额,oracle表空间配额（quota）
weixin_32484541的博客
04-02 2282
--先大概写一下，后面有时间会整理一下一：quota相关视图1)dba_ts_quotas(查看所有用户的表空间配额)BYTES字段表示用户已经使用的空间；MAX_BYTES如果为-1表示没有限制，其他值表示限制配额--只有用alteruseruser_namequotaontablespace_name;命令显示的调整过用户的空间配额，该视图中才会有记录，否则该视图没有记录--如果该视...
表空间
weixin_34268610的博客
12-19 113
表空间是数据库的逻辑划分，一个表空间只能属于一个数据库。所有的数据库对象都存放在指定的表空间中。但主要存放的是表， 所以称作表空间。 Oracle数据库中至少存在一个表空间，即SYSTEM的表空间。 SQL Server数据库与Oracle数据库之间最大的区别要属表空间设计。Oracle数据库开创性地提出了表空间的设计理念，这为Oracle数据库的高性能做出了不可磨灭的贡...
oracle创建用户'表空间配额,牛刀小试：Oracle 用户表空间配额（quota ）控制之随心所欲...
weixin_39726697的博客
04-02 650
Oracle 官网对quota的定义如下： A limit on a resource, such as a limit on the amount of database storage used by a database user. A database administrator can set tablespace quotas for each Oracle Database user...
用户对表空间的权限
努力学习，天天向上
03-19 1095
ORACLE中设置同一个用户对两个表空间的权限的代码解答：alter user 用户 quota unlimited on 表空间A; alter user 用户 quota unlimited on 表空间B; 或者放开所有表空间 grant unlimited tablespace to 用户;
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值