首页 > 代码库 > ORACLE 表空间管理

ORACLE 表空间管理

2024-07-26 00:38:25   212人阅读

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

 

ORACLE 表空间管理


联系
我们