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

Why IT people continually learn

Standard

I realized something this week while at a training: IT people like to learn, they like to be challenged, regardless of how senior they are. Here’s a quote from the Movie Good Will Hunting:

Will: Beethoven, okay. He looked at a piano, and it just made sense to him. He could just play.
Skylar: So what are you saying? You play the piano?
Will: No, not a lick. I mean, I look at a piano, I see a bunch of keys, three pedals, and a box of wood. But Beethoven, Mozart, they saw it, they could just play. I couldn’t paint you a picture, I probably can’t hit the ball out of Fenway, and I can’t play the piano.
Skylar: But you can do my o-chem paper in under an hour.
Will: Right. Well, I mean when it came to stuff like that… I could always just play.

IT people can become very smart about complex subjects very quickly. This is a curse and a gift, as they can get bored quickly. This boredom is what is needed to be addressed. I have mentioned multiple times to my employers that if I become bored, I have outlets where I can learn and tinker. This has kept my sanity more times than I can count, because in the end, the work needs to happen no matter how boring it is.

IT people love a challenge, they love to love to prove themselves. Make sure you, and your IT peers are mentally challenged. You’ll find that mental challenge will pay for itself in increased productivity.

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.