Want to Convert number to text/word form? Here is the solution

Oracle Database has great functionality that converts any number to its word format, suppose number "2 "you want to write it as "two", you can do it using Oracle database. you may not need it for single double, triple, or quadruple numbers but what if your number is big and it makes you lazy to read it or convert it into Word format, Here is the solution.

Make use of a dummy table dual along with a few other functions such as to_char and to_date, Here is the exact query to convert your number 3434590 into word or text format

SQL> select to_char(to_date(3434590,'j'),'jsp') from dual;

TO_CHAR(TO_DATE(3434590,'J'),'JSP')

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

three million four hundred thirty-four thousand five hundred ninety

Where J stands for Jubilian date and which starts from 1 to 5373484 and JSP stands for Julian dates SPelled(SP)

as there is no direct function in oracle to convert number to words, you can try this method. Here is another simple example

Read More

Flashback query in oracle

You must have heard about the time machine in movies or magazines, just think of an oracle providing the same feature with its databases, and that is called a flashback. 

We can go back in time to query data from the table, of course, there are certain limits to it, but it’s possible, we will learn this with an example. 

Here is the table employee, with 8 rows 

 

SQL> select count(*) from employee;

 

  COUNT(*)

----------

         8

and this particular snapshot is captured at the below timings 

 

SQL> select sysdate from dual;

 

SYSDATE

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

2022-07-21 23:37:12

 

Now, deleted a couple of rows using the below query

 

SQL> delete from employee where emp_id>305;

 

2 rows deleted.

 

SQL> commit;

 

Commit complete.

 

now we can see number of rows in the table 

 

SQL> select count(*) from employee;

 

  COUNT(*)

----------

         6

 

so basically, 2 rows are deleted from the table, what if you want to read those 2 deleted rows, you have the option to use the flashback query option, below is the exact format to achieve it

Check the number of rows based on timestamp, as we saw we had 8  rows preset in the table at 23:37 but later we deleted them, based on this timing, check the number of rows

 

SQL> select count(*) from employee as of timestamp TO_DATE('2022-07-21 23:37:12','YYYY-MM-DD HH24:MI:SS');

 

  COUNT(*)

----------

         8

 

see here, we were able to fetch data based on the flashback option, this is just reading, what if you want to insert data back to the table, use the below commands

 

SQL> insert into employee select * from employee as of timestamp TO_DATE('2022-07-21 23:37:12','YYYY-MM-DD HH24:MI:SS') where emp_id>305;

 

2 rows were created.

 

SQL> commit;

 

Commit complete.

 

now check the count in the table, you will be surprised to see, that data is inserted back to the table.

 

SQL> select count(*) from employee;

 

  COUNT(*)

----------

         8

 

Read More

pfile and spfile in oracle database

 In this short article, we will discuss the server parameter file and initialization parameter file i.e., spfile and pfile

 

pfile - parameter file, is a text-based file, readable and editable with any text editor

spfile- server parameter file, binary file, readable with only some utility and can not be edited.

 

pfile is called the initialization parameter file because this is the first file used to start (initialize) the database, spfile is created later on. 

The default extension of these files is ".ora", in an ASM file name can be with any random or fixed number i.e. spfiletechnodb.1223.343543 (technodb is database name)

The following parameters can be seen in the parameter files

 

• memory parameters such as memory target, sga, pga, shared_pool, java pool, large pool
• database and instance name (can be different)
• parameters related to different physical files such as control file, datafiles, archive files
• processes and session details
• trace file location, audit settings, and many more

 

the very first stage of the database startup is "nomount" and that’s where this parameter file will get read, it will load database memory and process settings and takes the location of control files.

 

The default location of the parameter file is based on the operating system you are using

in Linux/Unix it is located at $ORACLE_HOME/dbs location and for windows operation system it is %ORACLE_HOME%\database

 

Parameter change

Assume your database is running on pfile and you want to change the parameter of the database, then you need to perform the below steps

 

• shutdown the database
• add/change the parameter in pfile
• start the database

 

Now assume your database is running on spfile- then based on condition, you may not at all need to restart the database

• change the parameter using alter system command
• Restart the database if required

 

Read More

Rename pluggable database

There might be a situation when you created a pluggable database with the wrong name then it's not time to worry, you can simply rename it using rename command, in another situation where the client requested to change the name of the pluggable database, then rename command will to amazing work. Here we will discuss renaming oracle PDB

 

Below are the pluggable database present in my system I want to rename technopdb3 to technopdb2 

Below are steps to perform it

The first step is to close the pluggable database 

 

SQL> alter pluggable database technopdb3 close immediate;

 

Pluggable database altered.

 

SQL> show pdbs

 

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 TECHNOPDB                      READ WRITE NO

         4 TECHNOPDB3                     MOUNTED

         5 TECHNOPDB_NEW                  READ WRITE NO

