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

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

How to BI: Part 1

Standard

Once you go BI, you never go back.

I wrote a couple papers on how to do reporting at the enterprise level, and the issue I kept coming back to was the idea that BI or Business Intelligence should never be done in a silo. Anyone that does reporting should have an idea of how the whole set of data works before reporting on it. What does this entail? Let me show you.

Business Intelligence is the art of giving data a voice that is influential to the business’s operations. This influence could be to better customer satisfaction scores, decrease time on the phones, or provide insight into relevant add-ons that customers may be interested. There’s one thing in common: BI is used to help it make more money through higher sales or better public image.

This purpose to make sense of data and give it a voice can be expertly done by a few passionate people. I have found that the best BI people are the ones who are personally driven by it. Yes, you could do BI as just a job, but once it clicks for you, you’ll know that you’ve reached a new level of curiosity, a new level of analysis. The analysis is important to think about, but it’s equally important to keep in mind how the source of the data comes to be. When you understand how the data comes to be, then you can provide highly accurate, very informative and insightful reporting.

Let’s get an understanding on the different parts of data in BI: source, storage, and reporting.

Source:

The source of data is purely how the data is created. This can be through automated systems or manual work done my people. The more data you know, the better. Source data is usually the best in its most original form. This means there’s less potential for integrity issues.

Storage:

The storage of the data is the most important part of BI. The data needs to be stored accurately. If this is not the case, the data is no good. Bad data is worse than no data, as it gives the impression that you have data to work on, and can lead to bad business decisions. Storage of data can be done in dozens of different formats ranging from a flat file in Excel to a multi-server array. They can all provide valuable data.

Reporting:

The reporting of the data is the sole reason that you’ve spent this much effort. Make sure you take the time to accurately and concisely return data. Reporting is an art by itself, so don’t be discouraged when you do it wrong. We all have, and most of us still do so. There’s no right way to display data, as how the data should be displayed is dependent on the data, the user, and the current business questions being asked of the data.

Now that we have an idea about the main parts of BI, let’s go over the mentality. At every step, it’s your job as a BI developer to return accurate data. At no point in your career will this ever not be acceptable. Your next job is to try and answer the data-driven questions of your users. If it cannot answer their questions, then maybe you need to reevaluate what data you need to collect and report on. The last thing to worry about is reporting interface. This means the speed, the colors, and the bells and whistles. These are nothing without a solid foundation, but they will greatly increase user acceptance.