herb

喜欢写博，喜欢安静，喜欢挑战！

日志

关于我

herb

我就是我,认真工作,开心生活！

文章分类

Oracle新建用户，授权，建表空间

2010-04-29 10:15:18| 分类： Oracle | 标签： |举报 |字号大中小订阅

下载LOFTER 我的照片书 |

Oracle新建用户，授权，建表空间

这段时间在学oracle，终于把数据库成功导入到我自己的数据库里了。

create用户

sql 代码

1.

Sql代码

SQL> create user visiontv identified by visiontv default tablespace visiontv quota 10m on users;

SQL> create user visiontv identified by visiontv default tablespace visiontv quota 10m on users;

grant privilege

sql 代码

1.

Sql代码

SQL> grant connect,resource to visiontv;

SQL> grant connect,resource to visiontv;

create tablespace

sql 代码

Sql代码

1. SQL> create tablespace visiontv datafile 'D:\DBServer\oracle\oradata\oradb01\vis
2. iontv.ora' size 100m reuse default storage(initial 500k next 500k pctincrease 20
3. );

1. SQL> create tablespace visiontv datafile 'D:\DBServer\oracle\oradata\oradb01\vis 2. iontv.ora' size 100m reuse default storage(initial 500k next 500k pctincrease 20 3. );

import后就可以用了。

//

建立用户到用户建表的步骤：

Server

建立：create user 用户名 identified by "密码";

授权：grant create session to 用户名;

grant create table to 用户名;

grant create tablespace to 用户名;

grant create view to 用户名;

Client：

建立表空间(一般建N个存数据的表空间和一个索引空间)：

create tablespace 表空间名

datafile ' 路径(要先建好路径)\***.dbf ' size *M

tempfile ' 路径\***.dbf ' size *M

autoextend on --自动增长

--还有一些定义大小的命令，看需要

default storage(

initial 100K,

next 100k,

);

Server:

授予用户使用表空间的权限：

alter user 用户名 quota unlimited on 表空间;

或 alter user 用户名 quota *M on 表空间;

自此，才大功告成可以随意建表，运行SQL脚本！

//1．建表空间

Sql代码

create tablespace OSDB datafile 'F:\oracle\oradata\glsqjz\OSDB.ora' size 100m reuse default storage(initial 500k next 500k pctincrease 20);

create tablespace OSDB datafile 'F:\oracle\oradata\glsqjz\OSDB.ora' size 100m reuse default storage(initial 500k next 500k pctincrease 20);

2．建用户

Sql代码

create user OSUSER identified by OSUSER

create user OSUSER identified by OSUSER;//identified by 后面的是密码，前面的是用户名

3．用户授权

Sql代码

grant resource,connect,RECOVERY_CATALOG_OWNER to OSUSER ;
grant create table to OSUSER ;
alter user OSUSER quota unlimited ON OSDB;
alter user OSUSER default tablespace OSDB;

grant resource,connect,RECOVERY_CATALOG_OWNER to OSUSER ;grant create table to OSUSER ;alter user OSUSER quota unlimited ON OSDB;alter user OSUSER default tablespace OSDB;

4．删除表空间

Sql代码

DROP TABLESPACE TableSpaceName INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE TableSpaceName INCLUDING CONTENTS AND DATAFILES;

5．删除用户

Sql代码

DROP USER User_Name CASCADE

DROP USER User_Name CASCADE

6．删除表的注意事项

在删除一个表中的全部数据时，须使用

Sql代码

TRUNCATE TABLE 表名

TRUNCATE TABLE 表名

;因为用DROP TABLE，DELETE * FROM 表名时，TABLESPACE表空间该表的占用空间并未释放，反复几次DROP，DELETE操作后，该TABLESPACE上百兆的空间就被耗光了。

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

释放临时表空间实例

临时表空间

临时表空间作用

Oracle临时表空间主要用来做查询和存放一些缓冲区数据。临时表空间消耗的主要原因是需要对查询的中间结果进行排序。

重启数据库可以释放临时表空间，如果不能重启实例，而一直保持问题sql语句的执行，temp表空间会一直增长。直到耗尽硬盘空间。

网上有人猜测在磁盘空间的分配上，oracle使用的是贪心算法，如果上次磁盘空间消耗达到1GB，那么临时表空间就是1GB。也就是说当前临时表空间文件的大小是历史上使用临时表空间最大的大小。

临时表空间的主要作用：

索引create或rebuild

Order by 或 group by

Distinct 操作

Union 或 intersect 或 minus

Sort-merge joins

analyze

查看临时表空间大小

查看临时表文件大小和已使用空间

select t1."Tablespace" "Tablespace",