SQL>

 

pluggable database technopdb3 has been closed, now open same pluggable database in restricted mode 

 

SQL> alter pluggable database technopdb3 open restricted;

 

Pluggable database altered.

 

SQL> show pdbs

 

    CON_ID CON_NAME                      OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 TECHNOPDB                      READ WRITE NO

         4 TECHNOPDB3                     READ WRITE YES

         5 TECHNOPDB_NEW                  READ WRITE NO

 

now connect to pluggable database using alter session command and execute rename command as mentioned below 

 

SQL> alter session set container=technopdb3;

 

Session altered.

 

SQL> alter pluggable database technopdb3 rename global_name to technopdb2;

 

Pluggable database altered.

 

 

Now close the pluggable database and open in normal mode

 

SQL> alter pluggable database close;

Pluggable database altered.

SQL> alter pluggable database open;

Pluggable database altered.

SQL> show pdbs

 

    CON_ID CON_NAME                      OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 TECHNOPDB                      READ WRITE NO

         4 TECHNOPDB2                     READ WRITE NO

         5 TECHNOPDB_NEW                  READ WRITE NO

SQL>

 

In this way, technopdb3 has been renamed to technopdb2

Read More

All about oracle database auditing

As your application and database grow, more and more users get connected to the database, and it becomes difficult to manage all the privileges that individual user has granted. So, there is a feature provided by the oracle called AUDITING.

Users with DBA privileges can do a lot of things with the database, it is important to make sure that users with DBA privileges should not harm the database by any means, in order to do it, the security team or database admin can enable different levels of auditing.

Before enabling any auditing, you need to consider the fact that, this is put some extra effort into the database, so try to push it on a minimal basis.

 

Auditing SYSDBA activity

Based on the parameter AUDIT_SYS_OPERATIONS value, SYSDBA activity is tracked at the operating system level audit trail file. If AUDIT_SYS_OPERATIONS is set to TRUE then every statement executed by the user connected as “as sysdba” or “as sysopr”  is audited. Location of audit trail file can be set and monitored by parameter AUDIT_FILE_DEST

 

Database Auditing

Database auditing is controlled using the parameter AUDIT_TRAIL parameter, there are different values associated with it.

 

NONE (FALSE) – database auditing is disabled

OS – auditing will be recorded at the OS level audit trail and location is controlled by audit_file_dest

DB- auditing will be recorded at database table SYS.AUD$

DB_EXTENDED – saves at database level but includes the SQL statements with bind variables

XML- auditing is done at OS level, formatted with XML tag

XML_EXTENDED- formatted as XML tags, includes SQL statements with bind variables

 

Database auditing can be configured by AUDIT commands,

For example

 SQL> audit update any table; 

Audit succeeded. 

SQL> audit select any table by session; 

Audit succeeded. 

SQL>

 Assume, that few users have “update any table” privilege granted, this can be used to harm the database as well apart from regular work.  In order to record what tables are being updated, you can simply turn on auditing for the same.

By default, auditing will generate one row for each auditing violation.

BY SESSION- one record for each session does not matter how many times it violates (DEFAULT)

BY ACCESS- one record for every violation.

 

Auditing can be enabled on specific objects as well, for example

SQL> audit select on SYS.DBA_USERS whenever successful; 

Audit succeeded. 

SQL> 

This statement generates a record for every successful insertion for the table SYS.DBA_USERS

WHENEVER SUCCESSFUL – records only when the insertion is successful

WHENEVER NOT SUCCESSFUL – records only when the insertion is failed

By default- both conditions are recorded.

 

When AUDIT_TRAIL is set as OS or XML, one can check audit records at OS level audit trail file and when AUDIT_TRAIL is set as DB or a similar one, you can fetch records using SYS.AUD$ or you can use the DBA_AUDIT_TRAIL view. There are around 50 columns available to view in the DBA_AUDIT_TRAIL view.

There are a couple of subset views of DBA_AUDIT_TRAIL and can  be used to narrow down the results

DBA_AUDIT_OBJECT,

DBA_AUDIT_STATEMENT

DBA_AUDIT_SESSION

 

Auditing WITH triggers

Auditing enabled using the audit command will only have a single record for each statement, but it will not have the exact statement used to insert the record. Sometimes you may need to watch out for statements executed,

Database triggers will help to capture the statement based on the condition defined. Assume, there is an update trigger defined on the table, you try to update the table, it will simply generate an audit record and put the row in another table defined in trigger.

 

Fine-Grained Auditing (FGA)

Till now, we have discussed auditing at database level or table level, what if you want to capture auditing for only specific rows in table or views, FGA can help you to achieve this.

FGA can be configured through package DBMS_FGA and add FGA audit policy, need to use ADD_POLICY procedure.

To view records, you need to use the DBA_FGA_AUDIT_TRAIL view, generally FGA auditing can be enabled on rows on which data is critical such as salary or budget or revenue.

