[ORACLE] Use Flashback Query to Retrieve the Original Source Code of a Dropped/Altered Object

You might end up altering or dropping a PROCEDURE, PACKAGE, FUNCTION, without taking a backup first and you need to roll back! I was recently in that position, and I had to do a little search to figure out how to do it. I found a post  on uhesse.com but I had to update it a bit to fit my needs. Basically you can use Flashback Query to get the earlier version.

So all the source codes are in DBA_SOURCE view under SYS schema, so you have to make sure that you connect with a user that has SYSDBA privilege. If you do a SELECT TEXT on this view, you can get the current source code:

SELECT text FROM dba_source WHERE name='PKG_NAME' ORDER BY type, line;

But you need the source code from a couple of minutes ago, so you need to run the following statement.

SELECT text FROM dba_source
     AS OF TIMESTAMP systimestamp - interval '5' minute     --UPDATE THIS TIME TO GET THE FLASHBACK QUERY
     WHERE name='PKG_NAME'
     ORDER BY type, line;
TEXT
--------------------------------------------------------------------------------
PACKAGE PKG_NAME
     AUTHID CURRENT_USER
AS

     PROCEDURE setInsGasDl_Ind;
     PROCEDURE setPremise_Ser_Addr;
     PROCEDURE DLM_Rmv_Dup_DeviceNumber;
     PROCEDURE Publish_To_SRM;
END PKG_NAME;
PACKAGE BODY PKG_NAME
AS

TEXT
--------------------------------------------------------------------------------

     PROCEDURE setInsGasDl_Obsolete_Ind
     IS
     BEGIN
          COMMUDF.print_msg (
               'Set INSTALLED_LOCATION OBSOLETE_INDICATOR');

          UPDATE installed_location
               SET OBSOLETE_INDICATOR = 'Y', RECORD_UPDATE_DATE = SYSTIMESTAMP
          WHERE OBSOLETE_INDICATOR IS NULL
               AND (INSTALLATION_NUMBER, LOCATION_NUMBER) IN
     ....
     ....

The problem is you can’t really run it directly as there is no CREATE OR REPLACE before the name of the package. Also, you have the column header in the middle of the code, which makes it totally useless! So, let’s use Oracle’s DECODE to take care of the first issue and then set some environment variables to take care of the second issue.

set feedback off
set heading off
set termout off
set linesize 1000
set trimspool on
set verify off

select decode( type||'-'||to_char(line,'fm99999'),
               'PACKAGE BODY-1',
               '/'||chr(10),
               null)
          || decode(line,1,'CREATE OR REPLACE ', '' )
          || text text
     from dba_source
     as of timestamp systimestamp - interval '5' minute --UPDATE THIS TIME TO GET THE FLASHBACK QUERY
     where name = upper('PKG_NAME')
     order by type, line;

 

CREATE OR REPLACE PACKAGE PKG_NAME
     AUTHID CURRENT_USER
AS
     PROCEDURE setInsGasDl_Ind;
     PROCEDURE setPremise_SerAddr;
     PROCEDURE DLM_Rmv_Dup_DeviceNumber;
     PROCEDURE Publish_To_SRM;
END PKG_NAME;
/
CREATE OR REPLACE PACKAGE BODY PKG_NAME

AS
     PROCEDURE setInsGasDl_Obsolete_Ind
     IS
     BEGIN
          COMMUDF.print_msg (
               'Set INSTALLED_LOCATION OBSOLETE_INDICATOR');

          UPDATE installed_location
               SET OBSOLETE_INDICATOR = 'Y', RECORD_UPDATE_DATE = SYSTIMESTAMP
          WHERE OBSOLETE_INDICATOR IS NULL
               AND (INSTALLATION_NUMBER, DEVICE_NUMBER) IN
     ....
     ....

So here you have it. Try it and let me know if it works for you too.

Cheers,

 

Forcing a Database out of Single-User Mode Using Brute Force

This is what I found out lately: “Sometimes, brute force is the best way to get something done.

