Database not nomounting: ORA-00848: STATISTICS_LEVEL cannot be set to BASIC with SGA_TARGET or MEMORY_TARGET

I have Oracle database with ASM. Database is started by spfile, that is located on ASM. I have pfile that indicates to spfile.

Let’s start from the beginning. I run the following command:

alter system set STATISTICS_LEVEL=basic scope=spfile;

Restarted the database and tried to startup. Got the following error:

ORA-00848 STATISTICS_LEVEL cannot be set to BASIC with SGA_TARGET or MEMORY_TARGET

And because of this database is not nomounting.

image

My parameter file content is:

–/u01/app/oracle/product/11.2.0/db_1/dbs/initdevdb1.ora

SPFILE=’+DATA/devdb/spfiledevdb.ora’;

So let’s solve it. My aim is to set STATISTICS_LEVEL parameter to TYPICAL and start the database normally.

1. Create pfile from spfile.

image

2. Change parameter file initdevdb1.ora(which was created form spfile)  and set statistics_level parameter to TYPICAL.

vi  /u01/app/oracle/initdevdb1.ora

image

3. Nomount database with newly created pfile.

image

4. Delete old spfile using ASMCMD.

image

5.  create spfile from pfile.

image

Note: Default location for pfile is $ORACLE_HOME/dbs. STARTUP command reads pfile from default location, that in our case refers to spfile located on ASM.

That’s it!

Advertisements

External table with ORACLE_LOADER

Today, I will discuss how to create external table and show you the primary usage of it. 

Data in external table is saved outside of the database. To easily understand what does it mean, let’s discuss the following example:
user1 is the user, who is connected to the ORCL database and should see mytable content.
user2 is the user, who modifies data.csv file using Excel and places it in the directory accessible for oracle database.

Pic 1.

image

This approach may be achieved by ORACLE_LOADER or ORACLE_DATA.

In my example , I will use ORACLE_LOADER.

1. Create csv document and fill it with some data:

image

2. Change data.csv  with data.txt. So change csv extension to txt.

This will help you to determine by which character is data separated.

image

As you can see records are separated by newline and delimiter is comma (,)

So let’s write a script:

3.

/*Create necessary directories 

  Bad file will be saved in bad_dir. As you know bad file is for saving data, 
that was not loaded into the database because of some error.

*/

 

create directory data_dir as 'D:\data';

create directory bad_dir as 'D:\data\bad';

create directory log_dir as 'D:\log';

 

/*Create table owner*/

create user a identified by a default tablespace USERS;

/*Grant necessary privileges*/

grant create session to a;

grant read on directory data_dir to a;

grant write on directory bad_dir to a;

grant write on directory log_dir to a;

grant create table to a;

/*Connect as “a” user and create external table*/

Create table data_table(

  Name varchar2(30),

  Surname varchar2(60),

  quantity number

) 

organization external(

   type ORACLE_LOADER

   default directory data_dir

   access parameters(

     records delimited by newline

     badfile bad_dir:'data.bad'

     logfile log_dir:'data.log'

     fields terminated by ','

     missing field values are null

     (name,surname,quantity)

   )

  location('data.txt')

)

parallel

reject limit unlimited;

 

I think external table options are self-explanatory….

Let’s check table content:

SQL> select * from data_table;

 

NAME       SURNAME     QUANTITY

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

Mariam     Kupatadze   1

Giorgi     Beridze     1

Change the content of data.txt file to the following

/*data.txt*/

Giorgi,Beridze,1

Mariam,Kupatadze,2

and re-run the query

SQL> select * from data_table;
 
NAME       SURNAME     QUANTITY
---------- ----------- ----------
Mariam     Kupatadze   2
Giorgi     Beridze     1

as you can see changes were reflected.

Note, external table is read only. Let’s try updating it:

update  data_table set surname='Kupa' where surname='Kupatadze';

You will get an error

image

Now let’s change the content of data.txt to the following

/*data.txt*/

Giorgi,Beridze,1

Mariam,Kupatadze,2

mmmmmm,mmmmmmm,mmm

and re-run the query

SQL> select * from data_table; 

 

NAME       SURNAME     QUANTITY

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

Mariam     Kupatadze   2

Giorgi     Beridze     1 

 

There is no changes, why  ?

Now let’s see the log file and then bad file:

At the end of the log file you will notice the following error:

error processing column QUANTITY in row 3 for datafile D:\data\data.csv

ORA-01722: invalid number

In the bad file, there is:

mmm,mmm,mmm

Because of quantity column type was number, string “mmm” was not inserted in it. 
So log file showed us the error “invalid number” and bad file saved data that was causing an error.

I hope it was helpful for you. Good Luck!

GRANT ANY OBJECT PRIVILEGE

Real world scenario:

I have users: A_DBA, B_JUNIOR, C_MAIN, D_USERS.

I want my junior, user B_JUNIOR,  to grant execute privilege on C_MAIN.testProc to D_USERS, without having him execute privilege on C_MAIN.testProc.

So I want my junior to grant(JUST grant) object privilege on one schema to another schema .

Solution:

GRANT ANY OBJECT PRIVILEGE  is the privilege which solves it.

SQL> connect A_DBA/ta@MYDB

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 

Connected as A_DBA

SQL> grant GRANT ANY OBJECT PRIVILEGE to B_JUNIOR.

Grant succeeded

SQL> connect B_JUNIOR/a@MYDB

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 

Connected as B_JUNIOR 

SQL> grant execute on C_MAIN.testProc to D_USERS

Grant succeeded

————————————BUT, junior can’t execute itself

SQL> exec C_MAIN.testProc;

begin C_MAIN.testProc; end;

ORA-06550: line 2, column 7:

PLS-00201: identifier 'C_MAIN.TESTPROC' must be declared

ORA-06550: line 2, column 7:

PL/SQL: Statement ignored