DBMS_FGA has a lot of procedures and can be used to add, drop, enable policy, disable policy.

 

SQL> desc dbms_FGA

PROCEDURE ADD_POLICY

PROCEDURE DISABLE_POLICY

PROCEDURE DROP_POLICY

PROCEDURE ENABLE_POLICY 

This is all about the database auditing theory part.

Read More

ORA-29283: invalid file operation: unexpected "LFI" error (1509)[29437]

I was trying to export the schema in my windows PC, it got stuck with below error 

 

C:\Users\shaik\Videos\technodba exp>expdp userid=techno_user@technopdb directory=export_dir dumpfile=expdp_techno_users.dmp logfile=expdp_techno_users.log 

 

Export: Release 19.0.0.0.0 - Production on Wed Jun 1 16:13:15 2022

Version 19.3.0.0.0

 

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Password:

 

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

ORA-39002: invalid operation

ORA-39070: Unable to open the log file.

ORA-29283: invalid file operation: unexpected "LFI" error (1509)[29437]

 

On further investigation found that, the directory I had created and pointed out does not have write permission to it, Though I was part of admin group for my PC, it was not allowing oracle to write in C drive.

once I changed directory location, I was able to perform export. 

 

C:\Users\shaik\Videos\technodba exp>expdp userid=techno_user@technopdb directory=export_dir dumpfile=expdp_techno_users.dmp logfile=expdp_techno_users.log

 

Export: Release 19.0.0.0.0 - Production on Wed Jun 1 20:54:31 2022

Version 19.3.0.0.0

 

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Password:

 

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Starting "TECHNO_USER"."SYS_EXPORT_SCHEMA_01":  userid=techno_user/********@technopdb directory=export_dir dumpfile=expdp_techno_users.dmp logfile=expdp_techno_users.log

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/STATISTICS/MARKER

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/TABLE/AUDIT_OBJ

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

. . exported "TECHNO_USER"."DEPTS"                       393.4 KB   12002 rows

. . exported "TECHNO_USER"."DUMMY"                       5.085 KB       3 rows

. . exported "TECHNO_USER"."DUMMY1"                      5.085 KB       2 rows

. . exported "TECHNO_USER"."EMPLOYEES"                   393.4 KB   12002 rows

. . exported "TECHNO_USER"."PERSONS"                     425.6 KB   13002 rows

Master table "TECHNO_USER"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for TECHNO_USER.SYS_EXPORT_SCHEMA_01 is:

  C:\DOWNLOADS\ARCH\EXPDP_TECHNO_USERS.DMP

Job "TECHNO_USER"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Jun 1 20:54:56 2022 elapsed 0 00:00:20

 

 

C:\Users\shaik\Videos\technodba exp>

Read More

ORA-38706: Cannot turn on FLASHBACK DATABASE logging. ORA-38707: Media recovery is not enabled.

I was trying to turn on flashback for the oracle database and it was failed with below error 

 

SQL> alter database flashback on;

alter database flashback on

*

ERROR at line 1:

ORA-38706: Cannot turn on FLASHBACK DATABASE logging.

ORA-38707: Media recovery is not enabled.

 

On further checking I found, database was in noarchivelog mode, this is how we can check archive log mode.

 

SQL> archive log list

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            C:\Downloads\arch

Oldest online log sequence     137

Current log sequence           139

SQL>

 

In order to bring it in archive log mode, I followed steps mentioned here convert database to archive log mode  , once your database is in archive log mode , you can simple turn on flashback without any issues.

Follow below commands to turn on flashback for the database.

 

SQL> archive log list

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            C:\Downloads\arch

Oldest online log sequence     137

Current log sequence           139

SQL>

 

SQL> alter database flashback on;

 

Database altered.

 

SQL> select database_name, flashback_on from v$database;

 

DATABASE_NAME        FLASHBACK_ON

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

TECHNODB             YES

 

 

 

Read More

ORA-16024: parameter LOG_ARCHIVE_DEST_1 cannot be parsed

While working on database activities, we come across different error, below one of the errors reported while updating parameter LOG_ARCHIVE_DEST_1 in oracle database.

 

SQL> alter system set log_archive_dest_1='C:\Downloads\arch' scope=both;

alter system set log_archive_dest_1='C:\Downloads\arch' scope=both

*

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-16024: parameter LOG_ARCHIVE_DEST_1 cannot be parsed

 

This is purely due to syntax error, as LOCATION keyword is required before path, so correct syntax will be as below 

alter system set log_archive_dest_1='location=C:\Downloads\arch\';

so I was able to update parameter without any issue

 

SQL> alter system set log_archive_dest_1='location=C:\Downloads\arch\';

 

System altered.

Also read: Statspack installation check

Also read : switching between archive log and noarchivelog

Read More