Let’s say you have a production server and you were in the middle of a detach process, when the detach failed after the database went into single-user mode. In my situation somehow I ended up with a parallel deadlock (where process “X” is blocked by “Y” which in turn is blocked by “X”).

So I tried to use ALTER DATABASE TestDB SET MULTI_USER to bring it back into multi user mode, but I was getting the following error:

Error 1205: Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

So my first though was to set DEADLOCK_PRIORITY to HIGH and try it. The logic is that since deadlock priority can be set by the user I wanted to tell the SQL Server to allow my process to continue and drop the other process. So I tried:

USE master;
GO

SET DEADLOCK_PRIORITY HIGH;
GO

ALTER DATABASE TestDB SET MULTI_USER WITH NO_WAIT;

This failed too and I figured it made sense since you would always get a deadlock between the ALTER DATABASE session and the application’s session already exclusively using the database. So basically, the ALTER DATABASE session was chosen as deadlock victim since generally the transaction that requires the least amount of overhead to rollback is the transaction that is aborted!

So now What?!? That was when I found this thread online that talks about the brute force method when all your niceness is not getting you anywhere!

The idea is pretty simple, and not so much elegant; but hey, you can’t complain when it works! You are basically running the line below a 1000 times and you are hoping that during one of these tries SQL server realizes that you are not giving up and it might be cheaper on its resources to set your session as the exclusive session.

USE TestDB;
GO 1000

It took it about 300 tries before SQL Server gave up and gave me the exclusive session, from which I ran the ALTER DATABASE script to set the DB back to multi-user mode.

Deleted the SQL Server Resource from SQL Cluster? Recover it….

There is a chance that any of the resources in the SQL Cluster is removed from the cluster by mistake. This will cause the cluster to go into a Partial Online or Failure status. In order to fix the issue you need to recreate the resources in the cluster.

Following shows what to do when the SQL Server resource “SQL Server (SQLINSTANCE01)” and SQL Server Agent resource “SQL Server Agent (SQLINSTANCE01)” have been deleted from your SQL Cluster.

  1. First, using Domain Controller take a look at your OU to make sure that the cluster object belonging to your SQL Cluster is still there (ESPDBUAT01). A quick look shows that our cluster object (ESPDBUAT01) is still in AD, so no recovery is needed there.
    01-InitialDCCheck
  2. As you can see, your SQL Instance is down.
    02-SQLDown
  3. So you have to clean the role before you can anything, which means deleting the server associated with this role. So right click on Name: ESPDBUAT01 under Server Name and click Remove.
    03
  4. Click Yes.
    04
  5. Now you need to remove all disks from the cluster, so go to Storage, click on Disks, and highlight all the disks that are assigned to ESPDBUAT01 role, then click Remove from ESPDBUAT01.
    05-RemoveDisks
  6. Click Yes.
    06
    07-DiskAvailable
  7. Make sure there is no other resources assigned to this cluster.
    08-CleanNode
  8. Now you need to rename the old role to something else. Right click on the role’s name, and click Properties.
    09-RenameNode
  9. Change the name of the role (i.e. add -Old to the end of it), and click OK.
    10-RenameNode2
  10. Now if you check the Domain Controller you’ll see that the cluster object is disabled. Now you can begin recreating the entire thing from scratch.
    11-NodeDownInDC
  11. So now we are ready to add the deleted resource. In order to do so, right click on Roles in Failover Cluster Manager, and then select Configure Role…
    12-ConfigureROle
  12. Select the role Other Server, and then click Next.
    13-OtherServer
  13. In Client Access Point page, add the same name and IP Address that had used to setup the cluster object. Once done click Next.
    14-ClientAccessPoint
  14. Select all the disks belonging to this cluster, and then click Next.
    15-AddDisks
  15. In the Select Resource Types page, select the SQL Server and SQL Server Agent resource types, and then click Next.
    16-AddSQLResource
  16. Click Next.
    17-Confirm
  17. Click Finish, but we are not done yet!
    18-Finish
  18. As you can see in the following screenshot, all disks are added and are online. However, the server and the two SQL services are offline. They need to be configured their dependencies and properties before bringing them online.
    19-NodePartialUp
  19. Right click on New SQL Server and go to Properties.
    20-NewSQLServer
  20. Go to Dependencies tab and add the network name (ESPDBUAT01), the IP address, and all the Disks that our cluster uses.
    21-Dependencies
  21. Go to Properties tab and set VirtualServerName and InstanceName to their correct values.
    22-Properties
  22. Go to General tab and change the Name to SQL Server (SQLINSTANCE01), and then click OK.
    23-General
  23. Now we need to do a similar process for the SQL Server Agent. So right click on New SQL Server Agent, and go to Properties.
    24-NewSQLServerAgent
  24. Go to Properties tab and set VirtualServerName and InstanceName to their correct values. Note: The order of the rows in this tab (VirtualServername and InstanceName) might be different than the screenshot bellow. Make sure that you don’t type them in reverse. Also, any typo in this section causes the entire thing not to come online. So, double check your spelling!
    25-Properties
  25. Click on Dependencies tab and add SQL Server (SQLINSTANCE01).
    26-Dependecies
  26. Go to General tab and update the Name of the agent to SQL Server Agent (SQLINSTANCE01). Then click OK.
    27-General
  27. Now you are ready to start the role. Right click on the role (ESPDBUAT01), and click on Start Role. If you have typed everything correctly the role will start without any issues.
    28-StartRole
  28. Ensure that the two services are Online and you can connect to the SQL cluster in SSMS.
    29-RoleOnline
    30-SQLUp
  29. Check the Domain Controller and the cluster object ESPDBUAT01 is back online.
    31-DCUp
  30. Now you can clean up the Failover Cluster Manager. Right click on the old role ESPDBUAT01-Old and click Remove, and then Yes.
    32-RemoveOldRole
    33-Confirm

