Transport Tablespace Testing from Msocial (Sparc) to STS VMWare (x86_64)

1. Platform
- Msocial: Sparc Solaris 10
- STS_VMWare: x86_64 Solaris platform
2. Utility: RMAN
3. Task list details
3.1. On Source (MSocial)
3.1.1. Create test user schema & generate data in

sys@MSOCIAL> create user msocialtest identified by msocial
  2  default tablespace users
  3  temporary tablespace temp
  4  quota unlimited on users
  5  /

User created.

sys@MSOCIAL> grant connect, resource, create session, dba to msocialtest;

Grant succeeded.

sys@MSOCIAL> grant read, write on directory data_pump_dir to msocialtest;

Grant succeeded.

sys@MSOCIAL> grant select any dictionary to msocialtest;

Grant succeeded.

sys@MSOCIAL> grant execute on sys.dbms_tts to msocialtest;

Grant succeeded.

sys@MSOCIAL> conn msocialtest/msocial
Connected.
msocialtest@MSOCIAL> CREATE TABLE test_tab (
  2    id          NUMBER,
  3    description VARCHAR2(50),
  CONSTRAINT test_tab_pk PRIMARY KEY (id)
  4    5  );

Table created.

msocialtest@MSOCIAL> INSERT /*+ APPEND */ INTO test_tab (id, description)
  2  SELECT level,
  3         'Description for ' || level
  4  FROM   dual
  5  CONNECT BY level <= 10000;

10000 rows created.

msocialtest@MSOCIAL> commit;

Commit complete.

msocialtest@MSOCIAL> create table bigtab as select * from all_objects where 1=2;

Table created.

msocialtest@MSOCIAL> insert into bigtab select * from all_objects where rownum<=20000;

20000 rows created.

msocialtest@MSOCIAL> commit;

Commit complete.

msocialtest@MSOCIAL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BIGTAB                         TABLE
TEST_TAB                       TABLE

msocialtest@MSOCIAL> select count(*) from bigtab;

  COUNT(*)
----------
     20000

msocialtest@MSOCIAL> select count(*) from test_tab;

  COUNT(*)
----------
     10000

msocialtest@MSOCIAL> create index indx_objid on bigtab(object_id);

Index created.

msocialtest@MSOCIAL> create index indx_objname on bigtab(object_name);

Index created.

msocialtest@MSOCIAL> alter system switch logfile;

System altered.

msocialtest@MSOCIAL>

3.1.2. Verify none XML data in user schema

sys@MSOCIAL> select distinct p.tablespace_name
  2  from dba_tablespaces p, dba_xml_tables x, dba_users u, all_all_tables t
  3  where t.table_name=x.table_name
  4  and t.tablespace_name=p.tablespace_name
  5  and x.owner=u.username
  6  /

TABLESPACE_NAME
------------------------------
SYSAUX

sys@MSOCIAL> select username, default_tablespace
  2  from dba_users
  3  where default_tablespace='SYSAUX';

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
SYSMAN                         SYSAUX
DBSNMP                         SYSAUX
FLOWS_FILES                    SYSAUX
MDSYS                          SYSAUX
ORDDATA                        SYSAUX
ANONYMOUS                      SYSAUX
EXFSYS                         SYSAUX
WMSYS                          SYSAUX
XDB                            SYSAUX
ORDSYS                         SYSAUX
CTXSYS                         SYSAUX
APPQOSSYS                      SYSAUX
APEX_030200                    SYSAUX
OWBSYS_AUDIT                   SYSAUX
ORDPLUGINS                     SYSAUX
OLAPSYS                        SYSAUX
SI_INFORMTN_SCHEMA             SYSAUX
OWBSYS                         SYSAUX

18 rows selected.

sys@MSOCIAL>

Note: Only tablespace SYSAUX contains XML data, so, we need only MSOCIALTEST user schema but not all of above user schema in SYSAUX default tablespace

3.1.3. Check if some limitation do not allow to export

sys@MSOCIAL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('USERS',TRUE);

PL/SQL procedure successfully completed.

sys@MSOCIAL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

sys@MSOCIAL>

3.1.4. Put the tablespace in READ ONLY mode

sys@MSOCIAL> alter tablespace users read only;

Tablespace altered.

sys@MSOCIAL>

3.1.5. Export metadata

$ expdp system/msocial dumpfile=users.dmp directory=data_pump_dir transport_tablespaces=USERS logfile=export_users_tts.log

Export: Release 11.2.0.1.0 - Production on Mon Apr 13 09:18:30 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
sys@MSOCIAL> alter tablespace users read only;

Tablespace altered.

