ORACLE 表空间管理

浏览数：24 / 时间：2015年06月12日

1：查询数据库的表空间的使用情况

SELECT F.TABLESPACE_NAME,
       (T.TOTAL_SPACE - F.FREE_SPACE) "USED (MB)",
       F.FREE_SPACE "FREE (MB)",
       T.TOTAL_SPACE "TOTAL (MB)",
       (ROUND((F.FREE_SPACE / T.TOTAL_SPACE) * 100)) || ‘% ‘ PER_FREE
  FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BLOCKS * (SELECT VALUE / 1024
                                     FROM V$PARAMETER
                                    WHERE NAME = ‘db_block_size‘) / 1024)) FREE_SPACE
          FROM DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F,
       (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES / 1048576)) TOTAL_SPACE
          FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME) T
 WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME

2:查询当前的数据库中表的大小排序

SELECT owner,
       DECODE(partition_name,
              NULL,
              segment_name,
              segment_name || ‘:‘ || partition_name) NAME,
       segment_type,
       tablespace_name,
       BYTES / 1024 / 1024,
       initial_extent,
       next_extent,
       pct_increase,
       extents,
       max_extents
  FROM dba_segments t
 WHERE 1 = 1
   AND extents > 1
   and t.tablespace_name = ‘TBS_COMMON‘ --and t.partition_name  like ‘%__tb%‘
 ORDER BY BYTES / 1024 / 1024 DESC --, 3

3：移动表到另外一个较空闲的表空间

alter table tablename move tablespace tablespacenamme parallel 8;

4：修改oracle的默认的时间数据的展示形式

alter session set nls_date_format = ‘yyyy-mm-dd hh24:mi:ss‘

5：查看对表的操作记录

SELECT t.sql_text, t.first_load_time, t.last_load_time, t.module, t.action FROM v$sqlarea t WHERE upper(t.sql_text) LIKE ‘%%‘ ORDER BY t.first_load_time DESC