1.创建表空间
create tablespace TBSPACE_NAME datafile '/data/oracle_data/TBSPACE_NAME .dbf' size 1500M autoextend on next 5M maxsize 3000M;
2.创建用户及其授权
create user USERNAME identified by PASSWORD default tablespace tablespace_name temporary tablespace temp profile DEFAULT;
grant connect to USERNAME with admin option;
grant dba to USERNAME with admin option;
grant alter any index to USERNAME ;
grant alter any table to USERNAME ;
grant create any index to USERNAME ;
grant create any table to USERNAME ;
grant unlimited tablespace to USERNAME with admin option;
3.临时表空间爆满后修改启大小
--创建中转临时表空间
3.1.create temporary tablespace TEMP02 TEMPFILE '/u01/app/oracle/oradata/perm/temp02.dbf' SIZE 1024M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED;
--改变缺省临时表空间 为刚刚创建的新临时表空间temp02
3.2.alter database default temporary tablespace temp02;
--***原来临时表空间
3.3.drop tablespace temp including contents and datafiles;
--重新创建临时表空间
3.4.create temporary tablespace TEMP TEMPFILE '/u01/app/oracle/oradata/perm/temp01.dbf' SIZE 1024M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED;
--重置缺省临时表空间为新建的temp表空间
3.5.alter database default temporary tablespace temp;
--***中转用临时表空间
3.6.drop tablespace temp02 including contents and datafiles;
--重新指定用户表空间为重建的临时表空间
3.7.alter user sys temporary tablespace temp;
4.删除操作
4.1 删除用户 drop user username cascade;
4.2 删除临时表空间 drop tablespace tmptablespacename including contents
and datafiles cascade constraint;
4.3 删除表空间 drop tablespace tablespacename including contents
and datafiles cascade constraint;
5.按用户备份及其还原
备份:EXP username/password BUFFER=64000 FILE=E:\XXX\XXX.dmp OWNER=username
还原: imp username/password@database file=filename log=logname formuser=username touser=username buffer=10240
6.利用数据泵备份与还原
1.创建逻辑文件目录
create or replace directory hycsdata (目录名) as 'D:\XXX(目录名)';
2.授权
grant read, write on directory hycsdata to username;
3.导出
expdp DSSZ/123@orcl directory=hycsdata dumpfile=hycsdata.dmp logfile=aa.log
4.还原
impdp dssz/123@orcl directory=hycsdata dumpfile=hycsdata.dmp logfile=aa.log remap_schema=dssz:dssz
7.查询表空间使用情况
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC;
8.查看表空间是否具有自动扩展的能力
SELECT T.TABLESPACE_NAME,D.FILE_NAME,
D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS
FROM DBA_TABLESPACES T,DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME
ORDER BY TABLESPACE_NAME,FILE_NAME;
9.增加表空间大小的四种方法
9.1 给表空间增加数据文件
ALTER TABLESPACE app_data ADD DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF' SIZE 50M;
9.2 新增数据文件,并且允许数据文件自动增长
ALTER TABLESPACE app_data ADD DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP04.DBF' SIZE 50M AUTOEXTEND ON NEXT 5M MAXSIZE 100M;
9.3 允许已存在的数据文件自动增长
ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF' AUTOEXTEND ON NEXT 5M MAXSIZE 100M;
9.4 手工改变已存在数据文件的大小
ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP02.DBF' RESIZE 100M;