* 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

Different files involved in Oracle database

As Oracle DBA, we all know Oracle database server is combination of physical files, processes and memory, in this article we are going to discuss about different files involved in oracle database architecture

There are 3 most important files

·       Data files

·       Control files

·       Redo logs file

There are other supplementary files as well - parameter files, password files, archive log files

 

Let’s discuss some more details 

Datafiles 

Data files contains actual data of the database, data files are formatted as per block size designed during database setup.

There are few types of datafiles

 

1.    SYSTEM - this includes system datafile and is critical to database, it includes dictionary of the database

2.    User datafile - this includes application specific datafiles and contains application data and can be customized as per requirement

3.    UNDO datafile - It holds old data while performing activity in database like select, insert, update, delete, this old data can be used to revert the changes in case of recovery

4.    TEMP datafile - In case user orders data based on select query, it needs space to sort the data before returning back to user, first sorting operation is performed in memory and if memory is not sufficient then it moves to temp tablespace.

 

Control Files

Control file contains structure of the database like location of all datafiles, redo logfiles.

This is Critical file to operation of database, and it is one of first file read by instance at startup.

 

Redo logfile

This file contains changes made to the database and can be used for recovery of processes. 

copies of redo logfile are called as archived log files.

 

Other files in oracle database 

Password file - this file authenticates the use who is capable to startup the database.

parameter files- 

    This file contains list of parameters used by instance.  typically, there are two types of parameter files

PFILE - text based parameter file, change to parameter needs to be modified/added manually in file and restart is required to get it into effect. 

SPFILE - binary parameter file, change to the parameter can be carried out on the go. 

 

Read More

SQL Functions - LENGTH, ASCII, SUBSTR

In this article we will discuss few SQL functions useful while working daily. SQL functions are kind of predefined packages/procedure, can be used in different aspects, lets discuss one by one

LENGTH

Syntax - LENGTH(String)

This function returns the total number of characters present in the string

Example

SQL>  SELECT LENGTH('Interest') from dual;

 

LENGTH('INTEREST')

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

                 8

 

ASCII

Syntax – ASCII(char)

This function gives ASCII value of given character

Example

SQL>  SELECT ASCII('B') from dual;

 

ASCII('B')

----------

        66

 

SQL>  SELECT ASCII('I') from dual;

 

ASCII('I')

----------

        73

 

SUBSTR

Syntax - SUBSTR (string, position, sub-string_length)

This substring function return a portion of string, starting at character position, sub-string_length characters long.

SUBSTR calculates length using characters as defined by the input character set

SQL> select SUBSTR('entertainment',4,10) from dual;

 