t1."Total (G)" "Total (G)",

nvl(t2."Used (G)", 0) "Used(G)",

t1."Total (G)" - nvl(t2."Used (G)", 0) "Free (G)"

from

(

select tablespace_name "Tablespace", to_char((sum(bytes/1024/1024/1024)),'99,999,990.900') "Total (G)"

from dba_temp_files

group by tablespace_name

union

select tablespace_name "Tablespace", to_char((sum(bytes/1024/1024/1024)),'99,999,990.900') "Total (G)"

from dba_data_files

where tablespace_name like 'TEMP%'

group by tablespace_name

) t1,

(

select tablespace, round(sum(blocks)*8/1024) "Used (G)" from v$sort_usage

group by tablespace

) t2

where t1."Tablespace"=t2.tablespace(+)

查看当前临死表使用空间大小与正在占用临时表空间的sql语句

select sess.SID, segtype, blocks*8/1000 "MB" ,sql_text

from v$sort_usage sort, v$session sess,v$sql sql

where sort.SESSION_ADDR = sess.SADDR

and sql.ADDRESS = sess.SQL_ADDRESS

order by blocks desc;

select 'the ' || name || ' temp tablespaces ' || tablespace_name ||

' idle ' ||

round(100 - (s.tot_used_blocks / s.total_blocks) * 100, 3) ||

'% at ' || to_char(sysdate, 'yyyymmddhh24miss')

from (select d.tablespace_name tablespace_name,

nvl(sum(used_blocks), 0) tot_used_blocks,

sum(blocks) total_blocks

from v$sort_segment v, dba_temp_files d

where d.tablespace_name = v.tablespace_name(+)

group by d.tablespace_name) s,

v$database;

修改临时文件大小

select 'ALTER database TEMPFILE ' || file_name || ' resize 100M ;'

from dba_temp_files

where tablespace_name = 'ONLYDWTEMP';

ALTER database TEMPFILE '/oradata/ONLYDWTEMP06.dbf' resize 100M ;

ALTER database TEMPFILE '/oradata/ONLYDWTEMP07.dbf' resize 100M ;

ALTER database TEMPFILE '/oradata/ONLYDWTEMP08.dbf' resize 100M ;

ALTER database TEMPFILE '/oradata/ONLYDWTEMP09.dbf' resize 100M ;

ALTER database TEMPFILE '/oradata/ONLYDWTEMP10.dbf' resize 100M ;

ALTER database TEMPFILE '/oradata/ONLYDWTEMP01.dbf' resize 100M ;

ALTER database TEMPFILE '/oradata/ONLYDWTEMP02.dbf' resize 100M ;

ALTER database TEMPFILE '/oradata/ONLYDWTEMP03.dbf' resize 100M ;

ALTER database TEMPFILE '/oradata/ONLYDWTEMP04.dbf' resize 100M ;

ALTER database TEMPFILE '/oradata/ONLYDWTEMP05.dbf' resize 100M ;

SQL> ALTER database TEMPFILE '/oradata/ONLYDWTEMP09.dbf' resize 100M ;

ALTER database TEMPFILE '/oradata/ONLYDWTEMP09.dbf' resize 100M

ORA-03297: file contains used data beyond requested RESIZE value

创建新的临时表空间

SQL> create temporary tablespace TEMP1 TEMPFILE '/oradata/TEMP1_01.dbf' size 100M;

Tablespace created

SQL> create temporary tablespace TEMP2 TEMPFILE '/oradata/TEMP2_01.dbf' size 100M;

Tablespace created

将当前临时表空间指定为新的临时表空间

SQL> alter database default temporary tablespace TEMP1;

Database altered

删除旧的临时表空间

SQL> drop tablespace ONLYDWTEMP including contents and datafiles;

Tablespace dropped

检查当前用户的临时表空间

9i之前，如果一个数据库用户没有被指定默认临时表空间，那么oracle就会使用system表空间作为该用户的临时表空间，这是很危险的。在9i里边，database可以被指定一个默认临时表空间。这样如果数据库用户没有被明确指定临时表空间，oracle9i就会自动指定database的默认临时表空间作为该用户的临时表空间。

我们可以通过下面的语句来查询数据库的默认临时表空间。

select * from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE';

如下语句可以查询数据库用户的临时表空间。

select username,default_tablespace,temporary_tablespace from dba_users

如果删除了用户的临时表空间，而这个临时表空间又不是默认临时表空间（如果是数据库的默认临时表空间是删不掉的），用户的临时表空间不会自动转换到数据库的默认临时表空间上。这时如果使用该用户登陆，并执行查询用到临时空间，就会报错。解决方式就是将用户的临时表空间修改过来。

