PL/SQL Query to view tablespace use

Standard

I love making tools that help others. What better use of my time, than to help others save theirs? When I went looking online, there were snippets of info about where to find total space or used space, but nothing that was a finished work of art. I compiled this from all the different snippets I found. It returns a table of all tablespaces, how large they are right now, and how much space they have left. It was also requested to have the AUTOEXTENSIBLE flag included, so that’s there too.

To run this, you need select access to DBA_DATA_FILES, DBA_FREE_SPACE and DBA_SEGMENTS.

I’m going to work on making this a view for users to keep track of the space usage, and proactively address running out of space prior to ETL failing.

SELECT TOTAL.TABLESPACE_NAME,
 TOTAL.SPACE_TOTAL_IN_MB,
 NVL(USED.SPACE_USED_IN_MB,0) AS SPACE_USED_IN_MB,
 AVAILABLE.SPACE_AVAILABLE_IN_MB,
 ROUND(NVL(USED.SPACE_USED_IN_MB,0)/TOTAL.SPACE_TOTAL_IN_MB,6)*100 AS PCT_USED,
 TOTAL.AUTOEXTENSIBLE
FROM
 (SELECT TABLESPACE_NAME,
 AUTOEXTENSIBLE,
 SUM(BYTES)/1024/1024.0 AS SPACE_TOTAL_IN_MB
 FROM DBA_DATA_FILES
 GROUP BY TABLESPACE_NAME,
 AUTOEXTENSIBLE
 ) TOTAL
LEFT JOIN
 (SELECT TABLESPACE_NAME,
 SUM(BYTES)/1024/1024.0 AS SPACE_AVAILABLE_IN_MB
 FROM DBA_FREE_SPACE
 GROUP BY TABLESPACE_NAME
 ) AVAILABLE
ON AVAILABLE.TABLESPACE_NAME = TOTAL.TABLESPACE_NAME
LEFT JOIN
 (SELECT TABLESPACE_NAME,
 SUM(BYTES)/1024/1024.0 AS SPACE_USED_IN_MB
 FROM DBA_SEGMENTS
 GROUP BY TABLESPACE_NAME
 )USED
ON AVAILABLE.TABLESPACE_NAME = USED.TABLESPACE_NAME
ORDER BY 1;

PL-SQL Tips and Tricks

Standard

I, like every other coder, have had a series of times where we need to get creative to get something done. Here’s my documented list of Tips and Tricks. This is by no means complete, but I’ll try and update this as I come across new things.

Get the source code for Oracle materialized views:

I needed this when I lost the file that created the MV that I was working on in DEV. Using this, I was able to get the code that was committed to the DB back.

SELECT
QUERY
FROM
USER_MVIEWS
WHERE
MVIEW_NAME = '[Materialized View Name]'

Source

Oracle Row Generator using “CONNECT BY LEVEL”

This is useful when you need to generate a table with a single column of values that are incrementing. I use this when I am working through data in the ETL process.

SELECT
LEVEL just_a_column
FROM
dual
CONNECT BY LEVEL <= [Number of rows needed]

Source

 

Bitmap Join Indexes

“In a Bitmap Index, each distinct value for the specified column is associated with a bitmap where each bit represents a row in the table. A ‘1’ means that row contains that value, a ‘0’ means it doesn’t.”

“Bitmap Join Indexes extend this concept such that the index contains the data to support the join query, allowing the query to retrieve the data from the index rather than referencing the join tables. Since the information is compressed into a bitmap, the size of the resulting structure is significantly smaller than the corresponding materialized view.”

CREATE BITMAP JOIN INDEX <index_name>
ON <table_name> (<table_name.column_name>)
FROM <table_name, table_name>
WHERE <join_condition> 
 
-- for example:
CREATE BITMAP INDEX cust_sales_us
ON    sales(customers.state)
FROM  sales, customers
WHERE sales.cust_id = customers.cust_id;

Source

PL/SQL find dependencies in other objects (v2)

Standard

I found that my prior code did not account for dependencies in views. Dammit, but there must be a way. I found it, and wrapped it up into one  semi-organized chunk of code for audit purposes. As always, let me know what you think.

--FIRST WE NEED TO DROP THE TABLES SO WE CAN USE THEM AGAIN.
DROP TABLE TEMP_DEPENDENCIES;
DROP TABLE TEMP_OBJECTS;

--NOW WE NEED TO MAKE SURE THEY ARE SET UP PROPERLY
CREATE TABLE TEMP_DEPENDENCIES (
 OBJECT_OWNER VARCHAR(30),
 OBJECT_TYPE VARCHAR(30),
 OBJECT_NAME VARCHAR(30),
 DEPENDANT_OWNER VARCHAR(30),
 DEPENDANT_OBJECT_TYPE VARCHAR(30),
 DEPENDANT_OBJECT VARCHAR(30),
 DEPENDANT_LINE VARCHAR(30)
 );

