Oracle 一些操作

Achivelog
============================

alter system set db_recovery_file_dest='F:ORACLE ecovery_area' scope=both;
alter system set db_recovery_file_dest_size=100G scope=both;

Select big table

================================

select table_name,blocks*8192/1024/1024 size_m from user_tables order by size_m desc nulls last;

Create DB Link

=====================================

create public database link ORCL_AA
connect to username identified by "1234"
using 'ORCL_CC';

Set Password Unlimited

======================================

SELECT username, PROFILE FROM dba_users
where username in('SYS','SYSTEM','MGMT_VIEW','WDM_APP','SYSMAN','DBSNMP');

SELECT * FROM dba_profiles s
WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';

SELECT * FROM dba_profiles s
WHERE s.profile='MONITORING_PROFILE' AND resource_name='PASSWORD_LIFE_TIME';

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;  
ALTER PROFILE MONITORING_PROFILE LIMIT PASSWORD_LIFE_TIME UNLIMITED; 

Alter Process

===================================

select count(*) from v$session
Select count(*) from v$session where status='ACTIVE'
show parameter processes
alter system set processes = 2000 scope = spfile;

Modify Log Path

===========================================================================

-- 修改重做日志文件 路径
select * from V$logfile;

shutdown immediate;
-- 迁移文件 到相关目录
startup mount;

alter database rename file 'D:APPORACLEORADATAORCLREDO01.LOG' to 'F:ORACLEORADATAORCLREDO01.LOG';
alter database rename file 'D:APPORACLEORADATAORCLREDO02.LOG' to 'F:ORACLEORADATAORCLREDO02.LOG';
alter database rename file 'D:APPORACLEORADATAORCLREDO03.LOG' to 'F:ORACLEORADATAORCLREDO03.LOG';

alter database open;
-- ------------------------------------------------------------------------------------------------------------

-- 创建多路控制文件

select name from v$controlfile;

a)、shutdown immediate;

b)、startup nomount;

c)、
alter system set control_files =
'D:APPORACLEORADATAORCLCONTROL01.CTL',
'D:APPORACLERECOVERY_AREAORCLCONTROL02.CTL',
'F:ORACLEORADATAORCLCONTROL03.CTL'
scope = spfile;

d)、shutdown immediate;

e)、startup;

Temp Tablespace   常出现在Rman 异机恢复后的操作

============================================================

create temporary tablespace temp_1
tempfile 'F:ORACLEORADATAORCLTEMP_01.DBF' size 1G reuse
autoextend on next 64M  maxsize UNLIMITED;

create temporary tablespace temp_2
tempfile 'F:ORACLEORADATAORCLTEMP_02.DBF' size 1G reuse
autoextend on next 64M  maxsize UNLIMITED;

alter tablespace temp_1 tablespace group temp_group;
alter tablespace temp_2 tablespace group temp_group;

alter database default temporary tablespace temp_group;

select * from dba_tablespace_groups;

alter tablespace temp1 tablespace group '';

alter database tempfile 'D:APPORACLEORADATAORCLTEMP01.DBF' drop;