Duplicate PSAPTEMP

[arya_biswas]

Dear Gurus,

I was trying extend PSAPTEMP in our Quality system through Brtools

It was successful.

DB02 showing 1 PSAPTEMP with extended size 'PSAPTEMP 4,194,304 4,194,304'.

But from sql level it is showing two PSAPTEMP.

Tablespace Size (MB) Free (MB) % Free % Used

-

---

-

---

-

---

-

---

-

---

PSAPTEMP 2048 4080 200 100

PSAPTEMP 2048 1880 146 154

PSAPUNDO 15360 14798.0625 96 4

SYSAUX 1000 816.5625 82 18

SYSTEM 2048 1129.20313 55 45

PSAPPRD700 56162 12955.4375 23 77

PSAPPRD 75511 7529 10 90

PSAPPRDUSR 20 .1875 1 99

Tomorrow we have a client import in this system. Is it going to hamper the import? And why another PSAPTEMP showing in OS level? Could anyone through any light on this.

//Arya

[former_member189725 · Accepted solution]

what is the SQL statement you are executing ??

[arya_biswas · Accepted solution]

Dear Ratnajit,

The sql command is :

SELECT /* + RULE */ df.tablespace_name "Tablespace", df.bytes / (1024 * 1024) "Size (MB)", SUM(fs.bytes) / (1024 * 1024) "Free (MB)", Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free", Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used" FROM dba_free_space fs, (SELECT tablespace_name,SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) df WHERE fs.tablespace_name = df.tablespace_name GROUP BY df.tablespace_name,df.bytes UNION ALL SELECT /* + RULE */ df.tablespace_name tspace, fs.bytes / (1024 * 1024), SUM(df.bytes_free) / (1024 * 1024), Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1), Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes) FROM dba_temp_files fs, (SELECT tablespace_name,bytes_free,bytes_used FROM v$temp_space_header GROUP BY tablespace_name,bytes_free,bytes_used) df WHERE fs.tablespace_name = df.tablespace_name GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used ORDER BY 4 DESC;

//Arya

[former_member189725 · Accepted solution]

Please run this simple sql.

select TABLESPACE_NAME from dba_tablespaces;

Should not show you duplicate PSAPTEMP tablespaces .

[Former Member · Accepted solution]

You just seem to have added a second file, PSAPPRD has two files as well. Everything seems to be fine. You cannot group by things like df.bytes_free, when the free space is different among files

Cheers Michael

[Former Member · Accepted solution]

(Obsolete)

Edited by: Mark Foerster on Dec 22, 2011 1:54 PM

[arya_biswas · Accepted solution]

Dear gurus,

Many thank for the commands.

These are showing only one PSAPTEMP.

Still with my old command it is showing two table names.

Please advice me if I run client import today in this system. Will it going to hamper or it will work fine?

//Arya

[Former Member · Accepted solution]

Hi Arya,

Use brtools or DB02 in order to monitor Oracle to talk on the same path, not the sql statements found on the internet.

Best regards,

Orkun Gedik

[arya_biswas · Accepted solution]

Dear Orkun,

Thanks for the reply,

As per your post DB02 value is enough! So i can go forward client refresh task on this system today?

//Arya

[Former Member · Accepted solution]

DB02 refresh the data automatically, everday, as well as you can do it manually. I can't see any problem?

Could you clarify client refresh, please...

Best regards,

Orkun Gedik

[arya_biswas · Accepted solution]

Dear Orkun,

Thanks.

The data in DB02 is up-to date also we had refreshed it after the table space extension. It is fine in DB02.

How can i monitor this via brtools?

Client refresh using client export/import method.

We have exported the PRD client and today we will start the import process in Quality.

//Arya

[Former Member · Accepted solution]

>> How can i monitor this via brtools?

brspace -u / -p init<DBSID>.sap -s 20 -l E -f dbshow -c tsinfo

Best regards,

Orkun Gedik

[arya_biswas · Accepted solution]

Dear Orkun,

Many many thanks for the command. It worked fantastic.

Hope our import will go well today.

//Arya

[ashish_mishra2 · Accepted solution]

Hi,

Check with this command, this will not show PSAPTEMP but all other table spaces with their usage will be displayed.

From SQL prompt:

SET LINESIZE 100

COLUMN TABLESPACE FORMAT A15

select t.tablespace, t.totalspace as " Totalspace(MB)", round((t.totalspace-fs.freespace),2) as "Used Space(MB)", fs.freespace as "Freespace(MB)", round(((t.totalspace-fs.freespace)/t.totalspace)100,2) as "% Used", round((fs.freespace/t.totalspace)100,2) as "% Free" from (select round(sum(d.bytes)/(10241024)) as totalspace, d.tablespace_name tablespace from dba_data_files d group by d.tablespace_name) t, (select round(sum(f.bytes)/(10241024)) as freespace, f.tablespace_name tablespace from dba_free_space f group by f.tablespace_name) fs where t.tablespace=fs.tablespace order by t.tablespace;

Alternatively to get the tablespace usage from brtools, u cn use below method:

from orasid

brspace -f tsextend

select option 1- extend tablespace... check all tablespaces size and then u cn either stop- or extend particular tablespaces

Cheers !!!

Ashish.

[arya_biswas · Accepted solution]

Dear Gurus,

The Import was perfect and no issues with the PSAPTEMP.

I think there may be an issue with my query.

Anyway many many thanks for the help. Points Awarded.

Wish you all Happy Holidays and a fantastic 2012.

//Arya