And you are done! 🙂

Shrinking Oracle TEMP tablespace

There might be times that you need to shrink the TEMP tablespace because you are running out of disk space. Now you can check and query to see what’s the name of the tablespace(s) and their names so you can shrink them. Run the following to see the list of all the temp tablespces:

SELECT tablespace_name, file_name, bytes
FROM dba_temp_files
WHERE tablespace_name like 'TEMP%';

And you’ll get something like this:

oracle_drop_temp_0

The first question you have to ask is why the temporary tablespace is so large. It may be due to a large query that you just ran with a sort that was a mistake or it may be due to some other exceptional circumstance. If that is the case then all you need to do to clean up is to shrink the temporary tablespace. So first try running the following line:

ALTER DATABASE TEMPFILE '/FULL_PATH_HERE/temp01.dbf' RESIZE 256M;

If it works then you are done, but chances are that you will get the following error:

ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

This error may have occurred since Oracle will only shrink the file if the temporary tablespace is at the head of the file and if it is smaller than the size you specify. If the temporary tablespace you want to shrink is your default temporary tablespace, you will have to first create a new temporary tablespace, set it as the default temporary tablespace then drop your old default temporary tablespace and recreate it. Afterwords drop the second temporary table created. So let’s do this:

Create the new tablespace:

CREATE TEMPORARY TABLESPACE TEMP2
TEMPFILE '/FULL_PATH_HERE/temp02.dbs' SIZE 5M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

Set the new tablespace as the default so you can drop the original one:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;

Database altered.

Now, try dropping the first tablespace. Yes I said “Try” because it might hang and I’ll tell you why later:

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

If the above statement hangs for more than a couple of seconds, then there might be an OS process that has locked the TEMP tablespace and isn’t allowing Oracle to drop it. So, let’s find out if that’s true and kill the process if possible:

SELECT username ,session_num ,session_addr ,tablespace
FROM v$sort_usage
WHERE tablespace='TEMP'; --put the tablespace's name here

oracle_drop_temp_1

So now that we know there is process locking the tablespace, run the following line to generate the kill statement to kill the annoying session. You need to get the saddr from the last step:

SELECT 'ALTER SYSTEM KILL SESSION ''' || sid ||','|| serial# || ''';'
FROM v$session
WHERE saddr='C00000009E6F6118'; --This is the session_addr from last step

oracle_drop_temp_2