CREATE TABLE TEMP_OBJECTS (
 OBJECT_OWNER VARCHAR(30),
 OBJECT_TYPE VARCHAR(30),
 OBJECT_NAME VARCHAR(30)
 );

--INSERT ALL OJBECTS OF INTEREST
INSERT INTO TEMP_OBJECTS
WITH OBJECT_OWNERS AS (
 SELECT 'SYS' AS OBJECT_OWNER
 FROM DUAL
 )
SELECT OBJECT_OWNER,
 OBJECT_TYPE,
 OBJECT_NAME
FROM (
 SELECT OWNER AS OBJECT_OWNER,
 OBJECT_TYPE,
 OBJECT_NAME
 FROM ALL_OBJECTS
 WHERE OWNER IN (
 SELECT OBJECT_OWNER
 FROM OBJECT_OWNERS
 ) MINUS
 SELECT OWNER AS OBJECT_OWNER,
 'TABLE' AS OBJECT_TYPE,
 OBJECT_NAME
 FROM ALL_OBJECTS
 WHERE OWNER IN (
 SELECT OBJECT_OWNER
 FROM OBJECT_OWNERS
 )
 AND OBJECT_TYPE = 'MATERIALIZED VIEW'
 );

COMMIT;

--THIS LOOP LOOKS AT ALL THE DEPENDENCIES IN THE VIEWS
DECLARE L_CHAR VARCHAR2(32767);

BEGIN
 FOR OBJ IN (SELECT * FROM TEMP_OBJECTS)

 LOOP
 FOR REC IN (SELECT * FROM ALL_VIEWS)

 LOOP L_CHAR := REC.TEXT;
 IF (INSTR(L_CHAR, OBJ.OBJECT_NAME) > 0)
 THEN INSERT INTO TEMP_DEPENDENCIES VALUES (OBJ.OBJECT_OWNER,OBJ.OBJECT_TYPE,OBJ.OBJECT_NAME,REC.OWNER,'VIEW',REC.VIEW_NAME,-1); COMMIT;
 END IF;
 END LOOP;
 END LOOP;
END;

--THIS COMBINES THE VIEW DEPENDENCIES WITH ALL OTHER DEPENDENCIES
INSERT INTO TEMP_DEPENDENCIES
SELECT OBJECT_OWNER,
 OBJECT_TYPE,
 OBJECT_NAME,
 DEPENDANT_OWNER,
 DEPENDANT_OBJECT_TYPE,
 DEPENDANT_OBJECT,
 DEPENDANT_LINE
FROM TEMP_OBJECTS
INNER JOIN (
 SELECT OWNER AS DEPENDANT_OWNER,
 TYPE AS DEPENDANT_OBJECT_TYPE,
 NAME AS DEPENDANT_OBJECT,
 LINE AS DEPENDANT_LINE,
 TEXT AS DEPENDANT_TEXT
 FROM ALL_SOURCE
 WHERE TEXT NOT LIKE '--%'
 ) DETAILS
 ON INSTR(DETAILS.DEPENDANT_TEXT, TEMP_OBJECTS.OBJECT_NAME) != 0;

COMMIT;

--THIS MAKES SENSE OF THE DEPENDENCIES. IT COMBINES INSTANCES ON MULTIPULE LINES INTO ONE CSV RESULT 
SELECT OBJECT_OWNER,
 OBJECT_TYPE,
 OBJECT_NAME,
 DEPENDANT_OWNER,
 DEPENDANT_OBJECT_TYPE,
 DEPENDANT_OBJECT,
 SUBSTR(DEPENDANT_LINE, 2, MAX_LEN_DEPENDANT_LINE - 1) AS DEPENDANT_LINE
