ANBOB™

今天我得到table用dbms_metadata的DDL建表语句时，提示了ora 25153错误：临时表空间为空

SQL> conn / as sysdba
Connected.
SQL> select dbms_metadata.get_ddl('TABLE','SQLLDR_TEST','ANBOB') from dual;
ERROR:
ORA-25153: Temporary Tablespace is Empty
ORA-06512: at "SYS.DBMS_LOB", line 443
ORA-06512: at "SYS.DBMS_METADATA", line 2729
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1

no rows selected

SQL> show user
USER is "SYS"
SQL> SELECT * FROM v$tempfile;

no rows selected

SQL> COL PROPERTY_VALUE FOR A30
SQL> RUN
  1* select property_name,PROPERTY_VALUE from database_properties where property_name like '%TEMP%'

PROPERTY_NAME                                                PROPERTY_VALUE
------------------------------------------------------------ ------------------------------
DEFAULT_TEMP_TABLESPACE                                      TEMP

SQL> select * from v$tablespace;

       TS# NAME                                                         INCLUD BIGFIL FLASHB ENCRYP
---------- ------------------------------------------------------------ ------ ------ ------ ------
         0 SYSTEM                                                       YES    NO     YES
         7 TEST                                                         YES    NO     YES
         2 SYSAUX                                                       YES    NO     YES
         4 USERS                                                        YES    NO     YES
         6 EXAMPLE                                                      YES    NO     YES
         9 TT                                                           YES    NO     YES
        19 TBSLOGMNR                                                    YES    NO     YES
         5 UNDOTBS2                                                     YES    NO     YES
         3 TEMP                                                         NO     NO     YES
        20 SMAILTBS                                                     YES    NO     YES

10 rows selected.

SQL> host
[oracle@orazhang ~]$ cd /u01/app/oracle/oradata/ORCL/datafile/
[oracle@orazhang datafile]$ ls
o1_mf_example_6cgckxc7_.dbf  o1_mf_system_6cgckx95_.dbf    o1_mf_temp_6cgcv90w_.tmp      o1_mf_users_6cgckxds_.dbf  tbsg1.gdbf  tt1.dbf  undotbs2.dbf
o1_mf_sysaux_6cgckx9p_.dbf   o1_mf_tbslogmn_6vdjocp1_.dbf  o1_mf_undotbs2_6vl4kd8r_.dbf  smailtbs.dbf               test.dbf    tt.dbf

[oracle@orazhang datafile]$ exit
exit

SQL> select file_name,tablespace_name from dba_temp_files;

no rows selected
SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/ORCL/datafile/temp.dbf' size 100m;

Tablespace altered.

SQL> select * from v$tempfile;

     FILE# CREATION_CHANGE# CREATION_TIME         TS#     RFILE# STATUS         ENABLED                   BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE
---------- ---------------- -------------- ---------- ---------- -------------- -------------------- ---------- ---------- ------------ ----------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         1         46075147 17-5月 -11              3          1 ONLINE         READ WRITE            104857600      12800    104857600       8192
/u01/app/oracle/oradata/ORCL/datafile/temp.dbf

SQL> select dbms_metadata.get_ddl('TABLE','SQLLDR_TEST','ANBOB') from dual;

DBMS_METADATA.GET_DDL('TABLE','SQLLDR_TEST','ANBOB')
--------------------------------------------------------------------------------

  CREATE TABLE "ANBOB"."SQLLDR_TEST"
   (    "COL1" VARCHAR2(10),
        "COL2" VARCHA

SQL> set long 2000
SQL> select dbms_metadata.get_ddl('TABLE','SQLLDR_TEST','ANBOB') from dual;

DBMS_METADATA.GET_DDL('TABLE','SQLLDR_TEST','ANBOB')
--------------------------------------------------------------------------------

  CREATE TABLE "ANBOB"."SQLLDR_TEST"
   (    "COL1" VARCHAR2(10),
        "COL2" VARCHAR2(20),
        "COL3" NUMBER(*,0),
        "COL4" NUMBER(*,0),
        "COL5" NUMBER(*,0),
        "COL6" VARCHAR2(30)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

DBMS_METADATA.GET_DDL('TABLE','SQLLDR_TEST','ANBOB')
--------------------------------------------------------------------------------
  TABLESPACE "USERS"

SQL>

搞来搞去，临时表空间的的文件没有了，啥原因呢？请待下次分解！

打赏

微信扫一扫，打赏作者吧～