Problem while creating NUSI

Database
Fan

Problem while creating NUSI

I am using teradat V2R5. I Wanted to create NUSI using " CREATE INDEX NUSI_2(Student_ID) on jehan.Registration "

i got an error that "no more room in DBC", while my database name is jehan, it have too much space, any one can tell me what to do for creating index tables etc in my database,

following is query through which i want to invistigate, kindly read following query, its result is also following.

SELECT
databasename ,
SUM ((((MaxPerm) / 1024) /1024) ) AS MaxSpace ,
SUM ((((CurrentPerm) / 1024) /1024)) AS CurrentUsed_MB,
((100-Percent_Used)*MaxSpace)/100 AS FreeSpace,
(Current_Space_Used_MB / NULLIFZERO (Maximum_Space_MB) * 100 ) (FORMAT 'zz9.99%' , TITLE 'Percent // Used') AS %Used,
(FreeSpace / NULLIFZERO (MaxSpace))
FROM DBC.DISKSPACE
Group By 1
ORDER BY 5 DESC;

Databse | MaxSpace | CurrentUsed | FreeSpace |%used | Free/Max
------------------------------------------------------------------
DBC | 19 | 69 | -51 | 373 | -3
jehan | 350 | 111 | 239 | 32 | 1
6 REPLIES
Enthusiast

Re: Problem while creating NUSI

If you do an "explain" for your "create index" statement, you may notice that it wants to insert rows into DBC.Indexes and DBC.ContraintNames. Since there is no more available space in the DBC database, these inserts will fail.
Fan

Re: Problem while creating NUSI

How to make room(i mean some space in DBC), i am using TeraData V2R5 Demo Version, I have only one database "jehan", having only two Large tables, you can see database size from above query result.
thanks
Fan

Re: Problem while creating NUSI

When i created "jehan" database, then i created about 500 tables, but now i have droped all of them and i have only 2 tables, my database is more than 50% free, please show me way to solve problem of DBC space.
Teradata Employee

Re: Problem while creating NUSI

Hello,

Try running following query and check the results then:

For output in GBs:

SELECT
databasename ,
SUM ((((MaxPerm) / 1024) /1024) / 1024) AS Maximum_Space_GB ,
SUM ((((CurrentPerm) / 1024) /1024) / 1024) AS Current_Space_Used_GB,
((100-Percent_Used)*Maximum_Space_GB)/100 AS Free_Space_GB,
(Current_Space_Used_GB / NULLIFZERO (Maximum_Space_GB) * 100 ) (FORMAT 'zz9.99%' , TITLE 'Percent // Used') AS Percent_Used,
(Free_Space_GB / NULLIFZERO (Maximum_Space_GB))
FROM DBC.DISKSPACE
Group By 1
ORDER BY 5 DESC;

For output in MBs:

SELECT
databasename ,
SUM (((MaxPerm) / 1024) ) AS Maximum_Space_MB ,
SUM (((CurrentPerm) / 1024) ) AS Current_Space_Used_MB,
((100-Percent_Used)*Maximum_Space_MB)/100 AS Free_Space_MB,
(Current_Space_Used_MB / NULLIFZERO (Maximum_Space_MB) * 100 ) (FORMAT 'zz9.99%' , TITLE 'Percent // Used') AS Percent_Used,
(Free_Space_MB / NULLIFZERO (Maximum_Space_MB))
FROM DBC.DISKSPACE
GROUP BY 1
ORDER BY 5 DESC;

And also check if you have enabled query logging which is eating up your DBC space.

Lastly, it is good to post error-free SQL, and the reason of creating such forum is not to delete previous posts, but to let them stay there so anyone may find useful information from it or you may refer to them even after issue is solved or in similar problems.

HTH.

Regards,

Adeel
Teradata Employee

Re: Problem while creating NUSI

And one more suggestion, if you can use Teradata 12 Express, it supports more space than Teradata V2R5 Demo.

You can download Teradata 12 Express from http://www.teradata.com/express.

Regards,

Adeel
Fan

Re: Problem while creating NUSI

Dear Adil, i run the scrip you provided, its result is following,

another thing i cant change version, i need to do my work within this version.

I checked, Query loging not enabled

Database = DBC

Maximum_Space_MB = 19087

Current_Space_Used_MB = 131996

Free_Space_Used = -112909

Percent_Used_MB = 692

(Free_Space_MB / NULLIFZERO (Maximum_Space_MB)) = -6

-------------------------------------------------------------------

my own databse

Database = jehan

Maximum_Space_MB = 358400

Current_Space_Used_MB=112961

Free_Space_Used = 245440

Percent_Used_MB = 32

(Free_Space_MB / NULLIFZERO (Maximum_Space_MB)) = 1