oracle_创建表空间_创建和授权用户

//创建大文件表空间

create BIGFILE tablespace zhang 
datafile 'D:\oracle\product\10.2.0\oradata\zhang.DBF' 
size 64M 
autoextend on next 64M maxsize 1048576M; 

//删除表空间

drop tablespace xu including contents and datafiles;

//创建大文件临时表空间

CREATE BIGFILE TEMPORARY TABLESPACE mybigtmp 
TEMPFILE 'D:\oracle\product\10.2.0\oradata\mybigtmp.DBF' SIZE 1024M UNIFORM SIZE 64K;
 
//修改temp表空间
ALTER DATABASE TEMPFILE 'D:\oracle\product\10.2.0\oradata\mybigtmp.DBF' RESIZE 10240M;


temp表空间的重建比较简单，不需要关闭数据库。步骤如下：


1。以system用户进入；
2.create temporary tablespace  TEMP2 TEMPFILE ‘/oracle/u01/oracle/oradata/BIZDB/temp02.dbf’ SIZE 512M REUSE AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED;   –创建中转临时表空间
3.alter database default  temporary tablespace  temp2; –改变缺省临时表空间 为刚刚创建的新临时表空间temp2
4.drop tablespace temp including contents and datafiles;–删除原来临时表空间
5.create temporary tablespace  TEMP TEMPFILE ‘/oracle/u01/oracle/oradata/BIZDB/temp01.dbf’ SIZE 512M REUSE AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED;   –重新创建临时表空间
6.alter database default  temporary tablespace  temp; –重置缺省临时表空间为新建的temp表空间
7.drop tablespace temp2 including contents and datafiles;–删除中转用临时表空间
8.alter user narrowad temporary tablespace temp;   –重新指定用户表空间为重建的临时表空间
完成！


//创建用户并指定表空间   

create user zhang identified by password    
default tablespace zhang   
temporary tablespace mybigtmp;   

alter user zhang identified by zhang;//修改密码


//给用户授予权限   

grant dba,connect,resource to zhang;  
  
commit


//查看当前表空间

select df.tablespace_name "表空间名",totalspace "总空间M",freespace "剩余空间M",round((1-freespace/totalspace)*100,2) "使用率%" 
from 
(select tablespace_name,round(sum(bytes)/1024/1024) totalspace 
from dba_data_files 
group by tablespace_name) df, 
(select tablespace_name,round(sum(bytes)/1024/1024) freespace 
from dba_free_space 
group by tablespace_name) fs 
where df.tablespace_name=fs.tablespace_name;


//创建DBLINK

create public database link NEW
  connect to sys identified by system
  using '(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 130.34.22.7)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME =ycora9 )
    )
  )';
  
  
  ALTER  TEMPORARY TABLESPACE mybigtmp DEFAULT STORAGE (INITIAL 10G NEXT 1G );