???????????????????

???????????? ???????[ 2013/6/17 10:14:34 ] ????????

??????????????????

?????????????????????????????????????????????????????

SQL> alter tablespace temp begin backup;
alter tablespace temp begin backup
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE


SQL> alter temporary tablespace temp begin backup;
alter temporary tablespace temp begin backup
      *
ERROR at line 1:
ORA-00940: invalid ALTER command

?????????????????????(λ??澯?????alert_orcl.log)

????Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_dbw0_2230.trc:
????ORA-01186??file 3 failed verification tests
????ORA-01157??cannot identify/lock data file 3 - see DBWR trace file
????ORA-01110??data file 3:'/u01/app/oracle/oradata/orcl/temp01.dbf'

??????????????????????

????1????session 1????????????????????

SQL> show user;
USER is "SYS"
SQL> ho rm /u01/app/oracle/oradata/orcl/temp01.dbf

SQL> alter system checkpoint; -->??м??????

System altered.

????2????session 2??????????????????????????????

SQL> show user;
USER is "SCOTT"
SQL> set autotrace traceonly;
SQL> select owner??object_name from big_table order by id??owner??object_name;
select owner??object_name from big_table order by id??owner??object_name
                              *
ERROR at line 1:
ORA-01565: error in identifying file '/u01/app/oracle/oradata/orcl/temp01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

????3????session 1?????????????????????

SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf'
2 size 30m autoextend on next 1m maxsize 2g;

Tablespace altered.

????4????session 2?м????????????????????г??

SQL> select owner??object_name from big_table order by id??owner??object_name;

1000000 rows selected.

????5????????????????????????????

SQL> set linesize 160                            --> session 1?в?????????????????С?0????????online??
SQL> select s.name tbsname??t.name??(t.bytes/1024/1024) bytes??t.status
2 from v$tablespace s??v$tempfile t
3 where s.ts# = t.ts#;

TBSNAME              NAME                                               BYTES STATUS
-------------------- --------------------------------------------- ---------- -------
TEMP                 /u01/app/oracle/oradata/orcl/temp01.dbf                0 ONLINE
TEMP                 /u01/app/oracle/oradata/orcl/temp02.dbf               30 ONLINE

SQL> ho ls /u01/app/oracle/oradata/orcl/temp01.dbf
ls: /u01/app/oracle/oradata/orcl/temp01.dbf: No such file or directory

????6?????????????????????????????????

SQL> alter tablespace temp drop tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf';

Tablespace altered.

????7????????伶????????????????????????裬?????????

????a??????????μ????????

????b?????alter database ??????л????μ???????????????μ????????????

????c?????????????????

????1 2 3????

???????????????????????漰???????????????????SPASVOС??(021-61079698-8054)?????????????????????????