Copy the above line and run it and you should be able to ALTER SYSTEM which kills the process, now we can continue dropping the tablespace. Try again:

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

Now, you can recreate the original tablespace:

CREATE TEMPORARY TABLESPACE TEMP
TEMPFILE '/FULL_PATH_HERE/temp01.dbs' SIZE 5M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Mark it as default, and then drop the second tablespace:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

Database altered.

DROP TABLESPACE TEMP2 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

It should have worked this time… Now you have recreated your TEMP tablespace and saved some diskspace.

Avoiding MSDN’s Download Manager… Because it’s horrible!

Have you ever tried downloading anything from Microsoft using Microsoft’s Download Manager? If you have, then you know what I’m talking about when I say that IMHO that software is absolutely a piece of garbage. And whoever decided to make it that way needs to be fired from Microsoft (mabe not fired, but definitly demoted for the aweful job he/she has done on this thing). I mean it’s called Download Manager, but the only thing it can’t do is download one piece of software without some sort of error!!! Actually Richard Banks has a funny article about his encounters with this Download Manager (granted his article is from 2007, but I don’t think Microsoft has changed their software much)!

But since you are here, probably you want to know how to avoid using MSDN’s Download Manager. Well, there is a great extension for FireFox, which is called DownThemAll!, which can be your best friend here. Just add it to your FireFox and you’ll know what I mean.

dl-down0

After adding it to your FireFox browser, navigate to where you want to download the file from and choose “Web Browser” instead of “Download Manager” from the Download Method drop down box, and click continue.

dl-webbrowser

When FireFox’s download window opens up, choose DownThemAll, and hit OK

dl-down5dl-down3

Choose where you want the file to be saved and hit Start!, and Voila!!! You even get full speed download rather than 1MB/s limit from Download Manager!!

dl_max1

Also, if the download gets stuck, just right-click and pause the download, then right click and click resume. This will resume the download and it wont fail :D. Yay!!!!!

dl-pause1

dl-resume1I

I hope this saves somebody some time 🙂

Cracking Excel’s Password

Wow! It’s been so long since I’ve written anything here. 1 year and 4 days to be exact! You know, I wanted this blog to be link my code repository as well… but as the tasks become more complex, writing about them and explaining how they have been solved are getting harder and harder.

But this one I had to save here, since it took me couple of hours to find. I have to emphasize that I didn’t write any of the code myself so here is the original post... You know, credit where it’s due!

So, Here is the problem I had this morning: An old MS Excel spreadsheet that with a protected workbook and I had forgotten the password. I’m sure you have been in that situation before. So I started searching for ways to crack it and I found a bunch of applications (Free!!!) that does it for you. But since I don’t want to just install any application I find off the web on my personal PC, I kept looking and found the following VB script that works like a charm. Here is how to use it:

Open the workbook that has the protected sheet in it. Hit Alt+F11 to view the Visual Basic Editor. Hit Insert-Module and paste this code into the right-hand code window:

Sub PasswordBreaker()

'Author unknown but submitted by brettdj of www.experts-exchange.com

Dim i As Integer, j As Integer, k As Integer
Dim l As Integer, m As Integer, n As Integer
Dim i1 As Integer, i2 As Integer, i3 As Integer
Dim i4 As Integer, i5 As Integer, i6 As Integer
On Error Resume Next
For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126

ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _
Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
If ActiveSheet.ProtectContents = False Then
MsgBox "One usable password is " & Chr(i) & Chr(j) & _
Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
ActiveWorkbook.Sheets(1).Select
Range("a1").FormulaR1C1 = Chr(i) & Chr(j) & _
Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
Exit Sub
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
End Sub

Close the VB Editor window. Navigate to the worksheet you want to unprotect. Hit Tools-Macro-Macros and double-click PasswordBreaker in the list

Quote of the Day

“It is not the critic who counts, not the one who points out how the strong man stumbled or how the doer of deeds might have done them better. The credit belongs to the man who is actually in the arena, whose face is marred with sweat and dust and blood; who strives valiantly; who errs and comes short again and again; who knows the great enthusiasms, the great devotions, and spends himself in a worthy cause; who, if he wins, knows the triumph of high achievement; and who, if he fails, at least fails while daring greatly, so that his place shall never be with those cold and timid souls who know neither victory nor defeat.”

