Second round of the capacity metrics

SQL Server

Size of database :

EXEC sp_spaceused

Size of all the tables > 10mo :

--Removes the procedure if it already exists
IF OBJECT_ID('dbo.GetSizeOfAllTables') IS NOT NULL
DROP PROCEDURE dbo.GetSizeOfAllTables
GO
--Gets the number of rows and size in MB of every table in the current database
CREATE PROCEDURE GetSizeOfAllTables
AS
DECLARE @TableName VARCHAR(100)

--Cursor storing all the user tables names
DECLARE tableCursor CURSOR
FOR
SELECT [name]
FROM dbo.sysobjects
WHERE  OBJECTPROPERTY(id, N'IsUserTable') = 1
FOR READ ONLY

--We create a temp table to hold the result of sp_spaceused
CREATE TABLE #TempTable
(
    tableName varchar(100),
    numberOfRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
)

OPEN tableCursor
--Get the first table name from the cursor
FETCH NEXT FROM tableCursor INTO @TableName
WHILE (@@Fetch_Status >= 0)
BEGIN
    --Dump the results of the sp_spaceused query to the temp table
    INSERT  #TempTable
        EXEC sp_spaceused @TableName

    FETCH NEXT FROM tableCursor INTO @TableName
END
CLOSE tableCursor
DEALLOCATE tableCursor

--Select all records and compute the data we want
SELECT * FROM
    (SELECT tableName, RTRIM(numberOfRows) as 'rows', ROUND((CONVERT(FLOAT, SUBSTRING(dataSize, 1, LEN(dataSize)-3)) + CONVERT(FLOAT, SUBSTRING(indexSize, 1, LEN(indexSize)-3)))/1024, 3) as 'Size'
    FROM #TempTable) T
WHERE Size > 10

--Clean up behind us
DROP TABLE #TempTable
GO
EXEC GetSizeOfAllTables

Current users

--Create a table to hold the data from sp_who
CREATE TABLE #temp
(
    spid varchar(100),
    ecid varchar(100),
    statuts varchar(100),
    loginame varchar(100),
    hostname varchar(50),
    blk varchar(50),
    dbname varchar(50),
    cmd varchar(50),
    requestID varchar(50),
);

INSERT INTO #temp EXEC sp_who

--Get the data we want
SELECT COUNT(*) AS cnt, loginame FROM #temp WHERE dbname = 'OnlyForTest' GROUP BY loginame ORDER BY cnt DESC;

--Clean up
DROP TABLE #temp

Current queries

SELECT COUNT(*) AS cnt, text FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle) WHERE status='running' GROUP BY text ORDER BY cnt DESC;

Oracle

Size of the instance

SELECT sys_context('USERENV','DB_NAME'), SUM(bytes)/1024/1024 "Mb" FROM dba_data_files

Size of the tablespaces

SELECT b.tablespace_name, tbs_size SizeMB, a.free_space FreeMB
FROM  (SELECT tablespace_name, round(SUM(bytes)/1024/1024 ,2) AS free_space
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes)/1024/1024 AS tbs_size
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name(+)=b.tablespace_name

Size of the schemas (only the one above 10MB)

SELECT owner, (SUM(bytes)/1024/1024)
FROM dba_segments WHERE owner NOT IN ('SYS')
GROUP BY owner HAVING (SUM(bytes)/1024/1024) > 10

Size of the tables (only the one above 10MB)

SELECT owner, table_name, TRUNC(sum(bytes)/1024/1024) Meg
FROM
    (SELECT segment_name table_name, owner, bytes
    FROM dba_segments
    WHERE segment_type = 'TABLE'
    UNION ALL
    SELECT i.table_name, i.owner, s.bytes
    FROM dba_indexes i, dba_segments s
    WHERE s.segment_name = i.index_name
    AND   s.owner = i.owner
    AND   s.owner = i.owner
    AND   s.segment_type = 'INDEX'
    UNION ALL
    SELECT l.table_name, l.owner, s.bytes
    FROM dba_lobs l, dba_segments s
    WHERE s.segment_name = l.segment_name
    AND   s.owner = l.owner
    AND   s.segment_type = 'LOBSEGMENT'
    UNION ALL
    SELECT l.table_name, l.owner, s.bytes
    FROM dba_lobs l, dba_segments s
    WHERE s.segment_name = l.index_name
    AND   s.owner = l.owner
    AND   s.segment_type = 'LOBINDEX')
WHERE owner NOT IN ('SYS')
GROUP BY table_name, owner
HAVING SUM(bytes)/1024/1024 > 10
ORDER BY SUM(bytes) desc

Current users and queries

SELECT COUNT(*) AS cnt, a.username, b.sql_text
FROM v$session a, v$sqlarea b
WHERE sql_address=b.address
GROUP BY a.username, b.sql_text