精华内容
下载资源
问答
  • select df.tablespace_name “表空间名”,totalspace “总空间M”,freespace “剩余空间M”,round((1-freespace/totalspace)*100,2) “使用率%” from (select tablespace_name,round(sum(bytes)/1024/1024) ...

    select
    a.tablespace_name
    ,a.total_G
    ,b.fee_G
    ,trunc((a.total_G-b.fee_G),5) as “used_G”,trunc((a.total_G-b.fee_G)/a.total_G,5)*100 as “used%”
    from
    (select
    a.tablespace_name
    ,trunc(sum(a.bytes)/1024/1024/1024,3) As total_G
    from dba_data_files a
    group by
    a.tablespace_name
    ) a
    left join (select
    a.tablespace_name
    ,trunc(sum(a.bytes)/1024/1024/1024,3) as fee_G
    from dba_free_space a
    group by
    a.tablespace_name
    ) b
    on a.tablespace_name = b.tablespace_name
    order by 4 desc;

    select sum(bytes)/1024/1024/1024 from dba_segments; 单位为g

    select df.tablespace_name “表空间名”,totalspace “总空间M”,freespace “剩余空间M”,round((1-freespace/totalspace)*100,2) “使用率%”
    from
    (select tablespace_name,round(sum(bytes)/1024/1024) totalspace
    from dba_data_files
    group by tablespace_name) df,
    (select tablespace_name,round(sum(bytes)/1024/1024) freespace
    from dba_free_space
    group by tablespace_name) fs
    where df.tablespace_name=fs.tablespace_name;

    展开全文
  • 查看修改表空间语句,能够通过命令语句去实现表空间扩展,利于数据库维护。
  • 一 Oracle数据库查看表空间sql语句 1、oracle查看表空间当前用户 SQL> select username,default_tablespace from user_users; 2、oracle 查看表所属表空间 SQL> SELECT TABLE_NAME,TABLESPACE_NAME FROM ...

    一  Oracle数据库查看表空间sql语句

    1、oracle查看表空间当前用户

    SQL>  select  username,default_tablespace  from user_users;

    2、oracle 查看表所属表空间

    SQL>  SELECT
     TABLE_NAME,TABLESPACE_NAME
    FROM USER_TABLES
    where TABLE_NAME = 'test_table'

    3、oracle查看表空间大小(单位不是GB)

    SQL>   SELECT
     a.tablespace_name                        "表空间名",
     total                                    "表空间大小",
     free                                     "表空间剩余大小",
     ( total - free )                         "表空间使用大小",
      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

    4、oracle查看表空间大小 -单位GB

    SQL>   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;

    5.--Oracle查看表所属表空间
    SQL>   SELECT TABLE_NAME,TABLESPACE_NAME
    FROM USER_TABLES
    WHERE TABLE_NAME='test_table';

    二  查看Oracle数据库表空间剩余

    1)查看表空间物理文件的名称及大小 
    SQL> SELECT tablespace_name,
    file_id,
    file_name,
    round(bytes/(1024*1024*1024), 2)||' GB' total_space
    FROM dba_data_files
    ORDER BY tablespace_name;
    2)查看表空间的使用情况 
    SELECT round(SUM(bytes)/(1024*1024*1024),2)||' GB' AS free_space, tablespace_name 
    FROM dba_free_space 
    GROUP BY tablespace_name; 


    3)查看表空间的使用情况 
    SELECT a.tablespace_name, 
    round(a.bytes/(1024*1024*1024),2)||' GB' total, 
    round(b.bytes/(1024*1024*1024),2)||' GB' used, 
    round(c.bytes/(1024*1024*1024),2)||' GB' free, 
    round((b.bytes * 100) / a.bytes)||'%' "% USED ", 
    round((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; 

    三  查看、修改Oracle数据库表空间大小

    查看所有表空间信息
    select tablespace_name,file_id,bytes/1024/1024,file_name
    from dba_data_files order by file_id;
     
    -- 修改表空间大小
    ALTER TABLESPACE WEBSITE ADD DATAFILE
    'D:\SDE06.DBF' SIZE 20480M;  -- 此处文件路径可以由上面查到的路径而来

     

    四  ORACLE 查看RMAN的备份信息总结

     

    1,检查某个时间段备份情况

    SELECT t.START_TIME,t.END_TIME,t.STATUS,t.OBJECT_TYPE,t.RECID
        FROM V$RMAN_STATUS t where t.START_TIME between trunc(sysdate)-2 and sysdate
        and t.OPERATION='BACKUP';
    SQL> SELECT ROW_TYPE,COMMAND_ID,OPERATION,STATUS,OBJECT_TYPE FROM V$RMAN_STATUS WHERE START_TIME >= trunc(sysdate);
    

    2,从V$RMAN_BACKUP_JOB_DETAILS中查看RMAN备份更详细的信息

    SQL> set sqlformat ansiconsole
    SQL> SELECT START_TIME,END_TIME,OUTPUT_DEVICE_TYPE,STATUS,ELAPSED_SECONDS,COMPRESSION_RATIO,INPUT_BYTES_DISPLAY,OUTPUT_BYTES_DISPLAY
           FROM V$RMAN_BACKUP_JOB_DETAILS where START_TIME>=trunc(sysdate)-1 ORDER BY START_TIME DESC;
    SQL> SELECT t.COMMAND_ID as "备份名",t.STATUS as "狀態",t.START_TIME as "開始時間",t.TIME_TAKEN_DISPLAY as "所用時間",t.END_TIME as "結束時間"
          ,t.INPUT_TYPE as "類型",t.OUTPUT_DEVICE_TYPE as "輸出設備",t.INPUT_BYTES_DISPLAY as "輸入大小",t.OUTPUT_BYTES_DISPLAY as "輸出大小"
           ,t.OUTPUT_BYTES_PER_SEC_DISPLAY as "輸出速率(每秒)"
          FROM V$RMAN_BACKUP_JOB_DETAILS t where START_TIME>=trunc(sysdate)-7 ORDER BY START_TIME DESC;
    

    3,查看所有备份集详细信息

    SQL> 
       SELECT A.RECID "BACKUP SET",A.SET_STAMP,
            DECODE (B.INCREMENTAL_LEVEL,
                    '', DECODE (BACKUP_TYPE, 'L', 'Archivelog', 'Full'),
                    1, 'Incr-1级',
                    0, 'Incr-0级',
                    B.INCREMENTAL_LEVEL)
               "Type LV",
            B.CONTROLFILE_INCLUDED "包含CTL",
            DECODE (A.STATUS,
                    'A', 'AVAILABLE',
                    'D', 'DELETED',
                    'X', 'EXPIRED',
                    'ERROR')
               "STATUS",
            A.DEVICE_TYPE "Device Type",
            A.START_TIME "Start Time",
            A.COMPLETION_TIME "Completion Time",
            A.ELAPSED_SECONDS "Elapsed Seconds",
            A.BYTES/1024/1024/1024 "Size(G)",
            A.COMPRESSED,
            A.TAG "Tag",
            A.HANDLE "Path"
       FROM GV$BACKUP_PIECE A, GV$BACKUP_SET B
      WHERE A.SET_STAMP = B.SET_STAMP AND A.DELETED = 'NO'
      ORDER BY A.COMPLETION_TIME DESC;

    4,查找某个备份集中包含数据文件

     SELECT DISTINCT c.file#,A.SET_STAMP, D.NAME, C.CHECKPOINT_CHANGE#, C.CHECKPOINT_TIME
        FROM V$BACKUP_DATAFILE C, V$BACKUP_PIECE A, V$DATAFILE D
        WHERE A.SET_STAMP = C.SET_STAMP AND D.FILE# = C.FILE#  AND A.DELETED='NO' 
        and a.START_TIME>trunc(sysdate) AND c.set_stamp='999651695' 
        ORDER BY C.FILE#;

    5,查询某个备份集中控制文件

     SELECT DISTINCT A.SET_STAMP,D.NAME,C.CHECKPOINT_CHANGE#,C.CHECKPOINT_TIME
        FROM V$BACKUP_DATAFILE C, V$BACKUP_PIECE A, V$CONTROLFILE D
        WHERE A.SET_STAMP = C.SET_STAMP AND C.FILE# = 0 AND A.DELETED = 'NO'
        and a."START_TIME">trunc(sysdate)  AND C.SET_STAMP = '999651780';

    6,查看某个备份集中归档日志

    SELECT DISTINCT B.SET_STAMP,B.THREAD#,B.SEQUENCE#,B.FIRST_TIME,B.FIRST_CHANGE#,B.NEXT_TIME,B.NEXT_CHANGE#
        FROM V$BACKUP_REDOLOG B, V$BACKUP_PIECE A
        WHERE A.SET_STAMP = B.SET_STAMP AND A.DELETED = 'NO'
        and a.START_TIME>trunc(sysdate) AND B.SET_STAMP ='999651783'
        ORDER BY THREAD#, SEQUENCE#;

    7,查看某个备份集SPFILE

    SELECT DISTINCT A.SET_STAMP, B.COMPLETION_TIME,HANDLE
        FROM V$BACKUP_SPFILE B, V$BACKUP_PIECE A
        WHERE A.SET_STAMP = B.SET_STAMP AND A.DELETED = 'NO'
        and a."START_TIME">trunc(sysdate) and B.SET_STAMP ='999651798';

    8,查询RMAN的配置信息

    SELECT NAME,VALUE FROM V$RMAN_CONFIGURATION;

     

     

     

     

     

     

     

     

     

     

     

     

     


     

    展开全文
  • SQL语句大全—查看表空间 SQL语句大全—查看表空间 SQL语句大全—查看表空间
  • Oracle数据库查看表空间sql语句

    万次阅读 2018-09-03 15:49:51
    1、oracle查看表空间当前用户 select username,default_tablespace from user_users; 2、oracle 查看表所属表空间 SELECT TABLE_NAME,TABLESPACE_NAME FROM USER_TABLES where TABLE_NAME = 'test_...

    1、oracle查看表空间当前用户

    select 
     username,default_tablespace 
    from user_users;

    2、oracle 查看表所属表空间

    SELECT 
     TABLE_NAME,TABLESPACE_NAME 
    FROM USER_TABLES 
    where TABLE_NAME = 'test_table'

    3、oracle查看表空间大小(单位不是GB)

    SELECT 
     a.tablespace_name                        "表空间名",
     total                                    "表空间大小",
     free                                     "表空间剩余大小",
     ( total - free )                         "表空间使用大小",
      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

    4、oracle查看表空间大小 -单位GB

    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;
    展开全文
  • 一、查看表空间使用情况 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 ...

    一、查看表空间使用情况
    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.smtsavaila,sys.smts_avail a, sys.smts_used b, sys.sm$ts_free c
    WHERE a.tablespace_name = b.tablespace_name
    AND a.tablespace_name = c.tablespace_name;

    二、查看表空间文件(修改语句使用到)
    select tablespace_name, file_id, file_name, round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name

    三、cmd命令 sqlplus sys as sysdba
    执行下面语句
    alter database datafile ‘C:\APP\ADMINISTRATOR\PRODUCT\12.1.0\DBHOME_1\DATABASE\SDE_TBS’ resize 8000m

    展开全文
  • 0.有时候系统插入数据插入不进去,且系统中代码没有明显报错的时候,有可能就是你的数据库表空间没有设置自增长,表空间满了,以下是一些操作ORACLE数据库表空间语句: 1.查看已经开启和未开启自动增长的表空间 ...
  • ORACLE查看表空间使用率语句 SELECT a.tablespace_name “表空间名”, total “表空间大小”, free “表空间剩余大小”, (total - free) “表空间使用大小”, total / (1024 * 1024 ) “表空间大小(M)”, free / ...
  • 本文档详细介绍如何查看Oracle表空间,查询表空间使用情况,扩充表空间,删除表空间文件等详细sql。好不容易找到,分享给大家,希望对大家有帮助。
  • 转载来自如下 版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。...--Oracle查看表空间大小(单位不是GB)SELECT a.tablespace_name, --表空间名total, ...
  • 本文给大家分享一个oracle查看表空间已分配和未分配空间的语句,大家参考使用吧
  • 剩余表空间语句,很多人都会用会写,temp临时表空间使用率语句就复杂点了。经过日常使用和搜索回来的资料,下面总结下吧!语句都验证过的,sys用户可用--查看剩余表空间使用情况 语句set pagesize 100;set linesize ...
  • oracle查看表空间创建语句

    千次阅读 2019-01-04 16:29:28
    SELECT dbms_lob.substr(DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name))FROM DBA_TABLESPACES TS; 转载于:https://blog.51cto.com/12926044/2338874
  • 查看表空间名称、大小、使用大小、剩余大小和使用率: SELECT a.tablespace_name "表空间名称", total / (1024 * 1024) "表空间大小(M)", free / (1024 * 1024) "表空间剩余大小(M)", (total - free) / (1024 *...
  • 表空间使用情况包括:查看表空间的名称及大小/查看表空间物理文件的名称及大小/查看回滚段名称及大小等等感兴趣的你可以参考下本
  • 查看表空间的使用率的sql语句 注:转载来自 “ ITPUB博客 ” 作者:九十一万里 --1、查看表空间的名称及大小 SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size FROM dba_tablespaces t, dba...
  • select b.file_name FileName,  b.tablespace_name "Tablespace",  round(b.bytes / 1024 / 1024 / 1024, 2) "SpaceSize(G)",  round((b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024 / 1024,
  • ########1、查看表空间以及扩展 SELECT a.tablespace_name "表空间名称", total / (1024 * 1024) "表空间大小(M)", free / (1024 * 1024) "表空间剩余大小(M)", (total - free) / (1024 * 1024 ) "表空间使用...
  • 前提:要求使用SQL PLUS。 SQL> set pages 0 SQL> set long 1000000 SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name) FROM DBA_TABLESPACES TS;
  • Oracle查看表结构语句

    千次阅读 2020-06-30 14:14:22
    查看所有表空间 select name from v$tablespace; 根据表空间名称查询空间内部所有表名 select TABLE_NAME,TABLESPACE_NAME from dba_tables where TABLESPACE_NAME='表空间名称'; 查询当前用户的表 select table_...
  • 表空间名称及状态等信息: ...表空间的创建语句: SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name) FROM DBA_TABLESPACES TS; ----查看数据库表空间的信息 select *from v$datafile;
  • 查看oracle表空间和临时表空间的使用情况、当前用户下的表所占空间大小表空间物理文件的名称和大小,是oracle DBA的利器哦
  • 1、使用sys用户,在sqlplus中,执行这样的命令set long 2000select dbms_metadata.get_ddl('TABLESPACE','表空间名称') FROM DUAL;2、以users表空间为例SQL>setlong2000; SQL>selectdbms_metadata.get_ddl('...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,554
精华内容 621
关键字:

查看表空间语句