how to find database is running on PFILE or SPFILE

Below script will tells us if the database is running on PFILE or SPFILE

 SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type" FROM sys.v_$parameter WHERE name = 'spfile';

OR


SQL> show parameter spfile;
NAME TYPE VALUE
—— —— ————————————————
spfile string /u01/apps/oracle/10g/dbs/spfileorclkhan.ora


parameter will tell you the location where the SPFILE is present as well.

Comments

Post a Comment

Welcome to the Oracle DBA Blog.

Stay Hungry Stay Foolish

Popular posts from this blog

Oracle RAC Interview Question and Answers 10G and 11G/ 12c

Image
1. What is the major difference between 10g and 11g RAC? Well, there is not much difference between 10g and 11gR (1) RAC. But there is a significant difference in 11gR2. Prior to 11gR1(10g) RAC, the following were managed by Oracle CRS Databases Instances Applications Node Monitoring Event Services High Availability From 11gR2(onwards) its completed HA stack managing and providing the following resources as like the other cluster software like VCS etc. Databases Instances Applications Cluster Management Node Management Event Services High Availability Network Management (provides DNS/GNS/MDNSD services on behalf of other traditional services) and SCAN – Single Access Client Naming method, HAIP Storage Management (with help of ASM and other new ACFS filesystem) Time synchronization (rather depending upon traditional NTP) Removed OS dependent hang checker etc, manages with own additional monitor process 2.  What are Or...
Read more

how to Create Profile and Roles and What is the vlaue of DEFAULT in dba_profile?

Hi Team, Here is how you can deal with working on profile and creating role. A :---I have created role “ testinguserrole” on my test database; Step:-1 ----------------------------------- SQL> create role testinguserrole; GRANT CONNECT TO testinguserrole; GRANT SELECT ON ICP_P.CLAIM TO testinguserrole; GRANT SELECT ON ICP_P.CLAIM_LINE TO testinguserrole; GRANT SELECT ON ICP_P.CLAIM_EDIT TO testinguserrole; GRANT SELECT ON ICP_P.CLAIM_LINE_EDIT TO testinguserrole; GRANT SELECT ON ICP_P.CLAIM_LINE_MODIFIER TO testinguserrole; GRANT SELECT ON ICP_P.CLAIM_LINE_DIAGNOSIS TO testinguserrole; GRANT SELECT ON ICP_P.CLAIM_LINE_USER_DEFINED_FIELD TO testinguserrole; to verify the dba_tab_privs for testinguserrole select * from dba_tab_privs where grantee = 'testinguserrole'; Now i am creating user and will assign them the role. SQL> CREATE USER &username IDENTIFIED BY &username   2  DEFAULT TABLESPACE DATA1   3  TEMPORARY TABLESPACE TEMP   4  PROFILE DE...
Read more