导读:1建站知识清理TEMP临时表空间、清理UNDO表空间、清理TEMPTABS表空间等等，有类似需求的朋友可以参考下哈网站建设企业网站建设。

1.查询表空间使用情况: sqlplus system/manager@topprod

复制代码 代码如下:

SQL>@q_tbsFREE

2.查询temp使用方法: sqlplus system/manager@topprod

复制代码 代码如下:

SQL>SELECT d.tablespace_name tablespace_name , d.status tablespace_status , NVL(a.bytes, 0) tablespace_size , NVL(t.bytes, 0) used , TRUNC(NVL(t.bytes / a.bytes * 100, 0)) used_pct , NVL(s.current_users, 0) current_users FROM sys.dba_tablespaces d , ( select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name ) a , ( select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name ) t , v$sort_segment s WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND d.tablespace_name = s.tablespace_name(+) AND d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY';

2.清理TEMP临时表空间：(在无用户连接的状况下操作,最好在清理之前重启一下数据库)

复制代码 代码如下:

#重启数据库 sqlplus '/as sysdba' SQL>shutdown immediate SQL>startup #创建一个临时表空间temp02，用作临时替换 SQL>create temporary tablespace temp02 tempfile '/u2/oradb/oradata/topprod/temp02.dbf' size 10M autoextend on next 10M; #将系统临时表空间指向temp02 SQL>alter database default temporary tablespace temp02; #删除原来的临时表空间temp SQL>drop tablespace temp including contents and datafiles; #创建新的临时表空间temp SQL>create temporary tablespace temp tempfile '/u2/oradb/oradata/topprod/temp01.dbf' size 4096M autoextend on next 100M; #将系统临时表空间指回temp SQL>alter database default temporary tablespace temp; #删除临时表空间tem网seo优化趋势p02 SQL>drop tablespace temp02 including contents and datafiles;

3.清理UNDO表空间：(在无用户连接的状况下操作,最好在清理之前重启一下数据库)

复制代码 代码如下:

#重启数据库 sqlplus '/as sysdba' SQL>如何seo优化推广网站shutdown immediate SQL>startup #创建一个UNDO表空间undotbs2，用作临时替换 SQL>create undo tablespace undotbs2 datafile '/u2/oradb/oradata/topprod/undotbs02.dbf' size 10M autoextend on next 10M; #将系统UNDO表空间指向undotbs2 SQL>alter system set undo_tablespace=undotbs2 scope=both; #确保所有在UNDOTBS1的undo segment都已offline SQL> select SEGMENT_NAME ,STATUS ,TABLESPACE_NAME from dba_rollback_segs; #删除原来的UNDO表空间undotbs1 SQL>drop tablespace undotbs1 including contents and datafiles; #创建新的临时表空间undotbs1 SQL>create undo tablespace undotbs1 datafile '/u2/oradb/oradata/topprod/undotbs01.dbf' size 4096M; #将系统UNDO表空间指回undotbs1 SQL>alter system set undo_tablespace=undotbs1 scope=both; #删除UNDO表空间undotbs2 SQL>drop tablespace undotbs2 including contents and datafiles;

关键词标签: 代码 常用