//创建大文件表空间
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 );
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 );