Query to generate Baseline Script for a SQL_ID in Oracle Database
Please use the below query. col sql_text for a60 wrap set verify off set pagesize 999 set lines 155 col username format a13 col prog format a22 col sid format…
Read MoreSteps to Export and Import AWR Stats
Please find the steps to export and import AWR stats. Exporting AWR snapshot data:- ***************************** SQL> @?/rdbms/admin/awrextr.sql AWR EXTRACT **************************************************** This script will extract the AWR data for a range of snapshots into a dump file. The script…
Read MoreSteps to Backup and Restore Java Classes and Privileges only in Oracle Database
The following SQL script, when run as user SYS, will generate an ordered script to recreate all the java grants, and java policies, assigned to users. It does not generate…
Read MorePurge AUD$ Table
The AUD$ table growth will impact the performance of database so we want to purge it regularly by using the DBMS_AUDIT_MGMT package Check AUD$ table is not in SYSTEM table tablespace.…
Read MoreMoving AUD$ table to another tablespace using DBMS_AUDIT_MGMT
Steps to move the Audit table from SYSTEM tablespace to newly created tablespace. • Audit trail tables called SYS.aud$ and SYS.fga_log$ keeps all Audit records in the Oracle database and…
Read MoreCREATING AN EXTERNAL USER
EXTERNAL USER CREATION External users and their authentication are managed by external services such as operating system or a network service. If our OS environment is secured, we can enable…
Read MoreORA-01000 maximum open cursors exceeded
CURSORS A cursor is a temporary work area created in the system memory when a SQL statement is executed. It can hold more than one row but can process only…
Read MoreCONNECT TO USER WITHOUT KNOWING PASSWORD
We can connect to another user without knowing the password, with grant connect through privilege In this case a user TEST1 wants to connect to TEST2 user and create a…
Read MoreCREATE PASSWORD FILE IN ASM DISK GROUP
CREATE PASSWORD FILE IN ASM DISK GROUP FOR ORACLE 12C ONLY ASMCMD> pwcreate –dbuniquename {db_unique_name} {file_path} {sys_password} ASMCMD> pwcreate –dbuniquename PRDPRE +DATA/PWDFILE/pwdPRDPREoracle FOR ALL VERSION orapwd file=’+DATA/orapwPRODPRE’ ENTRIES=10 DBUNIQUENAME=’PRODPRE’
Read MoreModify ASM user password
LIST ASM USERS ASMCMD> lspwusr Username sysdba sysoper sysasm SYS TRUE TRUE TRUE CRSUSER__ASM_001 TRUE FALSE TRUE ASMSNMP TRUE FALSE FALSE MODIFY USER PASSWORD ASMCMD> orapwusr –modify asmsnmp Enter password:…
Read More