ASM Disk Info

Create Or Replace Force View Sys.Av_asm_disk (Name,
                                              Path,
                                              Header_status,
                                              Free_mb,

How can I tell if a procedure/package is running?

Sometimes the installation of a new version of a database package "hangs" and eventually times out with a ORA-04021: timeout occurred while waiting to lock object". This is caused by another session that is currently executing the same package.

Listing privileges recursively for Oracle users

This is a script that shows the hierarchical relationship between system privileges, roles and users.

FRM-92100 when running a long running report

Users were experiencing the FRM-92100 with IllegalArgumentException while running a long running report, after investigation it was found that Request Timeout (seconds) value was 600 only on OHS.  So changing to 1500 resolved the issue.

RMAN Restore (Netbackup)

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;

RMAN Backup using Netbackup

After Netbackup configuration on the Oracle Server, you can use rman to take backup on tape.

How To Change ASM SYS PASSWORD ?

Things tried:
SQL> password
Changing password for SYS
Old password:
New password:
Retype new password:
ERROR:
ORA-00600: internal error code, arguments: [15051], [], [], [], [], [], [], []

Moving table(s) to a different tablespace

Q: What happens when u move a table to a different Tablespace?
Q: How can u move table to a different Tablespace which have long datatype?
Q: How can u move table to a different Tablespace with indexes?

ORA-02449 during tablespace drop

SQL> drop tablespace users including contents and datafiles ;
drop tablespace users including contents and datafiles
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

Using Data Pump

Oracle Data Pump technology enables very high-speed movement of data and metadata from one database to another. The Data Pump is a server based utility vs. the traditional Export / Import which are client based utilities. The Oracle Data Pump is not compatible with the Export / Import functionality. The Oracle Data Pump can also use NETWORK_LINK functionality to move data and metadata from a remote database without a dumpfile using the network technology between the two databases.

Data Pump jobs use a master table, a master process, and worker processes to perform the work and keep track of the progress. For every Data Pump Export job and Data Pump Import job, a master process is created. The master process controls the entire job, including communicating with the clients, creating and controlling a pool of worker processes, and performing logging operations.

create a directory object
To create a directory, you must have the DBA role or you must have been granted the CREATE ANY DIRECTORY privilege.

Example (a DBA creates directories on the Windows platform and grants access to user scott):

CONNECT system/manager
CREATE OR REPLACE DIRECTORY my_dir as 'D:\DataPump';
CREATE OR REPLACE DIRECTORY my_logdir as 'E:\logs';
GRANT read, write ON DIRECTORY my_dir TO scott;
GRANT read, write ON DIRECTORY my_logdir TO scott;

Example (a normal user with the CREATE ANY DIRECTORY privilege creates directories on the Unix platform - this user automatically has READ and WRITE privilege on that directory):

CONNECT system/manager
GRANT CREATE ANY DIRECTORY TO scott;
CONNECT scott/tiger
CREATE OR REPLACE DIRECTORY my_dir as '/usr/DataPump';
CREATE OR REPLACE DIRECTORY my_logdir as '/usr/logs'; 

Note that the CREATE DIRECTORY statement does not actually create the directory for you on disk. If the directory is invalid, a DataPump job will fail with:

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

Example(dpexport.bat)

@echo off
cls
set day=%date:~0,3%
set mm=%date:~4,2%
set dd=%date:~7,2%
set yy=%date:~-4%

expdp 'sys/oracle10g@dupdb as sysdba' dumpfile= dupdb_full_export_%yy%%mm%%dd%.dmp logfile=dupdb_full_export_%yy%%mm%%dd%.log DIRECTORY=DUPDB_FULL_EXPORT_DIR full=Y

expdp 'monitor/monitor' dumpfile=dpdir:exp%U.dmp logfile=prod_full_export20120606.log DIRECTORY=dpdir full=Y cluster=N exclude=statistics parallel=5

Schema Exports/Imports
expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

impdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log

impdp 'sys/syspw as sysdba' directory=dpdir dumpfile=exp%U logfile=IMP

DPProd.log full=y cluster=N parallel=5

Include/Exclude 

expdp scott/tiger@db10g schemas=SCOTT include=TABLE:"IN ('EMP', 'DEPT')" directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log
expdp scott/tiger@db10g schemas=SCOTT exclude=TABLE:"= 'BONUS'" directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

A single import/export can include multiple references to the parameters, so to export tables, views and some packages we could use either of the following approaches.

INCLUDE=TABLE,VIEW,PACKAGE:"LIKE '%API'"

or

INCLUDE=TABLE
INCLUDE=VIEW
INCLUDE=PACKAGE:"LIKE '%API'"

 Network Exports/Imports (NETWORK_LINK) The NETWORK_LINK parameter identifies a database link to be used as the source for a network export/import. The following database link will be used to demonstrate its use.

CONN / AS SYSDBA
GRANT CREATE DATABASE LINK TO test;

CONN test/test
CREATE DATABASE LINK remote_scott CONNECT TO scott IDENTIFIED BY tiger USING 'DEV';

In the case of exports, the NETWORK_LINK parameter identifies the database link pointing to the source server. The objects are exported from the source server in the normal manner, but written to a directory object on the local server, rather than one on the source server. Both the local and remote users require the EXP_FULL_DATABASE role granted to them.

expdp test/test@db10g tables=SCOTT.EMP network_link=REMOTE_SCOTT directory=TEST_DIR dumpfile=EMP.dmp logfile=expdpEMP.log

For imports, the NETWORK_LINK parameter also identifies the database link pointing to the source server. The difference here is the objects are imported directly from the source into the local server without being written to a dump file. Although there is no need for a DUMPFILE parameter, a directory object is still required for the logs associated with the operation. Both the local and remote users require the IMP_FULL_DATABASE role granted to them.

impdp test/test@db10g tables=SCOTT.EMP network_link=REMOTE_SCOTT directory=TEST_DIR logfile=impdpSCOTT.log remap_schema=SCOTT:TEST

Monitoring Data Pump

set lines 150 pages 100 numwidth 7
col program for a38
col username for a10
col spid for a7

select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "DATE", s.program, s.sid, 

       s.status, s.username, d.job_name, p.spid, s.serial#, p.pid 

  from v$session s, v$process p, dba_datapump_sessions d

 where p.addr=s.paddr and s.saddr=d.saddr; 

select sid,username ,event,status,state,module,last_call_et

from v$session

where status ='ACTIVE'

and username like 'SYS%'  --user who is importing/exporting

select * from dba_datapump_jobs

where operation ='EXPORT'

select * from dba_datapump_jobs

where operation ='IMPORT'

select * from dba_datapump_sessions

select username, opname,target_desc,sofar,totalwork,message

from v$session_longops

where message like 'SYS%'

waitevent associated with dp
When using data pump you will have a calling session which is the session that you log into the DB with when executing the impdp or expdb utility. Within the DB there will be one or more sessions which are called by this main data pump session;


direct path read

wait for unread message on broadcast channel
Datapump dump file I/O

Note:

You can also include the undocumented parameter METRICS=y to include additional logging information about number of objects and the time it took to process them in the log file.

on and off the ARCHIVELOG mode

1. Turning on ARCHIVELOG mode:

We consider a database that is not in ARCHIVELOG mode and also automatic archival is not enabled. To see the status of the database we can use of the following SQL commands:
SQL> select log_mode from v$database;

Shared Storage

D:\oracle\product\10.2.0\crs\BIN>cluvfy comp ssa -n home-03-db12

Verifying shared storage accessibility

Checking shared storage accessibility...

  Disk Partition                        Sharing Nodes (1 in count)
  ------------------------------------  ------------------------
  \Device\Harddisk0\Partition-1         home-03-db12

  Disk Partition                        Sharing Nodes (1 in count)
  ------------------------------------  ------------------------
  OCRCFG                                home-03-db12

  Disk Partition                        Sharing Nodes (1 in count)
  ------------------------------------  ------------------------
  VOTEDSK1                              home-03-db12

  Disk Partition                        Sharing Nodes (1 in count)
  ------------------------------------  ------------------------
  \Device\Harddisk2\Partition3          home-03-db12

  Disk Partition                        Sharing Nodes (1 in count)
  ------------------------------------  ------------------------
  \Device\Harddisk2\Partition4          home-03-db12

  Disk Partition                        Sharing Nodes (1 in count)
  ------------------------------------  ------------------------
  \Device\Harddisk2\Partition5          home-03-db12


Shared storage check was successful on nodes "home-03-db12".

Verification of shared storage accessibility was successful.

---------

Forbidden file and folder names on Windows

Here’s an interesting experiment. On a Windows desktop, right-click on the desktop, and create a new Folder. Then, try renaming it ‘aux’. Can’t do it, right? There are certain restricted words that cannot be used to name folders or files, even though they use regular characters. You can’t even name it, say, ‘aux.test’.

Create Duplicate DB with RMAN - Single Instance

You can use the RMAN DUPLICATE command to create a duplicate database from target database backups while still retaining the original target database. The duplicate database can be either identical to the original database or contain only a subset of the original tablespaces.

Using SPFile

SQL> create spfile from pfile='E:\oracle\product\10.2.0\admin\DUPDB\initDUPDB.ora';

File created.

SQL> shutdown immediate;
ORA-01507: database not mounted

One Concurrent User Per Schema

1- Set in init.ora or spfile.ora the parameter
SQL@ homedev> alter system set resource_limit=true;
System altered.

Windows Sysinternals

The Sysinternals Troubleshooting Utilities have been rolled up into a single Suite of tools. This file contains the individual troubleshooting tools and help files. It does not contain non-troubleshooting tools like the BSOD Screen Saver or NotMyFault.

How to Find Out Which Windows Process is Using a File

Ever come across the following messages and wondered how to find out what is using the file?

• Cannot delete file: Access is denied

User Level Security - Disable SQL*Plus and DDL Commands

Sqlplus_product_profile

A table that resides in the SYSTEM account. It provides product level security that supplements the user level security provided by SQL commands GRANT and REVOKE, and can be used with one's own applications as well as with other ORACLE products.

Using External Table

1- Create the CSV File on some location using excel
1,ABC,100
2,DEF,200
3,IJK,300