HOW TO CHECK NLS SETTINGS ON DATABASE, INSTANCE AND SESSION LEVEL
REQUEST I want to find all NLS settings on the database, the instance and the current session level. QUERY SET PAGES 999 SET LINES 300 COL PARAMETER FOR A30 COL DATABASE FOR A30 COL INSTANCE FOR A30 COL SESSION FOR A30 SELECT DB.PARAMETER,…
NETWORKER CLIENT DOES NOT START ON A CLONED VM
PROBLEM Networker does not start after VM cloning if a cloned host with a different hostname. SYMPTOMPS Networker works fine on original VM and does not start on cloned one. Original : original.net Cloned : cloned.net root@original.net: service networker start root@original.net: ps -fe…
GETTING ALL CHILD OBJECTS WITHIN AN OBJECT
QUESTIONS How to fix “PL/SQL: ORA-00942: table or view does not exist” during compilation? How to find out all objects within another object? How to find out all table references in a specific function or procedure? How to find out in what database object…
FINDING OUT WHAT AN ORACLE PROCESS IS DOING
QUESTIONS How to match OS processes to Oracle processes? What a particular OS Oracle process (PID) is doing in an Oracle database? What is an easy way to match the OS PID to the Oracle session? How to find out SQL being executed by…
QUERYING A SERVER’S IP ADDRESS
REQUEST I want to gather a hostname and/or IP address of the current host or a remote one. SOLUTION A hostname and IP address can be retrieved through UTL_INADDR package LOCAL HOST oracle@dbpilot.net: nslookup dbpilot.net Server: 10.1.1.50 Address: 10.1.1.50#53 Name: dbpilot.net Address:…
QUERYING THE ORACLE_HOME PATH FROM A DATABASE
REQUEST I want to get the ORACLE_HOME path by querying the database. RELATED GETTING THE VALUE OF AN ENVIRONMENT VARIABLE THROGHT PL/SQL BLOCK SOLUTION DBMS_SYSTEM COL ORACLE_HOME FOR A30 VARIABLE ORACLE_HOME VARCHAR2(100); EXEC DBMS_SYSTEM.GET_ENV(‘ORACLE_HOME’,:ORACLE_HOME); PRINT ORACLE_HOME ORACLE_HOME —————————— /oracle/product/12.2.0.1 DBA_LIBRARIES…
GETTING THE VALUE OF AN ENVIRONMENT VARIABLE THROGHT PL/SQL BLOCK
REQUEST I want to get a value of the specific environment variable by querying a database. SOLUTION First, define a variable oracle@dbpilot.net: export MYVAR=”My Variable” oracle@dbpilot.net: echo $MYVAR My Variable A value of any variable can be retrieved through DBMS_SYSTEM.GET_ENV procedure COL OS_VAR…
SPOOLING TO A DYNAMICALLY GENERATED SPOOL FILE NAME
QUESTIONS How to create a spool file with the date appended to file name? Is it possible to change the spool file name dynamically? REQUEST I want to produce a spool file with a name generated during runtime. SOLUTION Create a substitution…
GENERATING THE CURRENT INITIALIZATION PARAMETER FILE
REQUEST To generate an initialization parameter file by querying a database. RELATED IS MY DATABASE USING PFILE OR SPFILE? DETAILS V$PARAMETER displays information about the initialization parameters that are currently in effect for the session. V$SPPARAMETER displays information about the contents of…
IS MY DATABASE USING PFILE OR SPFILE?
QUESTIONS How to check whether an Oracle instance is started using pfile or spfile? REQUEST To get a full path of an initialization parameter by querying of a database. RELATED QUERYING THE ORACLE_HOME PATH FROM A DATABASE SOLUTION SET LINES 300…
Categories
- Linux (7)
- Networker (1)
- Oracle (41)
- Account (2)
- AWR (1)
- Database Errors (2)
- Datapump (1)
- Enterprise Manager (10)
- Environments (4)
- Interview (1)
- NLS (1)
- Objects (1)
- Objects DDL (2)
- PL/SQL (4)
- UTL_FILE (4)
- Prerequisites (1)
- Redologs (1)
- Segment Shrink (1)
- Session (3)
- Spfile (3)
- SQL PLUS (1)
- Spooling (1)
- Statistics (2)