Oracle-重命名数据文件-2023年2月2日
表空间信息查询
set linesize 400 pagesize 500
col name for a70
col tablespace for a20
select online_status "Status",
tablespace_name "Tablespace",
t1.file_id,
file_name "Name",
round(t1.bytes / 1024 / 1024, 2) "Size(MB)",
round((t1.bytes - nvl(t2.bytes, 0)) / 1024 / 1024, 2) "Used(MB)",
round((t1.bytes - nvl(t2.bytes, 0)) / t1.bytes * 100, 2) "Used%",
autoextensible "Autoextensible",
t1.increment_by "Inc (bs)",
t1.online_status "Status"
from dba_data_files t1
left join (select file_id, sum(bytes) bytes
from dba_free_space
group by file_id) t2
on t1.file_id = t2.file_id
order by t1.tablespace_name, t1.file_id;
Status Tablespace FILE_ID Name Size(MB) Used(MB) Used% Aut Inc (bs) Status
------- -------------------- ---------- ---------------------------------------------------------------------- ---------- ---------- ---------- --- ---------- -------
ONLINE DDL_AUDIT 5 /oradata/orcl/ddl_audit_01.dbf 200 1.63 .81 YES 1 ONLINE
ONLINE SYSAUX 2 /oradata/orcl/sysaux01.dbf 600 542.94 90.49 YES 1280 ONLINE
SYSTEM SYSTEM 1 /oradata/orcl/system01.dbf 700 383.13 54.73 YES 1280 SYSTEM
ONLINE UNDOTBS1 3 /oradata/orcl/undotbs01.dbf 415 16.25 3.92 YES 640 ONLINE
ONLINE USERS 4 /oradata/orcl/users01.dbf 5 1.06 21.25 YES 160 ONLINE
set linesize 400 pagesize 500
col TABLESPACE_NAME for a20
col CONTENTS for a10
select status,
t1.tablespace_name,
contents,
extent_management "Extent Management",
segment_space_management "Segment Space Management",
allocation_type "Allocation Type",
logging,
force_logging,
round(t1.maxsize / 1024 / 1024 / 1024, 2) "MaxSize(GB)",
round(t1.bytes / 1024 / 1024 / 1024, 3) "File Size(GB)",
round((t1.bytes - t2.bytes) / 1024 / 1024 / 1024, 3) "Used(GB)",
round((t1.bytes - t2.bytes) / t1.bytes * 100, 2) "Used%",
round((t1.maxsize - t1.bytes + t2.bytes) / 1024 / 1024 / 1024, 2) "AVL Size(GB)",
round((t1.maxsize - t1.bytes + t2.bytes) / (t1.bytes - t2.bytes), 1) x,
t2.fsfi,
t2.frags
from (select tablespace_name, sum(bytes) bytes, sum(maxsize) maxsize
from (select tablespace_name, bytes, maxbytes maxsize
from dba_data_files
where autoextensible = 'YES'
union all
select tablespace_name, bytes, bytes maxsize
from dba_data_files
where autoextensible = 'NO')
group by tablespace_name) t1,
(select tablespace_name,
sum(bytes) bytes,
round(sqrt(max(blocks) / sum(blocks)) /
sqrt(sqrt(count(blocks))) * 100,
0) fsfi,
count(blocks) frags
from dba_free_space
group by tablespace_name) t2,
dba_tablespaces
where dba_tablespaces.tablespace_name = t1.tablespace_name
and dba_tablespaces.tablespace_name = t2.tablespace_name
order by "Used%" desc;
STATUS TABLESPACE_NAME CONTENTS Extent Man Segmen Allocatio LOGGING FOR MaxSize(GB) File Size(GB) Used(GB) Used% AVL Size(GB) X FSFI FRAGS
--------- -------------------- ---------- ---------- ------ --------- --------- --- ----------- ------------- ---------- ---------- ------------ ---------- ---------- ----------
ONLINE SYSAUX PERMANENT LOCAL AUTO SYSTEM LOGGING NO 32 .586 .53 90.49 31.47 59.4 45 15
ONLINE SYSTEM PERMANENT LOCAL MANUAL SYSTEM LOGGING NO 32 .684 .374 54.73 31.63 84.5 84 2
ONLINE USERS PERMANENT LOCAL AUTO SYSTEM LOGGING NO 32 .005 .001 21.25 32 30839.5 100 1
ONLINE UNDOTBS1 UNDO LOCAL MANUAL SYSTEM LOGGING NO 32 .405 .016 3.92 31.98 2015.5 59 8
ONLINE DDL_AUDIT PERMANENT LOCAL AUTO SYSTEM LOGGING NO 32 .195 .002 .81 32 20163.9 71 4
附上批量修改数据文件名的语句
set pagesize 999
set linesize 999
--日志文件
select 'alter database rename file '||''''||member||''''||' to '||chr(39)||replace(member,'/oradata/orcl/','/oradata2/orcl/')||''';' from v$logfile ;
--临时文件
select 'alter database rename file '||''''||name||''''||' to '||chr(39)||replace(name,'/oradata/orcl/','/oradata2/orcl/')||''';' from v$tempfile ;
--数据文件
--第一种
select 'alter tablespace ' ||tablespace_name|| ' rename datafile '||''''||file_name||''''||' to '||chr(39)||replace(file_name,'/oradata/orcl/','/oradata2/orcl/')||''';' from dba_data_files;
--select 'alter tablespace ' ||tablespace_name|| ' rename datafile '||''''||name||''''||' to '||chr(39)||replace(name,'/oradata/orcl/','/oradata2/orcl/')||''';' from v$datafile ;
--select 'alter tablespace ' ||tablespace_name|| ' rename datafile '||''''||file_name||''''||' to '||chr(39)||replace(file_name,'/oradata/orcl/','/oradata2/orcl/')||''';' from dba_data_files where tablespace_name='SYSTEM';
--select 'alter tablespace ' ||tablespace_name|| ' rename datafile '||''''||file_name||''''||' to '||chr(39)||replace(file_name,'/oradata/orcl/','/oradata2/orcl/')||''';' from dba_data_files where tablespace_name in ('SYSTEM');
--select 'alter tablespace ' ||tablespace_name|| ' rename datafile '||''''||file_name||''''||' to '||chr(39)||replace(file_name,'/oradata/orcl/','/oradata2/orcl/')||''';' from dba_data_files where tablespace_name not in ('SYSAUX','SYSTEM','USERS','UNDOTBS1');
--第二种
select 'alter database rename file '||''''||file_name||''''||' to '||chr(39)||replace(file_name,'/oradata/orcl/','/oradata2/orcl/')||''';' from dba_data_files;
--select 'alter database rename file '||''''||name||''''||' to '||chr(39)||replace(name,'/oradata/orcl/','/oradata2/orcl/')||''';' from v$datafile ;
--select 'alter database rename file '||''''||file_name||''''||' to '||chr(39)||replace(file_name,'/oradata/orcl/','/oradata2/orcl/')||''';' from dba_data_files where tablespace_name='SYSTEM';
--select 'alter database rename file '||''''||file_name||''''||' to '||chr(39)||replace(file_name,'/oradata/orcl/','/oradata2/orcl/')||''';' from dba_data_files where tablespace_name in ('SYSTEM');
--select 'alter database rename file '||''''||file_name||''''||' to '||chr(39)||replace(file_name,'/oradata/orcl/','/oradata2/orcl/')||''';' from dba_data_files where tablespace_name not in ('SYSAUX','SYSTEM','USERS','UNDOTBS1');
第一种
这种方式需要数据库处于open状态,表空间在offline的状态下才能更改。
--alter tablespace users rename datafile 'xxxx' to ‘xxxx';
alter tablespace users offline;
select 'cp -a ' ||''''||file_name||''''||' '||chr(39)||replace(file_name,'/oradata/orcl/','/oradata2/orcl/')||'''' from dba_data_files where tablespace_name in ('TEST');
--cp /opt/ora10g/oradata/orcl/user01aa.dbf /opt/ora10g/oradata/orcl/user01bb.dbf
select 'alter tablespace ' ||tablespace_name|| ' rename datafile '||''''||file_name||''''||' to '||chr(39)||replace(file_name,'/oradata/orcl/','/oradata2/orcl/')||''';' from dba_data_files where tablespace_name in ('TEST');
alter tablespace users rename datafile '/opt/ora10g/oradata/orcl/user01aa.dbf' to '/opt/ora10g/oradata/orcl/user01bb.dbf' ;
alter tablespace users online;
alter system switch logfile;
select * from dba_data_files;
--备库操作
--alter tablespace test add datafile '/oradata3/orcl/test_03.dbf' size 200M autoextend on;
show parameter name
alter system set db_file_name_convert='/oradata/orcl/','/oradata/orcldg/','/oradata2/orcl/','/oradata/orcldg/' scope=spfile;
alter database recover managed standby database cancel;
shu immediate
startup
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY PARALLEL 2 USING CURRENT LOGFILE DISCONNECT FROM SESSION;
show parameter name
--alter system set standby_file_management=manual;
--alter database create datafile '/u01/app/oracle/product/11G_R2/db/dbs/UNNAMED00008' as '/oradata/orcldg/test_03.dbf';
--alter system set standby_file_management=auto;
第二种
这种方式需要数据库处于mount状态
--alter database rename file 'xxxx' to ‘xxxx';
set line 400 pagesize 500
select 'cp -a ' ||''''||file_name||''''||' '||chr(39)||replace(file_name,'/oradata/orcl/','/oradata2/orcl/')||'''' from dba_data_files where tablespace_name in ('TEST');
select 'alter database rename file '||''''||file_name||''''||' to '||chr(39)||replace(file_name,'/oradata/orcl/','/oradata2/orcl/')||''';' from dba_data_files where tablespace_name='SYSTEM';
lsnrctl stop
lsnrctl status
shu immediate
startup mount
--cp /opt/ora10g/oradata/orcl/user01bb.dbf /opt/ora10g/oradata/orcl/user01aa.dbf
alter database rename file '/opt/ora10g/oradata/orcl/user01bb.dbf' to '/opt/ora10g/oradata/orcl/user01aa.dbf';
alter database open;