查看表空间使用情况

---------------

SELECT a.tablespace_name "表空间名",

       total "表空间大小",

       free "表空间剩余大小",

       (total - free) "表空间使用大小",

       total / (1024 * 1024 * 1024) "表空间大小(G)",

       free / (1024 * 1024 * 1024) "表空间剩余大小(G)",

       (total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",

       round((total - free) / total, 4) * 100 "使用率 %"

  FROM (SELECT tablespace_name, SUM(bytes) free

          FROM dba_free_space

         GROUP BY tablespace_name) a,

       (SELECT tablespace_name, SUM(bytes) total

          FROM dba_data_files

         GROUP BY tablespace_name) b

 WHERE a.tablespace_name = b.tablespace_name   order by a.tablespace_name

 --查询默认临时表空间

 select * from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE'

 select t.tablespace_name,t.file_name,t.bytes/1024/1024 file_size,t.autoextensible from dba_data_files t;

 select t.tablespace_name,t.file_name,t.bytes/1024/1024 file_size,t.autoextensible from dba_temp_files t;

 --扩展临时表空间：

 --方法一

 alter database tempfile '/oracle/oratest/temp2.dbf' resize 1G;

 alter database DATAFILE '/u01/app/oracle/oradata/amldb/users01.dbf' resize 2G;

 --方法二

 alter database tempfile '/oracle/oratest/temp2.dbf' autoextend on next 5m maxsize unlimited;

--追加物理文件

非临时表空间：ALTER TABLESPACE tsodsdat add DATAFILE '/u01/app/oracle/oradata/amldb/tsodsdat_02.dbf' SIZE 4096M;

临时表空间： ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/ora_data/temp02.dbf' size 512M reuse  autoextend on next 640K maxsize unlimited;

删除物理文件：ALTER TABLESPACE temp drop TEMPFILE '/oradata/ora_data/temp02.dbf';

--删除表空间

drop TABLESPACE test_space including contents and datafiles;

--查询表空间对应表使用情况  TSIND05 TSIND04 TSIND03 TSDAT03 TSDAT04

select * from dba_segments t where t.tablespace_name like '%TSDAT04%' and t.segment_type = 'TABLE'

select t.owner,t.segment_name,bytes/1024/1024 a from dba_segments t -- 11111 9211

where t.tablespace_name like '%TSDAT02%' and t.segment_type = 'TABLE' order by a desc

select t.owner,t.segment_name,bytes/1024/1024 a from dba_segments t   --9684 8027;

where t.tablespace_name like '%TSIND02%' and t.segment_type = 'INDEX' order by a desc

============================================================================================

--创建表空间：

--1、自增

CREATE TABLESPACE tsdat01 LOGGING DATAFILE '/u01/app/oracle/oradata/amldb/tsdat01.dbf' SIZE 5119M 

AUTOEXTEND ON NEXT 40M MAXSIZE UNLIMITED DEFAULT STORAGE (MAXEXTENTS UNLIMITED PCTINCREASE 5);

--2、非自增

CREATE SMALLFILE TABLESPACE TSDAT01 LOGGING DATAFILE  '/data/oradata/amldb/TSDAT01.dbf' SIZE 6G EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO ;

--收缩表空间

--1、查询file_id

select d.file_name,d.file_id,d.bytes/1024/1024 as d_byte from dba_data_files d;

--2、查询file_id对应的表空间使用率

select d.file_name,d.file_id,d.bytes/1024/1024 as d_byte,sum(f.bytes/1024/1024) as free_byte

from dba_data_files d,dba_free_space f

where d.file_id=f.file_id and d.file_id=5

group by d.file_name,d.file_id,d.bytes/1024/1024;

--移动表

SQL>select DISTINCT 'alter table '|| segment_name || ' move tablespace users;' from dba_extents where segment_type='TABLE' and file_id=5;

--移动索引

SQL>select DISTINCT 'alter index '|| segment_name || ' rebuild tablespace users;' from dba_extents where segment_type='INDEX' and file_id=5;

--移动分区表

SQL>select DISTINCT 'alter table '|| segment_name || ' move partition '|| partition_name || ' tablespace tsind02;' 

from dba_extents where segment_type='TABLE PARTITION' and file_id=4;

--移动分区索引

SQL>select DISTINCT 'alter index '|| segment_name || ' rebuild partition '|| partition_name || ' tablespace tsind02;' 

from dba_extents where segment_type='INDEX PARTITION' and file_id=4;

=====================================================================================

 select * from v$tablespace

 select * from sys.dba_tablespaces;

1、查看表空间的名称及大小

  SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size

    FROM dba_tablespaces t, dba_data_files d

   WHERE t.tablespace_name = d.tablespace_name

   GROUP BY t.tablespace_name;

--2、查看表空间物理文件的名称及大小

SELECT tablespace_name,

       file_id,

       file_name,

       round(bytes / (1024 * 1024), 0) total_space

  FROM dba_data_files

 ORDER BY tablespace_name;

--3、查看回滚段名称及大小

SELECT segment_name,

       tablespace_name,

       r.status,

       (initial_extent / 1024) initialextent,

       (next_extent / 1024) nextextent,

       max_extents,

       v.curext curextent

  FROM dba_rollback_segs r, v$rollstat v

 WHERE r.segment_id = v.usn(+)

 ORDER BY segment_name;

--4、查看控制文件

SELECT NAME FROM v$controlfile;

--5、查看日志文件

SELECT MEMBER FROM v$logfile;

--6、查看表空间的使用情况

SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name

  FROM dba_free_space

 GROUP BY tablespace_name;

SELECT a.tablespace_name,

       a.bytes total,

       b.bytes used,

       c.bytes free,

       (b.bytes * 100) / a.bytes "% USED ",

       (c.bytes * 100) / a.bytes "% FREE "

  FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c

 WHERE a.tablespace_name = b.tablespace_name

   AND a.tablespace_name = c.tablespace_name;

--7、查看数据库库对象

SELECT owner, object_type, status, COUNT(*) count#

  FROM all_objects

 GROUP BY owner, object_type, status;

--8、查看数据库的版本　

SELECT version

  FROM product_component_version

 WHERE substr(product, 1, 6) = 'Oracle';

--9、查看数据库的创建日期和归档方式

SELECT created, log_mode, log_mode FROM v$database;

--查看当前用户的缺省表空间

SQL >  select username, default_tablespace from user_users;

　　查看当前用户的角色

　　SQL >

  select * from user_role_privs;

　　查看当前用户的系统权限和表级权限

　　SQL >

  select * from user_sys_privs;

　　SQL >

  select * from user_tab_privs;

　　查看用户下所有的表

　　SQL >

  select * from user_tables;

　　1、用户

　　查看当前用户的缺省表空间

　　SQL >

  select username, default_tablespace from user_users;

　　查看当前用户的角色

　　SQL >

  select * from user_role_privs;

　　查看当前用户的系统权限和表级权限

　　SQL >

  select * from user_sys_privs;

　　SQL >

  select * from user_tab_privs;

　　显示当前会话所具有的权限

　　SQL >

  select * from session_privs;

　　显示指定用户所具有的系统权限

　　SQL >

  select * from dba_sys_privs where grantee = 'GAME';

　　2、表

　　查看用户下所有的表

　　SQL >

  select * from user_tables;

　　查看名称包含log字符的表

　　SQL >

  select object_name, object_id

    from user_objects

         　　where instr(object_name, 'LOG') > 0;

　　查看某表的创建时间

　　SQL >

  select object_name, created

    from user_objects

   where object_name = upper('&table_name');

　　查看某表的大小

　　SQL >

  select sum(bytes) / (1024 * 1024) as "size(M)"

    from user_segments

         　　where segment_name = upper('&table_name');

　　查看放在ORACLE的内存区里的表

　　SQL >

  select table_name, cache from user_tables where instr(cache, 'Y') > 0;

　　3、索引

　　查看索引个数和类别

　　SQL >

  select index_name, index_type, table_name

    from user_indexes

   order by table_name;

　　查看索引被索引的字段

　　SQL >

  select * from user_ind_columns where index_name = upper('&index_name');

　　查看索引的大小

　　SQL >

  select sum(bytes) / (1024 * 1024) as "size(M)"

    from user_segments

         　　where segment_name = upper('&index_name');

　　4、序列号

　　查看序列号，last_number是当前值

　　SQL >

  select * from user_sequences;

　　5、视图

　　查看视图的名称

　　SQL >

  select view_name from user_views;

　　查看创建视图的select语句

　　SQL > set view_name, text_length from user_views;

　　SQL > set long 2000;

说明：可以根据视图的text_length值设定set long 的大小

　　SQL >

  select text from user_views where view_name = upper('&view_name');

　　6、同义词

　　查看同义词的名称

　　SQL >

  select * from user_synonyms;

　　7、约束条件

　　查看某表的约束条件

　　SQL >

  select constraint_name,

         constraint_type,

         search_condition,

         r_constraint_name

         　　from user_constraints

   where table_name = upper('&table_name');

　　SQL >

  select c.constraint_name,

         c.constraint_type,

         cc.column_name

         　　from user_constraints c,

         user_cons_columns cc

         　　where c.owner = upper('&table_owner') and c.table_name = upper('&table_name')

         　　and c.owner = cc.owner and c.constraint_name = cc.constraint_name

         　　order by cc.position;

　　8、存储函数和过程

　　查看函数和过程的状态

　　SQL >

  select object_name, status

    from user_objects

   where object_type = 'FUNCTION';

　　SQL >

  select object_name, status

    from user_objects

   where object_type = 'PROCEDURE';

　　查看函数和过程的源代码

　　SQL >

  select text

    from all_source

   where owner = user

     and name = upper('&plsql_name');

1. 查看所有表空间大小SQL >

  select tablespace_name, sum(bytes) / 1024 / 1024

    from dba_data_files 2

   group by tablespace_name;

2. 已经使用的表空间大小SQL >

  select tablespace_name, sum(bytes) / 1024 / 1024

    from dba_free_space 2

   group by tablespace_name;

3. 所以使用空间可以这样计算

select a.tablespace_name, total, free, total - free used from(

  select tablespace_name, sum(bytes) / 1024 / 1024 total

    from dba_data_files

   group by tablespace_name) a, (select tablespace_name,

                                        sum(bytes) / 1024 / 1024 free

                                   from dba_free_space

                                  group by tablespace_name) b where a.tablespace_name = b.tablespace_name;

4. 下面这条语句查看所有segment的大小。Select Segment_Name, Sum(bytes) / 1024 / 1024 From User_Extents Group By Segment_Name

5. 还有在命令行情况下如何将结果放到一个文件里。SQL > spool out.txtSQL >

  select * from v$database;

SQL > spool off

查看默认临时表空间：select * from date