SQL> alter user CTL temporary tablespace TEMP1;

User altered

SQL> alter user ODS temporary tablespace TEMP2;

User altered

select username,default_tablespace,temporary_tablespace from dba_users

user

tablespace

temporary tablespace

SCOTT

SYSTEM

ONLYDWTEMP

CTL

TEMP1

ODS

ODSD

TEMP2

SQL> drop tablespace TEMP2 including contents and datafiles;

Tablespace dropped

select username,default_tablespace,temporary_tablespace from dba_users

user

tablespace

temporary tablespace

SCOTT

SYSTEM

ONLYDWTEMP

CTL

TEMP1

ODS

ODSD

TEMP2

SQL> conn ods/ods@test_BI_DW

Connected to Oracle9i Enterprise Edition Release 9.2.0.8.0

Connected as ods

SQL> select do.object_name from dba_objects do, dba_objects db order by 1;

Tablespace TEMP2 not found

SQL> alter user ODS temporary tablespace TEMP1;

User altered

以上的方法只是暂时释放了临时表空间的磁盘，是治标但不是治本的方法。真正的治本方法是找出数据库中消耗资源比较大的sql语句，然后对其进行优化处理。

Select se.username,

se.sid,

su.extents,

su.blocks * to_number(rtrim(p.value)) as Space,

tablespace,

segtype,

sql_text

from v$sort_usage su, v$parameter p, v$session se, v$sql s

where p.name = 'db_block_size'

and su.session_addr = se.saddr

and s.hash_value = su.sqlhash

and s.address = su.sqladdr

order by se.username, se.sid

评论这张

转发至微博

阅读(7872)| 评论(2)

历史上的今天

this.p={  m:2,
              b:2,
              loftPermalink:'',
              id:'fks_080070083082085074081086082095085084083069092086085074084',
              blogTitle:'Oracle新建用户，授权，建表空间',
              blogAbstract:'<P style=\"TEXT-INDENT: 2em;\"  \> </P\>  <P style=\"TEXT-INDENT: 2em;\"  \> </P\>  <P style=\"TEXT-INDENT: 2em;\"  \>  <TABLE\>  <TBODY\>  <TR\>  <TD\>  <P\></P\>  <P style=\"TEXT-INDENT: 2em;\"  \><A rel=\"nofollow\" href=\"http://ilinux.javaeye.com/blog/178306\"  \>Oracle新建用户，授权，建表空间</A\></P\>  <P style=\"TEXT-INDENT: 2em;\"  \>这段时间在学oracle，终于把数据库成功导入到我自己的数据库里了。 </P\>  <P style=\"TEXT-INDENT: 2em;\"  \>create用户 </P\>  <P style=\"TEXT-INDENT: 2em;\"  \>sql 代码 </P\>  <P style=\"TEXT-INDENT: 2em;\"  \>   1. </P\>  <P style=\"TEXT-INDENT: 2em;\"  \>Sql代码</P\>  <P style=\"TEXT-INDENT: 2em;\"  \>  </P\><OL\>  <LI\>SQL> create user visiontv identified by visiontv default tablespace visiontv quota 10m on users;     </LI\></OL\>  <P\></P\>  <P style=\"TEXT-INDENT: 2em;\"  \>SQL> create user visiontv identified by visiontv default tablespace visiontv quota 10m on users;   </P\></TD\></TR\></TABLE\></P\>',
              blogTag:'',
              blogUrl:'blog/static/1226803902010329101518870',
              isPublished:1,
              istop:false,
              type:2,
              modifyTime:1316778408142,
              publishTime:1272507318870,
              permalink:'blog/static/1226803902010329101518870',
              commentCount:2,
              mainCommentCount:1,
              recommendCount:0,
              bsrk:-100,
              publisherId:0,
              recomBlogHome:false,
              currentRecomBlog:false,
              attachmentsFileIds:[],
              vote:{},
              groupInfo:{},
              friendstatus:'none',
              followstatus:'unFollow',
              pubSucc:'',
              visitorProvince:'',
              visitorCity:'',
              visitorNewUser:false,
              postAddInfo:{},
              mset:'000',
              mcon:'',
              srk:-100,
              remindgoodnightblog:false,
              isBlackVisitor:false,
              isShowYodaoAd:false,
              hostIntro:'我就是我,认真工作,开心生活！',
              hmcon:'1',
              selfRecomBlogCount:'0',
              lofter_single:'<iframe width="140" height="560" style="overflow:hidden;" src="http://www.lofter.com/mailEntry.do?blogad=1&blog" frameBorder="0"></iframe>'
            }