Franklin D. Roosevelt

MSSQL – sp_who3, A better sp_who2

As a DBA, sp_who2 is a procedure that I use on a daily basis. It’s a good procedure, but you always have to copy the entire result into excel sheet and then sort through it to find the SPID of the session that you were looking for in the first place. And sometimes, you just don’t have the time to do that. So there are two things you can do to solve this issue. First, you can get the result of the sp_who2 into a @table and then look through there (which is way easier than using excel), or you can create your own procedure that accepts a parameter as the DBName and filters the outcome based on that. Let me show you both and you can choose whichever works for you best.

1. Sort through the result of sp_who2 via @table

So, if all you want to do is find out what processes are running against a specific database, you can copy and paste the following query and execute it. Make sure to edit line 35 and replace <DATABASE NAME GOES HERE> with the name of the database you have.


DECLARE @whotbl TABLE
    (
      SPID        INT    NULL
     ,Status    VARCHAR(50)    NULL
     ,Login        SYSNAME    NULL
     ,HostName    SYSNAME    NULL
     ,BlkBy        VARCHAR(5)    NULL
     ,DBName    SYSNAME    NULL
     ,Command    VARCHAR(1000)    NULL
     ,CPUTime    INT    NULL
     ,DiskIO    INT    NULL
     ,LastBatch VARCHAR(50)    NULL
     ,ProgramName VARCHAR(200)    NULL
     ,SPID2        INT    NULL
     ,RequestID INT    NULL
     )

     INSERT INTO @whotbl
     EXEC sp_who2

    SELECT W.*
          ,CommandText = sql.text
          ,ExecutionPlan   = pln.query_plan
          ,ObjectName  = so.name
          ,der.percent_complete
          ,der.estimated_completion_time
          --,CommandType =der.command
      FROM @whotbl  W
 LEFT JOIN sys.dm_exec_requests der
        ON der.session_id = w.SPID
       OUTER APPLY SYS.dm_exec_sql_text (der.sql_handle) Sql
       OUTER APPLY sys.dm_exec_query_plan (der.plan_handle) pln
 LEFT JOIN sys.objects so
        ON so.object_id = sql.objectid
      WHERE DBName = '<DATABASE NAME GOES HERE>'

2. Create a better sp_who2 procedure

If you will be using this more than once, it’s better to create a procedure that you can pass the name of the database to it and it could return the result to you. If that’s the case, copy and paste the following and execute it to create the procedure:


IF OBJECT_ID('sp_who3','P') IS NOT NULL DROP PROC sp_who3

GO
CREATE PROC sp_who3 @DBNAME nvarchar(100) = NULL
AS

    DECLARE @whotbl TABLE
    (
      SPID        INT    NULL
     ,Status    VARCHAR(50)    NULL
     ,Login        SYSNAME    NULL
     ,HostName    SYSNAME    NULL
     ,BlkBy        VARCHAR(5)    NULL
     ,DBName    SYSNAME    NULL
     ,Command    VARCHAR(1000)    NULL
     ,CPUTime    INT    NULL
     ,DiskIO    INT    NULL
     ,LastBatch VARCHAR(50)    NULL
     ,ProgramName VARCHAR(200)    NULL
     ,SPID2        INT    NULL
     ,RequestID INT    NULL
     )

     INSERT INTO @whotbl
     EXEC sp_who2

    SELECT W.*
          ,CommandText = sql.text
          ,ExecutionPlan   = pln.query_plan
          ,ObjectName  = so.name
          ,der.percent_complete
          ,der.estimated_completion_time
          --,CommandType =der.command
      FROM @whotbl  W
 LEFT JOIN sys.dm_exec_requests der
        ON der.session_id = w.SPID
       OUTER APPLY SYS.dm_exec_sql_text (der.sql_handle) Sql
       OUTER APPLY sys.dm_exec_query_plan (der.plan_handle) pln
 LEFT JOIN sys.objects so
        ON so.object_id = sql.objectid
      WHERE DBName = ISNULL(@DBNAME,DBName)