sys@MSOCIAL> Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** dumpfile=users.dmp directory=data_pump_dir transport_tablespaces=USERS logfile=export_users_tts.log 
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
  /msoapp/app/oracle/product/11.2.0/msocial/rdbms/log/users.dmp
******************************************************************************
Datafiles required for transportable tablespace USERS:
  /mSocial-DB-DATA1/oradata/msocial/users01.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 09:19:37

3.1.6. Datafile export by RMAN

RMAN> convert tablespace USERS to platform 'Solaris Operating System (x86-64)' format '/mSocial-DB-DATA3/dpdump/%U';

Starting conversion at source at 13-APR-2015 10:10:14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=229 instance=msocial1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=245 instance=msocial1 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=249 instance=msocial1 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00004 name=/mSocial-DB-DATA1/oradata/msocial/users01.dbf
converted datafile=/mSocial-DB-DATA3/dpdump/data_D-MSOCIAL_I-1761030358_TS-USERS_FNO-4_a3q495k9
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at source at 13-APR-2015 10:10:18

Starting Control File and SPFILE Autobackup at 13-APR-2015 10:10:18
piece handle=/mSocial-DB-FLASH/flash_recovery_area/MSOCIAL/autobackup/2015_04_13/o1_mf_s_876910219_blq5jjxv_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 13-APR-2015 10:10:34

RMAN>

3.1.7. Put the tablespace USERS in mode READ WRITE again

3.2. On Target (STS_VMWare)
3.2.1. Verify the platform

sys@STS> SELECT tp.platform_id,substr(d.PLATFORM_NAME,1,30) PLATFORM_NAME, ENDIAN_FORMAT
  2  FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
  3  WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

PLATFORM_ID PLATFORM_NAME                        ENDIAN_FORMAT
----------- ------------------------------------ --------------
         20 Linux x86 64-bit                     Little

sys@STS>

3.2.2. Create user

sys@STS> create user msocialtest identified by msocial
  2  default tablespace users  
  3  temporary tablespace temp
  4  quota unlimited on users;

User created.

sys@STS> grant connect, resource, create session, dba to msocialtest;

Grant succeeded.

sys@STS> grant select any dictionary to msocialtest;

Grant succeeded.

sys@STS> grant execute on sys.dbms_tts to msocialtest;

Grant succeeded.

sys@STS> grant execute on sys.dbms_metadata to msocialtest;

Grant succeeded.

3.2.3. Mark tablespace USERS read only

sys@STS> alter tablespace users read only;

Tablespace altered.

3.2.4. Copy metadata file and import metadata

sys@STS> ! ls -ll /stsapp/app/oracle/admin/sts/dpdump/
total 13234
-rw-r--r--   1 oracle   oinstall 6561792 Apr 13  2015 data_D-MSOCIAL_I-1761030358_TS-USERS_FNO-4_a3q495k9
-rw-r-----   1 oracle   asmadmin     116 Mar 31 13:32 dp.log
-rw-r--r--   1 oracle   oinstall  192512 Apr 13  2015 users.dmp

$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Apr 13 10:29:32 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: STS (DBID=1636328243)

$ impdp transport_datafiles='/stsapp/app/oracle/admin/sts/dpdump/data_D-MSOCIAL_I-1761030358_TS-USERS_FNO-4_a3q495k9' directory=data_pump_dir dumpfile=users.dmp logfile=impd_tts_users.log

Import: Release 11.2.0.1.0 - Production on Mon Apr 13 10:39:06 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: system
Password: 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** transport_datafiles=/stsapp/app/oracle/admin/sts/dpdump/data_D-MSOCIAL_I-1761030358_TS-USERS_FNO-4_a3q495k9 directory=data_pump_dir dumpfile=users.dmp logfile=impd_tts_users.log 
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29349: tablespace 'USERS' already exists

Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at 10:39:23

Note: Because of tablespace USERS already exists in STS database, so, we rename the tablespace USERS to USERS_STS

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 13 10:45:11 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

sys@STS> alter tablespace users rename to users_sts;

Tablespace altered.

and re-import again

$ impdp transport_datafiles='/stsapp/app/oracle/admin/sts/dpdump/data_D-MSOCIAL_I-1761030358_TS-USERS_FNO-4_a3q495k9' directory=data_pump_dir dumpfile=users.dmp logfile=impd_tts_users.log

