SQL Server空间与Oracle表空间查看

IF OBJECT_ID('tempdb.dbo.#spacetable') IS NOT NULL
    DROP TABLE dbo.#spacetable;

create table #spacetable
(
host_name varchar(100),
instance_name varchar(100),
database_name varchar(500) ,
file_id int,
file_name varchar(100),
file_full_name varchar(300),
file_type varchar(30),
file_increase varchar(20),
file_increase_mode varchar(20),
file_increase_value varchar(20),
max_size varchar(100),
file_size DECIMAL(12,2),
space_used DECIMAL(12,2),
space_usage DECIMAL(12,2)
)

declare @name  varchar(100) --dbname
declare @sql nvarchar(3000) --temp sql
declare cursor1 cursor for
    SELECT name from sys.databases where state=0;

open cursor1
fetch next from cursor1 into @name
while @@fetch_status = 0
begin

if @name <>'tempdb' 
begin
set @sql = 'use [' +  @name +'];insert into #spacetable '+
'
SELECT 
 convert(varchar,SERVERPROPERTY(''MachineName'')) AS [hostname],
 convert(varchar,isnull(SERVERPROPERTY(''InstanceName''),''MSSQLSERVER'')) AS [实例名称],
b.name
,a.file_id [文件编号]
,a.name [名称]
    ,a.physical_name AS [文件名]  
    ,a.type_desc AS [文件类型]  
--    ,A.growth 是否增长    
    ,CASE WHEN A.growth =0 THEN ''fixed_size'' ELSE ''auto'' end [是否增长]  
--    ,CASE WHEN A.growth > 0 AND is_percent_growth = 0 THEN ''增量为固定大小''  
--     WHEN A.growth > 0 AND is_percent_growth = 1 THEN ''增量为百分比''  
--     ELSE ''fixed_size'' END AS [是否增长]  
    ,A.is_percent_growth [增长模式]
    ,CASE WHEN A.growth > 0 AND is_percent_growth = 0 THEN cast(cast(a.growth*1.0/128 as decimal(12,0)) AS VARCHAR)+''MB''  
        WHEN A.growth > 0 AND is_percent_growth = 1 THEN cast(cast(a.growth AS decimal(12,0)) AS VARCHAR)+''%''  
        ELSE ''fixed_size'' end AS [增长值(%或MB)] 
    ,A.max_size/128 [最大值MB]   
    ,cast(a.[size]*1.0/128 as decimal(12,2)) AS [文件大小(MB)]  
    , case when a.type_desc <>''FILESTREAM'' then
    CAST( fileproperty(a.name,''SpaceUsed'')/(8*16.0) AS DECIMAL(12,2))  
    else 99.99 end AS [所占空间(MB)]
    ,
    case when a.type_desc <>''FILESTREAM'' then
    CAST( (fileproperty(a.name,''SpaceUsed'')/(8*16.0))/(a.size*1000.0/(8*16.0))*100000.0  AS DECIMAL(12,2)) 
    else 99.99 end AS [所占空间率%] 
FROM sys.master_files  a
inner join SYS.databases  as b on a.[database_id]=b.[database_id] 
where a.database_id=DB_ID(DB_NAME()) 
'
end
else
begin
set @sql = 'use [' +  @name +'];insert into #spacetable ' +
'
SELECT 
 convert(varchar,SERVERPROPERTY(''MachineName'')) AS [hostname],
 convert(varchar,isnull(SERVERPROPERTY(''InstanceName''),''MSSQLSERVER'')) AS [实例名称],
b.name
,a.file_id [文件编号]
,a.name [名称]
    ,a.physical_name AS [文件名]  
    ,a.type_desc AS [文件类型]  
--    ,A.growth 是否增长    
    ,CASE WHEN A.growth =0 THEN ''fixed_size'' ELSE ''auto'' end [是否增长]  
--    ,CASE WHEN A.growth > 0 AND is_percent_growth = 0 THEN ''增量为固定大小''  
--     WHEN A.growth > 0 AND is_percent_growth = 1 THEN ''增量为百分比''  
--     ELSE ''fixed_size'' END AS [是否增长]  
    ,A.is_percent_growth [增长模式]
    ,CASE WHEN A.growth > 0 AND is_percent_growth = 0 THEN cast(cast(a.growth*1.0/128 as decimal(12,0)) AS VARCHAR)+''MB''  
        WHEN A.growth > 0 AND is_percent_growth = 1 THEN cast(cast(a.growth AS decimal(12,0)) AS VARCHAR)+''%''  
        ELSE ''fixed_size'' end AS [增长值(%或MB)] 
    ,A.max_size [最大值MB]   
    ,cast(a.[size]/128 as decimal(12,2)) AS [文件大小(MB)]  
,case 
    when c.unallocated_extent_page_count is null then 0
    when c.unallocated_extent_page_count/128> a.size/128 then 0
    else
    a.size/128 -  c.unallocated_extent_page_count*1.0/128
 end [所占空间(MB)] 
,case 
    when c.unallocated_extent_page_count is null then 0
    when c.unallocated_extent_page_count/128 > a.size/128 then 0
    else
    a.size/128 -  c.unallocated_extent_page_count*1.0/128
  end
    /  (a.size*1000/128)*100000  [所占空间率%]
FROM sys.master_files  a
inner join SYS.databases  as b on a.[database_id]=b.[database_id] 
left join sys.dm_db_file_space_usage c
on a.[database_id]=c.[database_id]
and a.file_id=c.file_id
where a.database_id=DB_ID(DB_NAME()) 
'
 END


 -- print(@sql)
 exec(@sql)

 fetch next from cursor1 into @name 
end
close cursor1                   --close
deallocate cursor1

 --sys.dm_db_file_space_usage;  
select * from #spacetable
order by database_name, file_id

sqlplus / as sysdba

set line 200

SELECT   s.tablespace_name,
           s.cursize AS "CURRENT(MB)",
           s.MAXSIZE AS "MAX(MB)",
           TRUNC (NVL2 (f.free, s.cursize - f.free, s.cursize)) AS "USED(MB)",
           s.MAXSIZE - s.cursize + NVL (f.free, 0) AS "EXTENSIBLE(MB)",
           NVL (f.free, 0) AS "FREE_IN_DATAFILE(MB)",
           round(((s.cursize - NVL (f.free, 0) ) / s.MAXSIZE),3)*100 AS USAGE_PCT
  FROM     (SELECT   tablespace_name,
                     TRUNC (SUM (BYTES) / 1024 / 1024) AS cursize,
                     TRUNC (SUM (CASE
                   WHEN (AUTOEXTENSIBLE='NO') THEN BYTES 
                                   WHEN (   maxbytes < BYTES
                                         OR maxbytes IS NULL) THEN BYTES
                                   ELSE maxbytes
                                 END) / 1024 / 1024) AS MAXSIZE
            FROM     dba_data_files
            GROUP BY tablespace_name) s,
           (SELECT   frees.tablespace_name,
                     TRUNC (SUM (frees.BYTES) / 1024 / 1024) AS free
            FROM     dba_free_space frees 
            GROUP BY frees.tablespace_name) f
  WHERE    f.tablespace_name(+) = s.tablespace_name
  ORDER BY USAGE_PCT;