oracle-变更-查看及变更数据文件datafile常用操作

数据文件相关操作

查看datafile的日志路径

select distinct 'datafile', (substr(name,0,instr(name,'/',-1) -0))
from v$datafile;

查看tempfile的日志路径

select distinct 'tempfile',substr(name,0,instr(name,'/',-1) -0)
from v$tempfile;

查看redo和standby log的日志路径

select  substr(member,instr(member,'/',-1)), substr(member,0,instr(member,'/',-1) -0)
from v$logfile;

select  distinct 'redo-standby',substr(member,0,instr(member,'/',-1) -0)
from v$logfile;

查看原来的数据文件路径,生成修改成新的路径语句rename datafile

SQL> select  'set newname for datafile '||file#|| ' to ''' ||'/data01/oradata01/prod2'||substr(name,instr(name,'/',-1)) ||''';'  aa from v$datafile;
set newname for datafile 1 to '/data01/oradata01/prod2/system01.dbf';

rename logfile，rename tempfile

SQL> select 'alter database rename file '''||member|| ''' to '''  ||'/data01/oradata01/prod2'||substr(member,instr(member,'/',-1)) ||''';'  aa from v$logfile;

alter database rename file '/u1/PROD/prodora/db/apps_st/data/log02a.dbf' to '/data01/oradata01/prod/log02a.dbf';
###tempfile

SQL> select 'alter database rename file '''||name|| ''' to '''  ||'/data01/oradata01/prod2'||substr(name,instr(name,'/',-1)) ||''';'  aa from v$tempfile;

alter database rename file '/u1/PROD/prodora/db/apps_st/data/temp01.dbf'  to '/data01/oradata01/prod/temp01.dbf'; 

把自动增长文件修改成非自动增长

SELECT 'ALTER DATABASE DATAFILE ''' || file_name || ''' AUTOEXTEND OFF;',
       file_name,
       tablespace_name,
       bytes / 1024 / 1024 / 1024 ,
       AUTOEXTENSIBLE,
       maxbytes / 1024 / 1024 / 1024,
       user_bytes / 1024 / 1024 / 1024,
       online_status
  FROM dba_data_files
 WHERE file_name LIKE '/oradata05%' AND AUTOEXTENSIBLE = 'YES';
 

把非自动增长文件修改成自动增长

SELECT 'ALTER DATABASE DATAFILE ''' || file_name || ''' AUTOEXTEND ON NEXT 100M MAXSIZE 30G;',
       file_name,
       tablespace_name,
       bytes / 1024 / 1024 / 1024 ,
       AUTOEXTENSIBLE,
       maxbytes / 1024 / 1024 / 1024,
       user_bytes / 1024 / 1024 / 1024,
       online_status
  FROM dba_data_files
 WHERE tablespace_name='USERS' AND AUTOEXTENSIBLE = 'NO' ORDER BY 4; 

为表空间添加文件，生成脚本，再修改脚本中的文件号之后执行

select * from (SELECT 'ALTER TABLESPACE'||tablespace_name|| 'ADD DATAFILE ''' || file_name || ''' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G;',
      file_name,file_id,
      tablespace_name,
      AUTOEXTENSIBLE,
      online_status
 FROM dba_data_files
WHERE tablespace_name='TEST_INX' AND  file_id =(select max(file_id) from dba_data_files WHERE tablespace_name='TEST_INX' )) connect by 0 + level <= 15;