SUBSTR('EN

----------

ertainment

 

SQL>

Read More

ORA-01950: no privileges on tablespace 'USERS'

 In this article, we will try to resolve error ORA-01950: no privileges on tablespace 'USERS' received during insert command,

I was able to create table under schema TECHNO_USER, but I was not able to insert data into it (as shown below)

 SQL> insert into techno_user.dummy values (10);

insert into techno_user.dummy values (10)

                        *

ERROR at line 1:

ORA-01950: no privileges on tablespace 'USERS'

Then I checked default tablespace and quota on default tablespace or any other tablespace 

SQL> select username, default_tablespace from dba_users where username='TECHNO_USER';

 

USERNAME                       DEFAULT_TABLESPACE

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

TECHNO_USER                    USERS

 

SQL> select distinct tablespace_name, USERNAME from dba_ts_quotas where USERNAME='TECHNO_USER';

 

no rows selected

 

SQL>

 

As per output of above commands, we can say this user does not have quota on any tablespace, insert/update command will require quota on tablespace, so I have granted quota on tablespace USERS with below command 

SQL> alter user TECHNO_USER quota unlimited on USERS;

 

User altered.

 

SQL> select distinct tablespace_name, USERNAME from dba_ts_quotas where USERNAME='TECHNO_USER';

 

TABLESPACE_NAME                USERNAME

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

USERS                          TECHNO_USER

 

Also read changing default tablespace

 

After doing this, I was able to resolve error and was able to insert data in the table

  

SQL> insert into techno_user.dummy values (10);

 

1 row created.

 

SQL> insert into techno_user.dummy values (20);

 

1 row created.

 

SQL>

Read More

Changing default tablespace for user in oracle

In my oracle database, one of the users was having its default tablespace as SYSAUX, and having default tablespace as SYSAUX is not a good idea in the oracle database, here we are going to discuss changing the default tablespace for oracle user

Let’s see how to check default tablespace using dba_users

SQL> select username, default_tablespace from dba_users where username='TECHNO_USER';

 

USERNAME             DEFAULT_TABLESPACE

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

TECHNO_USER          SYSAUX

 

Here we can see, default tablespace is SYSAUX, now change it USERS tablespace

SQL> alter user TECHNO_USER default tablespace USERS;

 

User altered.

 

verify the result

SQL> select username, default_tablespace from dba_users where username='TECHNO_USER';

 

USERNAME             DEFAULT_TABLESPACE

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

TECHNO_USER          USERS

 

In this we can simply alter default tablespace for any user.

Read More

Lock and Unlock table stats in oracle database

There are unlimited features provided by oracle with an upgraded version, one of the feature we are going to discuss is stats lock and unlock in the oracle database,

Assume the application team has set up stats gather job from application end but that causing issue to the database like CPU/Memory utilization getting high during the job execution, being a DBA you can ask them to stop/disable the job but in case if it’s not feasible then you can lock stats on tables and unlock whenever required.

Another scenario- you have locked a good execution plan for ETL/Job run and that should not change with stats gathered, then you can lock the stats for the table.

Below we will discuss how to do this exactly, I have a table called LOCATIONS under user TECHNO, I do not want to gather stats on the same, in the below query, if stats are locked, the output will be ALL if not then it will be blank, you can stats are not locked

SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'LOCATIONS' and owner = 'TECHNO';

 

STATT

-----

 

 

SQL>

Now I will lock stats and execute the query again

SQL> exec dbms_stats.lock_table_stats('TECHNO','LOCATIONS');

 

PL/SQL procedure successfully completed.

 

SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'LOCATIONS' and owner = 'TECHNO';

 

STATT

-----

ALL

 

SQL>

Now I will try to gather stats on the same table, it should throw error 

SQL> exec dbms_stats.gather_table_stats(ownname=> 'TECHNO', tabname=>'LOCATIONS');

BEGIN dbms_stats.gather_table_stats(ownname=> 'TECHNO', tabname=>'LOCATIONS'); END;

 

*

ERROR at line 1:

ORA-20005: object statistics are locked (stattype = ALL)

ORA-06512: at "SYS.DBMS_STATS", line 40751

 [Also Read - All About Database links]

Now we will see how to unlock stats and gather stats

SQL> exec dbms_stats.unlock_table_stats('TECHNO','LOCATIONS');

 

PL/SQL procedure successfully completed.

 

SQL> exec dbms_stats.gather_table_stats(ownname=> 'TECHNO', tabname=>'LOCATIONS');

 

PL/SQL procedure successfully completed.

 

SQL>

Read More

Create local user in oracle pluggable database

In this short article, I will give a brief idea about how to create a local user in a pluggable database and how to check its status as well.

Basically, with this multitenant architecture concept, there are two types of users common user and local user, common users are shared across multiple pluggable databases while local user is specific to a particular database, here we will discuss how to create a local user

SQL> alter session set container=technopdb; 

Session altered.

 

SQL> create user techno_user identified by test123;

 

User created.

 

SQL> grant create session to techno_user;

 

Grant succeeded.

 

SQL> show pdbs

 

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         3 TECHNOPDB                      READ WRITE NO

SQL>

SQL> select username, con_id from cdb_users where username='TECHNO_USER';

 

USERNAME                      CON_ID

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

TECHNO_USER                        3

 

SQL> select d.username, c.con_id,p.name from dba_users d, cdb_users c , v$pdbs p where d.username='TECHNO_USER' and d.username=c.username and p.con_id=c.con_id;

 

USERNAME                      CON_ID NAME

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

TECHNO_USER                        3 TECHNOPDB

 

SQL>

Here we can check, how to connect to pluggable database using  local user

if you are trying it from your local machine/desktop then below command will work fine, you just need to have ip address and port number and if you are using tnsnames then mention the in the connection string

C:\Users\****>sqlplus techno_user/test123@192.168.0.107:1521/technopdb

 

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 15 16:29:48 2021

Version 19.3.0.0.0

 

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

 

SQL>

SQL> select * from global_name;

 

GLOBAL_NAME

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

TECHNOPDB

 

SQL> show user

USER is "TECHNO_USER"

SQL>

Read More