col USER_NAME format a15
col POLICY_NAME format a15
col USER_PRIVILEGES format a15
col USER_LABELS format a20
select USER_NAME,POLICY_NAME,USER_PRIVILEGES,USER_LABELS from dba_sa_users
where USER_NAME=’APPS’;
procedure or package backup
set verify off
set feedback off
set lines 132
set pages 0
set heading off
set space 0
set recsep off
column text format a79
column line noprint
Spool ABC_CRS_TASKS_DL_bak.pkg
select DECODE(line,1,'create or replace ','')||text
from dba_source
where owner = upper('apps')
and type = upper('package')
and name = upper('ABC_CRS_TASKS_DL')
and line >= 1;
select DECODE(line,1,'create or replace ','')||text
from dba_source
where owner = upper('apps')
and type = upper('package body')
and name = upper('ABC_CRS_TASKS_DL')
and line >= 1;
spool off
Script to compile package,procedure,view manually
SQL> select 'alter view '|| OBJECT_NAME ||' compile ;' from user_objects
where OBJECT_TYPE='VIEW' and status='INVALID';
SQL> select 'alter procedure '|| OBJECT_NAME ||' compile ;' from user_objects
where OBJECT_TYPE='PROCEDURE' and status='INVALID';
SQL> select 'alter package '|| OBJECT_NAME ||' compile body ;' from user_objects
where OBJECT_TYPE='PACKAGE BODY' and status='INVALID';
SQL> select 'alter package '|| OBJECT_NAME ||' compile ;' from user_objects
where OBJECT_TYPE='PACKAGE' and status='INVALID';
select OWNER,OBJECT_TYPE,count(*) from dba_objects where status='INVALID' group by
OBJECT_TYPE,owner;
To compile the package:
alter package packg_name compile (will compile only the header).
alter package packg_name compile body (will compile the body explicitly).
Check To know whether package body also exists etc.
select owner , OBJECT_NAME ,OBJECT_TYPE , status from dba_objects where OBJECT_NAME like '%CSI_ITEM_INSTANCE_PUB%';
Syntax to create synonym
create synonym system.FND_ORACLE_USERID_bkp for system.FND_ORACLE_USERID;
How to set a table in read-only mode
ALTER TABLE empADD CONSTRAINT read_only CHECK (1=1) DISABLE VALIDATE;It is now, impossible to insert, update or delete anything with this table.
How to backup the defination of a View before droping a view.
select dbms_metadata.get_ddl(’VIEW’,'RG_View’,'APPS’) from dual;
How to check if the partitions of a table are set to LOGGING
select partition_name, logging
from dba_tab_partitions
where table_name=’WF_LOCAL_ROLES’;
How to check which object is corrupted when corrupted file_id is know
SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents
WHERE file_id = 64 and 1 between block_id AND block_id + blocks-1;
col file_id format 99
col block_id format 9999999
col blocks format 9999999
col segment_name format a25
col owner format a15
col tablespace_name format a20
set lines 150
SELECT file_id,block_id,blcoks,tablespace_name, segment_type, owner, segment_name
FROM dba_extents
WHERE file_id = &1
and &2 between block_id AND block_id + blocks - 1
Count the number of JAVA Objects in the APPS schema via the SQL:select count(*)
from dba_objects
where owner = 'APPS'
and object_type like '%JAVA%';
To find number of invalid JAVA objects via:
select count(*)
from dba_objects
where owner = 'APPS'
and object_type like '%JAVA%'
and status = 'INVALID';
set linesize 120
set pagesize 60
col java_class format a80 wrap
select dbms_java.longname(object_name) as "JAVA_CLASS", status
from user_objects
where object_type = 'JAVA CLASS'
and status = 'INVALID'
/
To reduce invalid java objects do,
Run adadmin - Reload JAR files
Run adadmin - Compile Apps schema
To compile java class objects
SQL> alter java CLASS "oracle/apps/jtf/cache/CacheMessageManager" compile;
SQL> alter java class "oracle/apps/fnd/security/AolSecurity" resolve ;
Warning: Java altered with compilation errors.
SQL> show err
Errors for JAVA CLASS /50e4719a_AolSecurity:
set echo off
set feedback off
set pagesize 0
spool alter_java.sql
select 'ALTER JAVA SOURCE "' || object_name || '" COMPILE;'
from user_objects
where object_type = 'JAVA SOURCE'
and status = 'INVALID'
/
select 'ALTER JAVA CLASS "' || object_name || '" RESOLVE;'
from user_objects
where object_type = 'JAVA CLASS'
and status = 'INVALID'
/
spool off
set feedback on
set pagesize 120
set echo on
@alter_java.sql
To laod JAVA objects to database manually
Eg:
loadjava asf java/jar asfleadshare.jar
loadjava asg java/jar asgmtran.jar
loadjava csf java/jar csflf.jar
loadjava csf java/jar csflf.jar
loadjava cz java/jar czdb.jar
loadjava eam java/jar eampmsch.jar
loadjava eam java/jar eamwkbch.jar
loadjava ecx java/jar ecxutils.jar
loadjava ecx java/jar ecxprocess.jar
loadjava fnd java/3rdparty/stdalone xmlparserv2.zip
loadjava fnd java/jar fndcct.jar
loadjava fnd java/jar fndsec.jar
loadjava ibe java/jar ibeeos.jar
loadjava inv java/jar invdbtrx.jar
loadjava inv java/jar invlabel.jar
loadjava jtf java/jar jtfaoljdepen.zip
loadjava pay java/jar payproc.jar
loadjava pay java/jar payutil.jar
loadjava per java/jar perimage.jar
loadjava per java/jar perkpiclient.jar
loadjava per java/jar perkigfclient.jar
loadjava wms java/jar wmscrtzn.jar
loadjava xtr java/jar xtrintrp.jar
Related metalink note:To ensure the removal of any unnecessary java class files run the rmjvm.sql and full_jvminst.sql (as per Note 276554.1)
few more egs:
loadjava -user apps/
loadjava -force -u apps/
loadjava -force -resolve -user user/password@database -verbose jars
loadjava -u apps/apps -r $JTF_TOP/java/jar/jtfaoljdepen.zip
loadjava -user apps/
To find java class object details given thier longname
col object_name format a45
col owner format a10
select object_name, status, owner
from dba_objects
where object_type = 'JAVA CLASS'
and dbms_java.longname(object_name) IN
(
'oracle/xml/parser/v2/DOMParser',
'oracle/xml/parser/v2/XMLError',
'oracle/xml/parser/v2/NSName',
'oracle/xml/parser/v2/SAXParser',
'oracle/xml/parser/v2/XMLParser',
'oracle/xml/parser/v2/DTD'
)
;
Table reorg
"SQL> show user;
USER is ""SYS""
SQL> ALTER TABLE ONT.OE_ORDER_LINES_ALL ENABLE ROW MOVEMENT ;
Table altered.
SQL> ALTER TABLE ONT.OE_ORDER_LINES_ALL SHRINK SPACE COMPACT CASCADE;
Table altered.
SQL> ALTER TABLE ONT.OE_ORDER_LINES_ALL SHRINK SPACE CASCADE;"
To grant privileges on one user objects to another user
You could write a simple loop :
begin
for i in
(select distinct table_name from all_tables where owner='USER_A')
loop
execute immediate 'grant all on '||i.table_name||' to USER_B';
end loop;
end;
/
To find users who have DBA role means List of Persons Having DML Access
Select username, created from dba_users where username in (Select grantee from dba_role_privs where granted_role = 'DBA');
To find temporary tablespace usage query
"SELECT s.sid ""SID"",s.username ""User"",s.program ""Program"", u.tablespace ""Tablespace"",
u.contents ""Contents"", u.extents ""Extents"", u.blocks*8/1024 ""Used Space in MB"", q.sql_text ""SQL TEXT"",
a.object ""Object"", k.bytes/1024/1024 ""Temp File Size""
FROM v$session s, v$sort_usage u, v$access a, dba_temp_files k, v$sql q
WHERE s.saddr=u.session_addr
and s.sql_address=q.address
and s.sid=a.sid
and u.tablespace=k.tablespace_name;
How would you check if the tablespace is in backup mode ?- Check in v$backup
SQL> SELECT d.tablespace_name, b.status FROM dba_data_files d, v$backup b
WHERE d.file_id = b.FILE# and b.STATUS = ‘ACTIVE’ ;
how to monitor dba_jobs of materialized view
For more detailed info, pls follow ""How to monitor the progress of a materialized view refresh (MVIEW) [ID 258021.1]
Check for number of failures in query. In case of failures check for any locks/blocking sessions.
SQL> column broken format a6
SQL>
SQL> alter session set nls_date_format='DD-MON-YY hh24:MI:SS';
Session altered.
SQL> select job, last_date last_refresh,next_date next_refresh, total_time,broken, failures, what from dba_jobs where what like '%dbms_refresh%ABSL_MGN_BK_US_OPS_MV1%';
JOB LAST_REFRESH NEXT_REFRESH TOTAL_TIME BROKEN
---------- ------------------------ ------------------------ ---------- ------
FAILURES
----------
WHAT
--------------------------------------------------------------------------------
7643 02-JAN-10 00:00:04 03-JAN-10 00:00:00 331694 N
0
dbms_refresh.refresh('""ABSL"".""ABSL_MGN_BK_US_OPS_MV1""');
To force a logfile switch use
ALTER SYSTEM SWITCH LOGFILE;
Put db in archive mod in 10g
If your DB is in no Archive log mode then change the mode to Archive log.
Set FRA (Flash Recovery Area) in your init/spfile.ora
DB_RECOVERY_FILE_DEST=/u03/FRA
DB_RECOVERY_FILE_DEST_SIZE=4G
Startup up the database in mount state
$ sqlplus / as sysdba
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVE LOG;
SQL> ALTER DATABASE OPEN;
SQL> ARCHIVE LOG LIST;
To force a checkpoint use
ALTER SYSTEM CHECKPOINT;
In Oracle 10.1 and above, the following supported command can be used to flush the buffer cache:
ALTER SYSTEM FLUSH BUFFER_CACHE;
In Oracle 9.0.1 and above the following unsupported command can also be used to flush the buffer cache:
ALTER SESSION SET EVENTS 'immediate trace name flush_cache';
After this command has been executed, the STATE column for all rows in X$BH will be zero unless the block is currently pinned
To flush the shared pool use:
ALTER SYSTEM FLUSH SHARED_POOL;
move all objects from tools to apps_ts_tools and then drop tools tablepsaceEg:
alter TABLE USIDBA.USI_ALERT_LOG move tablespace APPS_TS_TOOLS
alter index USIDBA.USII0_INFO rebuild online tablespace APPS_TS_TOOLS parallel 2;
To check on which directories you have privilege to read & write:
#################################################################
SQL> SELECT privilege, directory_name
2 FROM user_tab_privs t, all_directories d
3 WHERE t.table_name(+)=d.directory_name
4 ORDER BY 2,1;
Re-creating Dba_directories
drop directory COST_DIR;
create directory COST_DIR as '/d01/oravis/visappl/xxhem/11.5.0/datain/Cost';
grant all on directory COST_DIR to apps;
Using ANALYZE command is little bit older now. If you are using oracle >8i then you have new package called dbms_stats.
To analyze schema use
dbsm_stats.gather_schema_stats 'USERNAME',casacde=>true,estimate_percent=>10);
For table use the following
dbms_stats.gather_table_stats
syntax for analysing a table is :
ANALYZE TABLE emp ESTIMATE STATISTICS;
The full syntax (not using DBMS package) is
ANALYZE TABLE xxxxx ESTIMATE STATISTICS SAMPLE 20 PERCENT;
analyze table Dec6Table4 compute statistics;
Oracle will actually read through all the blocks/rows to compute the true value of the stats"
To drop and re-create the statistics on sys schema
SQL> EXECUTE DBMS_STATS.DELETE_SCHEMA_STATS('SYS');
SQL> EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('SYS');
exec fnd_stats.gather_schema_statistics('SYS');
exec fnd_stats.gather_schema_statistics('SYSTEM');
exec fnd_stats.gather_schema_statistics('ONT');
sqlplus apps/
SQL> exec fnd_stats.gather_schema_statistics('MRP'); <- One schema
SQL> exec fnd_stats.gather_schema_statistics('ALL'); <- All schemas
SQL> exec fnd_stats.gather_table_stats('MRP','MRP_FORECAST_DATES'); <- One table
exec fnd_stats.gather_schema_statistics(SCHEMANAME =>'XXBR',ESTIMATE_PERCENT=>10,HMODE=>'LASTRUN',OPTIONS=>'GATHER AUTO',MODPERCENT=>5,INVALIDATE=>'Y');
To find out a hidden parameter value in the database
SELECT a.ksppinm ""Parameter"",
b.ksppstvl ""Session Value"",
c.ksppstvl ""Instance Value""
FROM x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE a.indx = b.indx AND
a.indx = c.indx AND
a.ksppinm LIKE '%kgl_large_heap%';
To get a list of all deprecated initialization parameters, issue the following SQL statement:
SELECT name FROM v$parameter WHERE isdeprecated = 'TRUE';
How to Inovke SQLPLUS in scripts in Oracle 10g !!!
In Oracle 9i we use ""sqlplus -s @start_mydb.sql"" to start the database. But, where as in Oracle 10g this command simply hangs and will not do anything.
To invoke SQLPLUS in shell/bat scripts in Oracle 10g, We need to give below command.
sqlplus /NOLOG @start_mydb.sql
How to find the ORACLE_HOME path in Oracle Database?
In 9i:
SELECT substr(file_spec,1,instr(file_spec,'lib')-2) ORACLE_HOME FROM dba_libraries
WHERE library_name='DBMS_SUMADV_LIB';
In 10g:
SQL > var OHM varchar2(100);
SQL > EXEC dbms_system.get_env('ORACLE_HOME', :OHM) ;
SQL > PRINT OHM
Linux/Unix:
echo $ORACLE_HOME
Windows:
start - run - regedit (enter) - HKEY_LOCAL_MACHINE - SOFTWARE - ORACLE
Type the below command at prompt if the environment variable is defined for ORACLE_HOME
c:\> set oracle_home (ENTER)
Using ORA_ROWSCN and SCN_TO_TIMESTAMP to find the last updated date or timestamp of a record.
SQL> SELECT scn_to_timestamp(ORA_ROWSCN) FROM emp WHERE empno=7839;
SCN_TO_TIMESTAMP(ORA_ROWSCN)
------------------------------------
02-SEP-08 03.20.20.000000000 PM"
To find database characterset
SQL> SELECT * FROM nls_database_parameters;
SQL> select * from nls_database_parameters
2 where parameter in ('NLS_LANGUAGE','NLS_TERRITORY','NLS_CHARACTERSET');
PARAMETER VALUE
------------------------------ ----------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CHARACTERSET WE8ISO8859P1
"
"Set the NLS_LANG environment variable according the database character set and language details.
$ export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
Following script can be used to construct the dblink.
SELECT‘create ‘||DECODE(U.NAME,’PUBLIC’,'public ‘)||’database link ‘||CHR(10)||DECODE(U.NAME,’PUBLIC’,Null, U.NAME||’.')|| L.NAME||chr(10) ||’connect to ‘ || L.USERID || ‘ identified by ”’||L.PASSWORD||”’ using ”’ || L.host || ”” ||chr(10)||’;’ TEXTFROM sys.link$ L, sys.user$ UWHERE L.OWNER# = U.USER# ;
To drop db links
SQL> select 'drop database link '||b.username||'.'||c.name||' ;'
from dba_users b ,link$ c
where c.owner#=b.user_id
union
select 'drop public database link '||c.name||' ;'
from link$ c
where owner#=1;
To test DB links
select * from dual@link name; need to put tns entries for using colomun
To find/create DB links
select 'DB link for '||b.username||chr(10)||' create database link '||c.name||' connect to '||c.userid|| ' identified by '||c.password||' using '''||c.host||''';'
from dba_users b ,link$ c
where c.owner#=b.user_id
union
select 'DB link for Public' ||chr(10)|| ' create public database link '||c.name||' connect to
'||c.userid|| ' identified by '||c.password||' using '''||c.host||''';'
from link$ c
where owner#=1
;
select owner||','||DB_LINK||','||USERNAME||','||HOST||','||created from dba_db_links;
To create db link b/w two instances:
VIS is the non-prod ASCP instance. All ASCP related db links should point to VIS.
Login to the DB as system user:
Drop public database link PLAN;
Drop public database link VIS;
Create public database link PLAN connect to APPS identified by APPS using ‘VIS’;
Create public database link VIS connect to APPS identified by APPS using ‘VIS’;
Login to the DB as apps user:
Drop database link PLAN;
Drop database link VIS;
Create database link PLAN connect to APPS identified by APPS using ‘VIS’;
Create database link VIS connect to APPS identified by APPS using ‘VIS’;
How to find the particular Data Dictionary Table or Dynamic Performance View (V$)?
SELECT * FROM dictionary
WHERE table_name='DICTIONARY';
TABLE_NAME
------------------------------
COMMENTS
-----------------------------------------------
DICTIONARY
Description of data dictionary tables and views
To Stop Password set DB listener
LSNRCTL> set current_listener ABC
Current Listener is ABC
LSNRCTL> set password xxxxx
The command completed successfully
LSNRCTL> start ABC
Creating dictionary mangaed temporary tablespsce
create tablespace temp datafile '/d14/oracle/proddata/temp01.dbf' size 2048M temporary default storage (initial 5m next 5m pctincrease 0);
Enable unsuccessfull db sessions audit
audit_trial=true in parameter file
"Connect to sysdba
create spfile from pfile
=> start the database
=> NOAUDIT ALL ;
= AUDIT SESSION WHENEVER NOT SUCCESSFUL;
=> Run this script to verify that audit is enabled for failed logins only :
SQL> SELECT * FROM dba_stmt_audit_opts;
USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE
------------------------------ ------------------------------ ---------------------------------------- ---------- ----------
CREATE SESSION NOT SET BY ACCESS
Here is the list of few important tables.
Concurrent Manager
FND_CONCURRENT_QUEUES
FND_CONCURRENT_PROGRAMS
FND_CONCURRENT_REQUESTS
FND_CONCURRENT_PROCESSES
FND_CONCURRENT_QUEUE_SIZE
FND
FND_APPL_TOPS
FND_LOGINS
FND_USER
FND_DM_NODES
FND_TNS_ALIASES
FND_NODES
FND_RESPONSIBILITY
FND_DATABASES
FND_UNSUCCESSFUL_LOGINS
FND_LANGUAGES
FND_APPLICATION
FND_PROFILE_OPTION_VALUES
AD / Patches
AD_APPLIED_PATCHES
AD_PATCH_DRIVERS
AD_BUGS
AD_INSTALL_PROCESSES
AD_SESSIONS
AD_APPL_TOPS
To revert the password back
SQL> alter user abc identified by values '901D29B9D7CACF3D';
To know the value of any parameter listed in init.ora.
select name , value from v$parameter; or
show parameter dump;
To know database status
select status from v$instance;
To know database name
select name from v$database
Enabling hot backup
1.do the following changes in init file:
log_archive_start = true
log_archive_format = SID_%t_%s.arc (SID - instance name)
log_archive_dest = '/ux1/oraarch/SID’ REOPEN' (x - could be 1,2,3 etc; there's a space after SID)
2.shutdown immediate;
3.startup mount;
4.alter database archivelog;
5.alter database open ;
6.archive log list; (to check the changes are there)
7.alter system switch logfile;
8.check the log dir.Verify that log files are getting written there or not.
To take the DB out of archive mode
SQL>sqlplus "" / as sysdba""
SQL>archive log stop;
SQL>alter database noarchivelog;
4. Shutdown the database.
5. Start the database instance
sqlplus "" / as sysdba""
SQL>startup pfile=/d01/vis/sw/920/dbs/initvis.ora -- This init.ora file has been updated to start the database in noarchivelog mode
SQL>archive log list; -- This should show that automatic archival is disabled and that the database is running in noarchivelog mode"
If Compiling Invalids manually:
i) As sysdba run utlrcmp.sql from rdbms/admin
Then as sysdba run the follwoing
exec utl_recomp.recomp_parallel (8);(8 – no. Of workers)
ii) in case of Materialized Views:
TO COMPILE INVALID OBJECTS WE CAN USE THE FOLLOWING SYNTAX exec
dbms_mview.refresh ('VIEW NAME');
Syntax to refresh meterilaized view:
SQL> connect apps/xxx
Connected.
SQL> begin
2 dbms_snapshot.refresh('CCOR_NETRV_MV','c');
3 end;
4 /
dbms_refresh.refresh('"APPS"."FREIGHT_NOT_INVOICED_MV"');
INVALIDS IN SPECIFIC FORMAT
Invalid objects listing by name, type, owner; total count.
spool original_invalids.lst
col owner format a15
col object_name format a35
col object_type format a18
select count(*) from dba_objects where status = 'INVALID';
select object_name, object_type, owner from dba_objects where status = 'INVALID';
spool off;
Invalid objects listing by name, type, owner; total count.
spool original_invalids.lst
col owner format a15
col object_name format a35
col object_type format a18
select count(*) from dba_objects where status = 'INVALID';
select owner, object_name, object_type from dba_objects where status = 'INVALID' order by owner, object_name;
spool off;
Number of database objects by owner, type (order by owner, type). spool original_objects.lst
col owner format a15
col object_name format a35
col object_type format a18
select owner, object_type, count(object_type) from dba_objects group by owner, object_type order by owner, object_type;
spool off;"
select count(*) from dba_objects where status='INVALID';
SELECT owner, object_type, object_name
FROM dba_objects
WHERE status = 'INVALID'
AND object_name IN (SELECT referenced_name
FROM dba_dependencies
WHERE name ='WK_SNAPSHOT');
DBVERIFY for chechking database block corruption
dbv file=/u01/oradata/PROJDEV/invd02.dbf blocksize=8192 logfile=invd02_feb25.log feedback=10000
create a password file as follows:
$orapwd file=$ORACLE_HOME/dbs/orapwDB01
password=orapass(must be sys pwd) entries=5
set REMOTE_LOGIN_PASSWORDFILE to EXCLUSIVE.
to grant all privileges for all tables of a schema to another schema:
SQL> begin
for i in (select distinct table_name from all_tables where owner='MIM')
loop
execute immediate 'grant all on '||i.table_name||' to apps';
end loop;
end;
/
When starting listener for DB, you will find it asks for password.
Do the following :
lsnrctl
LSNRCTL>set current_listener PROD
LSNRCTL>set password
supply the pwd
LSNRCTL>stop or start as the case may be
LSNRCTL>exit
No comments:
Post a Comment