* ERROR at line 1: ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only

As a DBA, you must have come across the below error while working on pluggable databases

 

ERROR at line 1:

ORA-01219: database or pluggable database not open: queries allowed on fixed

tables or views only

 Generally, this error gets reported whenever you try to access a view or a table in the pluggable database which is not in the open state, Here I was trying to check users in the database and was hit with this error, then I checked the status of my pluggable database and I found it was in mount state and not in the open state.

Then immediately I opened the database using the below command and then I was able to query the table, 

SQL> alter pluggable database open;

 

Pluggable database altered.

 

SQL> show pdbs

 

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         3 TECHNOPDB                      READ WRITE NO

SQL>  select count (*) from dba_users;

 

  COUNT(*)

----------

        40

SQL>

so wherever you see this type of error, try to check status of the database and if down, bring it up.

Read More

Snowflake- what is account name in snowflake login

Snowflake is a really hot topic in 2021 and 2022 due to its benefits over traditional database offerings, Here I will show you, to check the account name in the snowflake portal, for login to snowflake you can use the link https://app.snowflake.com/, this is how the login page looks like and the first thing it will ask to enter the account name 

Once you enter the account name, it will ask for your username and password, the username is the one you have entered during creating the demo account for snowflake

and once you enter your username and password, it will go to the main portal of snowflake, now click on the left bottom corner, you will see the below screen

Now just click on highlighted part, it will give you one URL, which looks like below 

https://i****3.ap-southeast-1.snowflakecomputing.com

 The highlighted part is your account name that can be used for login purposes

[Also read -Introduction and Architecture of Snowflake]

Read More

How to know if STATSPACK is installed

 

Statspack is important tool for dba to investigate performance issue in oracle database, it makes DBA's life easy in performance issues cases. Most of the time, statspack is not installed automatically unless it is specified in custom script prepared from database creation.

The question is how to check if statspack is installed or not? There are number of ways to detect that, we will discuss few here

 

1.  PERFSTAT user

statspack uses PERFSTAT user to perform its operation, so check in DBA_USERS whether user is present of not using select * from dba_users where USERNAME='PERFSTAT';

2. Presence of table STATS$DATABASE_INSTANCE

statspack generates snap id's and save information in table STATS$DATABASE_INSTANCE, and table is created during installation of statspack, check using desc STATS$DATABASE_INSTANCE if table is present, if present then statspack is installed otherwise not

3. Running report using spreport.sql

For DBA's convenience, there are number of scripts provided by oracle in admin folder under oracle home directory, spreport.sql is such script used to generate statspack report , try running this sql file, if it asks for begin snap id and end snap id then assume statspack is installed otherwise it will throw error.

 

 

Read More