{list a as x}
    {if !!x}
    <div class="iblock nbw-fce nbw-f40">
      <a class="fc03 noul" target="_blank" hidefocus="true" href="http://blog.163.com/${x.visitorName}/">
      {if x.visitorName==visitor.userName}
      <img alt="${x.visitorNickname|escape}" onerror="this.src=location.f40" class="cwd bdwa bdc0" src="${fn1(x.visitorName)}&r=${visitor.imageUpdateTime}"/>
      {else}
      <img alt="${x.visitorNickname|escape}" onerror="this.src=location.f40" class="cwd bdwa bdc0" src="${fn1(x.visitorName)}"/>
      {/if}
      </a>
      <div class="cwd vname thide">
        {if x.moveFrom=='wap'}
          <a class="noul pnt" target="_blank" href="http://blog.163.com/services/wapblog.html?frompersonalbloghome"><span title="来自网易手机博客" class="iblock wapIcon"> </span></a>
        {elseif x.moveFrom=='iphone'}
          <a class="noul pnt" target="_blank"><span title="来自iPhone客户端" class="iblock iphoneIcon"> </span></a>
        {elseif x.moveFrom=='android'}
          <a class="noul pnt" target="_blank"><span title="来自Android客户端" class="iblock androidIcon"> </span></a>
        {elseif x.moveFrom=='mobile'}
          <a class="noul pnt" target="_blank" href="http://blog.163.com/services/emsblog.html?frompersonalbloghome"><span title="来自网易短信写博" class="iblock wapIcon"> </span></a>
        {/if}
        <a class="fc03 m2a"  target="_blank" hidefocus="true" href="http://blog.163.com/${x.visitorName}/">
          ${fn(x.visitorNickname,8)|escape}
        </a>
      </div>
    </div>
    {/if}
    {/list}

<#--最新日志，群博日志--> <#--推荐日志-->

<p class="fc06">推荐过这篇日志的人：</p>
    <div>
      {list a as x}
      {if !!x}
      <div class="iblock nbw-fce nbw-f40">
        <a class="fc03 noul" target="_blank" hidefocus="true" href="http://blog.163.com/${x.recommenderName}/">
        <img alt="${x.recommenderNickname|escape}" onerror="this.src=location.f40" class="cwd bdwa bdc0" src="${fn1(x.recommenderName)}"/>
        </a>
        <div class="cwd thide">
          <a class="fc03 m2a" target="_blank" hidefocus="true" href="http://blog.163.com/${x.recommenderName}/">
            ${fn(x.recommenderNickname,6)|escape}
          </a>
        </div>
      </div>
      {/if}
      {/list}
    </div>
    {if !!b&&b.length>0}
    <p  class="fc06">他们还推荐了：</p>
    <ul>
    {list b as y}
      {if !!y}
        <li class="rrb"><span class="iblock">·</span><a class="fc03 m2a" target="_blank" href="http://blog.163.com/${y.recommendBlogPermalink}/?from=blog/static/1226803902010329101518870">${y.recommendBlogTitle|escape}</a></li>
      {/if}
    {/list}
    </ul>
    {/if}

<#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇，下一篇--> <#-- 热度 -->

{list a as x}
    {if !!x}
    <div class="hotItem iblock nbw-fce nbw-f40">
      <a class="fc03 noul" target="_blank" hidefocus="true" href="http://blog.163.com/${x.publisherUsername}/">
      {if x.publisherUsername==visitor.userName}
      <img alt="${x.publisherNickname|escape}" onerror="this.src=location.f40" class="cwd bdwa bdc0" src="${fn1(x.publisherUsername)}&r=${visitor.imageUpdateTime}"/>
      {else}
      <img alt="${x.publisherNickname|escape}" onerror="this.src=location.f40" class="cwd bdwa bdc0" src="${fn1(x.publisherUsername)}"/>
      {/if}
      </a>
      <div class="cwd vname thide">
        <a class="fc03 m2a"  target="_blank" hidefocus="true" href="http://blog.163.com/${x.publisherUsername}/">
          ${fn(x.publisherNickname,8)|escape}
        </a>
      </div>
      <a class="f-myLikeIcons hottype {if x.type==1} js-liketype{elseif x.type==2} js-reblogtype{elseif x.type==3} js-sharetype{else}{/if}" target="_blank" hidefocus="true" href="http://blog.163.com/${x.publisherUsername}/"> </a>
    </div>
    {/if}
    {/list}

<#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->

页脚

我的照片书 - 手机博客 - 下载LOFTER APP - 订阅此博客

herb

导航

日志

Oracle新建用户，授权，建表空间

历史上的今天

最近读者

热度

评论

页脚