Import: Release 11.2.0.1.0 - Production on Mon Apr 13 10:47:15 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: system
Password: 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** transport_datafiles=/stsapp/app/oracle/admin/sts/dpdump/data_D-MSOCIAL_I-1761030358_TS-USERS_FNO-4_a3q495k9 directory=data_pump_dir dumpfile=users.dmp logfile=impd_tts_users.log 
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
ORA-39151: Table "SCOTT"."DEPT" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "SCOTT"."EMP" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "SCOTT"."BONUS" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "SCOTT"."SALGRADE" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" completed with 4 error(s) at 10:47:22

Note: Some errors occurred due to SCOTT user schema exists, by-pass this error normally

3.2.5. Backup datafile and change the file-system into +ASM

RMAN> backup as copy datafile '/stsapp/app/oracle/admin/sts/dpdump/data_D-MSOCIAL_I-1761030358_TS-USERS_FNO-4_a3q495k9' format '+DATAVOL1';

Starting backup at 13-APR-2015 10:52:48
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=/stsapp/app/oracle/admin/sts/dpdump/data_D-MSOCIAL_I-1761030358_TS-USERS_FNO-4_a3q495k9
output file name=+DATAVOL1/sts/datafile/users.256.876912769 tag=TAG20150413T105248 RECID=1 STAMP=876912768
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 13-APR-2015 10:52:49

Starting Control File and SPFILE Autobackup at 13-APR-2015 10:52:49
piece handle=/stsapp/app/oracle/product/11.2.0/sts/dbs/c-1636328243-20150413-01 comment=NONE
Finished Control File and SPFILE Autobackup at 13-APR-2015 10:52:52

RMAN>

3.2.6. Verify the new tablespace & datafile in STS

sys@STS> @?/filespace

Tablespace Name    Filename                                              FILE_ID       File Size Used (in bytes) Pct. Used
------------------ -------------------------------------------------- ---------- --------------- --------------- ---------
INDX               +DATA/sts/datafile/indx01.dbf                               5   1,073,741,824       7,340,032         0
STS                +DATA/sts/datafile/sts01.dbf                                7   1,073,741,824       1,048,576         0
SYSAUX             +DATA/sts/datafile/sysaux.267.875798947                     2   1,073,741,824     516,685,824        48
SYSTEM             +DATA/sts/datafile/system.266.875798947                     1   1,073,741,824     708,247,552        65
TEMP               +DATA/sts/tempfile/temp.274.875799045                       1      30,408,704      29,360,128        96
UNDOTBS1           +DATA/sts/datafile/undotbs1.268.875798947                   3   1,073,741,824      20,643,840         1
USERS              /stsapp/app/oracle/admin/sts/dpdump/data_D-MSOCIAL          8       6,553,600       6,422,528        98   <<----
                   _I-1761030358_TS-USERS_FNO-4_a3q495k9

USERS_STS          +DATA/sts/datafile/users.269.875798947                      4   1,073,741,824      10,813,440         1
USERS_STS          +DATA/sts/datafile/users01.dbf                              6   1,073,741,824       1,048,576         0
                                                                                 --------------- --------------- ---------
avg                                                                                                                     34
sum                                                                                7,553,155,072   1,301,610,496

9 rows selected.

sys@STS>

3.2.7. Offline the new tablespace & datafile

sys@STS> alter database datafile '/stsapp/app/oracle/admin/sts/dpdump/data_D-MSOCIAL_I-1761030358_TS-USERS_FNO-4_a3q495k9' offline;

Database altered.

sys@STS>

3.2.8. Switch the datafile to copy & recover datafile

$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Apr 13 10:59:14 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: STS (DBID=1636328243)

RMAN> switch datafile '/stsapp/app/oracle/admin/sts/dpdump/data_D-MSOCIAL_I-1761030358_TS-USERS_FNO-4_a3q495k9' to copy;

using target database control file instead of recovery catalog
datafile 8 switched to datafile copy "+DATAVOL1/sts/datafile/users.256.876912769"

RMAN> recover datafile '+DATAVOL1/sts/datafile/users.256.876912769';

Starting recover at 13-APR-2015 11:01:14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=439 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=222 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=13 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 13-APR-2015 11:01:15

RMAN>

3.2.9. Put the datafile online

sys@STS> @?/filespace

Tablespace Name    Filename                                              FILE_ID       File Size Used (in bytes) Pct. Used
------------------ -------------------------------------------------- ---------- --------------- --------------- ---------
INDX               +DATA/sts/datafile/indx01.dbf                               5   1,073,741,824       7,340,032         0
STS                +DATA/sts/datafile/sts01.dbf                                7   1,073,741,824       1,048,576         0
SYSAUX             +DATA/sts/datafile/sysaux.267.875798947                     2   1,073,741,824     516,947,968        48
SYSTEM             +DATA/sts/datafile/system.266.875798947                     1   1,073,741,824     708,247,552        65
TEMP               +DATA/sts/tempfile/temp.274.875799045                       1      30,408,704      29,360,128        96
UNDOTBS1           +DATA/sts/datafile/undotbs1.268.875798947                   3   1,073,741,824       8,585,216         0
USERS              +DATAVOL1/sts/datafile/users.256.876912769                  8
USERS_STS          +DATA/sts/datafile/users.269.875798947                      4   1,073,741,824      10,813,440         1
USERS_STS          +DATA/sts/datafile/users01.dbf                              6   1,073,741,824       1,048,576         0
                                                                                 --------------- --------------- ---------
