Oracle Script to Create or Drop Series of Users or Schemas

Sometimes DBAs are asked to create series of users of same pattern like xyz01 to xyNN. In such situations it is very difficult in case we are approaching with manual user creation. You can use below enclosed script for this purpose.

Script to create series of users

Find out New or Updated Rows from an Oracle Table without having Date Column

It is a common question to the DBA from the developer that is there any way to find out the rows inserted or modified recently without having the date column in the table. If you have date column inside the table it is easy job to find out the data. If you have flashback feature enabled in your database here is the way to find out. This will work out up to certain extend.

See an example below

Method to Create New Column in Oracle Table with Combination Other Columns

It is not a regular requirement. But sometimes we need it such cases. This can be achieved by the virtual columns in the Oracle.

See some examples below.

SQL>alter table emp add name_job as (ename||'-'||job);
Table altered.

Impdp Expdp Failed with ORA-31626, ORA-31633, ORA-06512, ORA-01031

Error description

Oracle data pump expdp / impdp failed with following errors.

Import: Release - Production on Fri Jun 20 07:42:33 2014

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

Username: scott

Connected to: Oracle Database 11g Release - 64bit Production
ORA-31626: job does not exist

Use of Expdp Impdp nologfile=y

Oracle data pump has one option called nologfile and by default its value is N. The purpose of this option is that if you specify the value for this parameter as Y then it will not create the log file for the export or import activity. This is very useful option when you do an import where the oracle user doesn’t have write privilege on the directory where the dump file exists. In such cases the impdp will throw the following error.

ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation

Method to Track Oracle Schema User Password and Modification History; Find out Current, New Password

This is the solution to find out the Oracle user current password by recording the password history. This would be helpful to find out who changed the password for a user, when he changed, for which user with new password. From the recorded history you can find out the previous/old as well as current password for the Oracle user. This is achieved by oracle autonomous_transaction and profile PASSWORD_VERIFY_FUNCTION.

Here are the steps:

Sql Query to Find and Reclaim the Fragmented Space in Oracle Table

How the table hits by space fragmentation?

If a table has large number of records and the particular table is getting updated or the rows getting deleted periodically then there will be unused blank spaces (holes) in the table segments. These blank spaces will get created by the row deletions which will not be used without reorg or reclaiming. Over a period of time the volume of the unused space will get accumulated to a huge size. These unused spaces are called fragmentations. The oracle will not automatically release this space into usable free space whereas we have to perform the reorg activity to claim the fragmented space.


dba topics. Copyright 2011-14 All Rights Reserved | Site Map | Contact | Disclaimer | Google