FROM (
 SELECT OBJECT_OWNER,
 OBJECT_TYPE,
 OBJECT_NAME,
 DEPENDANT_OWNER,
 DEPENDANT_OBJECT_TYPE,
 DEPENDANT_OBJECT,
 LENGTH(SYS_CONNECT_BY_PATH(DEPENDANT_LINE, ',')) AS LEN_DEPENDANT_LINE,
 MAX(LENGTH(SYS_CONNECT_BY_PATH(DEPENDANT_LINE, ','))) OVER (
 PARTITION BY OBJECT_OWNER,
 OBJECT_TYPE,
 OBJECT_NAME,
 DEPENDANT_OWNER,
 DEPENDANT_OBJECT_TYPE,
 DEPENDANT_OBJECT
 ) AS MAX_LEN_DEPENDANT_LINE,
 SYS_CONNECT_BY_PATH(DEPENDANT_LINE, ',') AS DEPENDANT_LINE
 FROM (
 SELECT OBJECT_OWNER,
 OBJECT_TYPE,
 OBJECT_NAME,
 DEPENDANT_OWNER,
 DEPENDANT_OBJECT_TYPE,
 DEPENDANT_OBJECT,
 DEPENDANT_LINE,
 ROW_NUMBER() OVER (
 PARTITION BY OBJECT_OWNER,
 OBJECT_TYPE,
 OBJECT_NAME,
 DEPENDANT_OWNER,
 DEPENDANT_OBJECT_TYPE,
 DEPENDANT_OBJECT ORDER BY OBJECT_OWNER,
 OBJECT_TYPE,
 OBJECT_NAME,
 DEPENDANT_OWNER,
 DEPENDANT_OBJECT_TYPE,
 DEPENDANT_OBJECT,
 DEPENDANT_LINE
 ) AS DEPENDANT_OBJECT_LEVEL
 FROM TEMP_DEPENDENCIES
 ) SUBQ START WITH DEPENDANT_OBJECT_LEVEL = 1
 CONNECT BY PRIOR(OBJECT_OWNER || '.' || OBJECT_TYPE || '.' || OBJECT_NAME || '.' || DEPENDANT_OWNER || '.' || DEPENDANT_OBJECT_TYPE || '.' || DEPENDANT_OBJECT || '.' || TO_CHAR(DEPENDANT_OBJECT_LEVEL + 1))
 = (OBJECT_OWNER || '.' || OBJECT_TYPE || '.' || OBJECT_NAME || '.' || DEPENDANT_OWNER || '.' || DEPENDANT_OBJECT_TYPE || '.' || DEPENDANT_OBJECT || '.' || TO_CHAR(DEPENDANT_OBJECT_LEVEL))
 ) CONNECTED_DATA
WHERE LEN_DEPENDANT_LINE = MAX_LEN_DEPENDANT_LINE
ORDER BY OBJECT_OWNER,
 OBJECT_NAME,
 OBJECT_TYPE,
 DEPENDANT_OWNER,
 DEPENDANT_OBJECT_TYPE,
 DEPENDANT_OBJECT

PL SQL Find all objects in other objects

Standard

I wrote some code that allows you to find exactly where all references in scripts (sprocs, views) your objects are. This includes tables, views, indexes, sequences. Run it, and let me know what you think!

WITH OBJECT_OWNERS
AS (
 SELECT 'SYS' AS OBJECT_OWNER
 FROM DUAL
 )
SELECT OBJECT_OWNER,
 OBJECT_TYPE,
 OBJECT_NAME,
 REFERENCED_IN_OWNER,
 REFERENCED_IN_OBJECT_TYPE,
 REFERENCED_IN_OBJECT,
 REFERENCED_IN_LINE,
 REFERENCED_IN_TEXT
FROM (
 SELECT OBJECT_TYPE,
 OBJECT_NAME,
 OWNER AS OBJECT_OWNER
 FROM ALL_OBJECTS
 WHERE OWNER IN (
 SELECT OBJECT_OWNER
 FROM OBJECT_OWNERS
 ) MINUS
 SELECT 'TABLE' AS OBJECT_TYPE,
 OBJECT_NAME,
 OWNER AS OBJECT_OWNER
 FROM ALL_OBJECTS
 WHERE OWNER IN (
 SELECT OBJECT_OWNER
 FROM OBJECT_OWNERS
 )
 AND OBJECT_TYPE = 'MATERIALIZED VIEW'
 ) QUALIFIER
LEFT JOIN (
 SELECT OWNER AS REFERENCED_IN_OWNER,
 TYPE AS REFERENCED_IN_OBJECT_TYPE,
 NAME AS REFERENCED_IN_OBJECT,
 LINE AS REFERENCED_IN_LINE,
 TEXT AS REFERENCED_IN_TEXT
 FROM ALL_SOURCE
 WHERE TEXT NOT LIKE '--%'
 ) DETAILS
 ON INSTR(DETAILS.REFERENCED_IN_TEXT, QUALIFIER.OBJECT_NAME) != 0
ORDER BY OBJECT_OWNER,
 OBJECT_TYPE,
 OBJECT_NAME,
 REFERENCED_IN_OWNER,
 REFERENCED_IN_OBJECT_TYPE,
 REFERENCED_IN_OBJECT,
 REFERENCED_IN_LINE