avg                                                                                                                     26
sum                                                                                7,546,601,472   1,283,391,488

9 rows selected.

sys@STS> alter datbase datafile '+DATAVOL1/sts/datafile/users.256.876912769' online;
alter datbase datafile '+DATAVOL1/sts/datafile/users.256.876912769' online
      *
ERROR at line 1:
ORA-00940: invalid ALTER command

Note: Due to offline normal datafile USERS, when recover and bring up we have got the error. Solution

sys@STS> desc v$recover_file
 Name                                                                             Null?    Type
 -------------------------------------------------------------------------------- -------- ------------------------------------------------------
 FILE#                                                                                     NUMBER
 ONLINE                                                                                    VARCHAR2(7)
 ONLINE_STATUS                                                                             VARCHAR2(7)
 ERROR                                                                                     VARCHAR2(18)
 CHANGE#                                                                                   NUMBER
 TIME                                                                                      DATE

sys@STS> select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
         8 OFFLINE OFFLINE OFFLINE NORMAL                                                             0

sys@STS> alter tablespace users offline;

Tablespace altered.

sys@STS> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Apr 13 11:04:50 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: STS (DBID=1636328243)

RMAN> recover datafile '+DATAVOL1/sts/datafile/users.256.876912769';

Starting recover at 13-APR-2015 11:05:09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=439 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=646 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=13 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 13-APR-2015 11:05:10

RMAN> sql 'alter tablespace users online';

sql statement: alter tablespace users online

RMAN> exit


Recovery Manager complete.
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 13 11:05:25 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

sys@STS> select * from v$recover_file;

no rows selected

Appendix: filespace SQL script

-- +----------------------------------------------------------------------------+                                                                            |
-- | DATABASE : Oracle        							|
-- | Author	  : Tuan Anh Tran -                    |
-- | FILE     : dba_file_space_usage.sql                                        |
-- | CLASS    : Database Administration                                         |
-- | PURPOSE  : Reports on all data file usage. This script was designed to     |
-- |            work with Oracle8i or higher. It will include true TEMPORARY    |
-- |            tablespaces. (i.e. use of "tempfiles")                          |
-- +----------------------------------------------------------------------------+

SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY   OFF

COLUMN tablespace  FORMAT a18             	HEADING 'Tablespace Name'
COLUMN filename    FORMAT a50             	HEADING 'Filename'
COLUMN filesize    FORMAT 9999,999,999,999  	HEADING 'File Size'
COLUMN used        FORMAT 9999,999,999,999  	HEADING 'Used (in bytes)'
COLUMN pct_used    FORMAT 999            	HEADING 'Pct. Used'

BREAK ON report
COMPUTE SUM OF filesize  ON report
COMPUTE SUM OF used      ON report
COMPUTE AVG OF pct_used  ON report

spool filespace.lst replace

SELECT /*+ ordered */
    d.tablespace_name                     tablespace
  , d.file_name                           filename
  , d.file_id                             file_id
  , d.bytes                               filesize
  , NVL((d.bytes - s.bytes), d.bytes)     used
  , TRUNC(((NVL((d.bytes - s.bytes) , d.bytes)) / d.bytes) * 100)  pct_used
FROM
    sys.dba_data_files d
  , v$datafile v
  , ( select file_id, SUM(bytes) bytes
      from sys.dba_free_space
      GROUP BY file_id) s
WHERE
      (s.file_id (+)= d.file_id)
  AND (d.file_name = v.name)
UNION
SELECT
    d.tablespace_name                       tablespace 
  , d.file_name                             filename
  , d.file_id                               file_id
  , d.bytes                                 filesize
  , NVL(t.bytes_cached, 0)                  used
  , TRUNC((t.bytes_cached / d.bytes) * 100) pct_used
FROM
    sys.dba_temp_files d
  , v$temp_extent_pool t
  , v$tempfile v
WHERE 
      (t.file_id (+)= d.file_id)
  AND (d.file_id = v.file#)
ORDER BY 1
/

spool off

Hope this help.
TaT