go

You can use the procedure similar to sp_who2 (without argument) or with a @DBNAME = ‘ ‘ argument, like bellow:

sp_who3 @DBNAME = '<DATABASE NAME GOES HERE>'

Obviously, the credit for the code goes to someone else. I just thought of improving it a bit. Hope it helps you.

ORACLE – Kill, stop and resume datapump expdp and impdp jobs

Since datapump export and important utilities are both command-line driven, it is easy to forget that they are runnig in the background. So sometimes you need to kill, cancel or restart the expdp or impdp job. Keep reading to see what you need to do to achieve this:

1) Identify the datapump jobs (or check the status of your job)

You can do 2 things:

a) If you assigned a name to job_name parameter of your expdp or impdp command, then you can use the name

b) You can do a select from dba_datapump_jobs to get a list of all the jobs that are currnetly running (sqlplus)

SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs;

OWNER_NAME     JOB_NAME              OPERATION   JOB_MODE    STATE
----------     --------------------  ----------  ----------  ------------
SYS            TS2_expdp_July4       EXPORT      FULL        EXECUTING

2) Killing or stopping a running datapump job

a) To kill a job (no chance of resuming it later on)
press Ctrl-c to show the datapump prompt and then type KILL_JOB

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
[Ctrl-c]
Export> KILL_JOB

b) To Stop a job (you can resume it later)
press Ctrl-c to show the datapump prompt and then type STOP_JOB[=IMMEDIATE]

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
[Ctrl-c]
Export> STOP_JOB=IMMEDIATE
Are you sure you wish to stop this job ([yes]/no): yes

NOTE: Sometimes the job is killed but it’s not removed from the dba_datapump_jobs table. In that case you need to drop the table:

DROP TABLE SYS.TS2_expdp_July4;
Table dropped.

3) Resuming a stopped job

If you know the job_name parameter then use it, if you don’t do a select from dba_datapump_jobs

SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs;

OWNER_NAME  JOB_NAME              OPERATION   JOB_MODE    STATE
----------  --------------------  ----------  ----------  ------------
SYS         TS2_expdp_July4       EXPORT      FULL NOT    RUNNING

Now we can ATTACH to the job using it as a parameter to the expdp or impdp command, and a lot of gibberish is shown:

$ expdp system ATTACH=TS2_expdp_July4

SQL Server – How to query all databases sizes?

Sometimes you need to get a quick glance of the size of all the databases in your instance. Following script would give you just that. It gives you the size of the data file as well as the log file.


SELECT
 DB_NAME(db.database_id) DatabaseName,
 (CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB,
 (CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB,
 (CAST(mfstream.StreamSize AS FLOAT)*8)/1024 StreamSizeMB,
 (CAST(mftext.TextIndexSize AS FLOAT)*8)/1024 TextIndexSizeMB
 FROM sys.databases db
 LEFT JOIN (SELECT database_id, SUM(size) RowSize FROM sys.master_files WHERE type = 0 GROUP BY database_id, type) mfrows ON mfrows.database_id = db.database_id
 LEFT JOIN (SELECT database_id, SUM(size) LogSize FROM sys.master_files WHERE type = 1 GROUP BY database_id, type) mflog ON mflog.database_id = db.database_id
 LEFT JOIN (SELECT database_id, SUM(size) StreamSize FROM sys.master_files WHERE type = 2 GROUP BY database_id, type) mfstream ON mfstream.database_id = db.database_id
 LEFT JOIN (SELECT database_id, SUM(size) TextIndexSize FROM sys.master_files WHERE type = 4 GROUP BY database_id, type) mftext ON mftext.database_id = db.database_id

And you should end up with something like the following:

DatabaseName  RowSizeMB LogSizeMB StreamSizeMB TextIndexSizeMB
------------- --------- --------- ------------ ---------------
master        4         1.25      NULL         NULL
model         2.25      0.75      NULL         NULL
msdb          14.75     8.1875    NULL         NULL
tempdb        8         0.5       NULL         NULL