Some Oracle operations

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

Alter system set db_recovery_file_dest= ' F:\ORACLE\recovery_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 = SPFile;

Modify Log Path

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

--Modifying the Redo log file path
SELECT * from V$logfile;

Shutdown immediate;
--Migrating files to related directories
startup Mount;

Alter DATABASE rename file ' D:\APP\ORACLE\ORADATA\ORCL\REDO01. LOG ' to ' F:\ORACLE\ORADATA\ORCL\REDO01. LOG ';
Alter DATABASE rename file ' D:\APP\ORACLE\ORADATA\ORCL\REDO02. LOG ' to ' F:\ORACLE\ORADATA\ORCL\REDO02. LOG ';
Alter DATABASE rename file ' D:\APP\ORACLE\ORADATA\ORCL\REDO03. LOG ' to ' F:\ORACLE\ORADATA\ORCL\REDO03. LOG ';

ALTER DATABASE open;
-- ------------------------------------------------------------------------------------------------------------

--Create a multi-path control file

Select name from V$controlfile;

A), shutdown immediate;

b), startup Nomount;

c),
alter system Set Control_files =
' D:\APP\ORACLE\ORADATA\ORCL\CONTROL01. CTL ',
' D:\APP\ORACLE\RECOVERY_AREA\ORCL\CONTROL02. CTL ',
' F:\ORACLE\ORADATA\ORCL\CONTROL03. CTL '
scope = SPFile;

D), shutdown immediate;

e), startup;

Temp tablespace often occurs after an Rman machine recovery operation

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

Create temporary tablespace temp_1
Tempfile ' F:\ORACLE\ORADATA\ORCL\TEMP_01.DBF ' size 1G reuse
Autoextend on next 64M maxsize UNLIMITED;

Create temporary tablespace temp_2
Tempfile ' F:\ORACLE\ORADATA\ORCL\TEMP_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:\APP\ORACLE\ORADATA\ORCL\TEMP01. DBF ' drop;

Some operations by Oracle