Welcome to Paul Chuks Oracle dba Professional

When preparing to taking export backup of application tables from a target database(source) and importing to another database(destination), You need to do the following: 1. You need to create directory where the dump file to be created with reside. CREATE DIRECTORY DUMPDIR AS '<filesystem_path with enough space>'; example: CREATE DIRECTORY DUMPDIR AS '/u01/export/dumpdir'; 2. Grant required permission to the directory. GRANT READ, WRITE ON DIRECTORY DUMPDIR TO <USERNAME>; 3. Select the properties of the objects/tables to be exported in the source database. select owner,segment_name,segment_type,tablespace_name, sum(bytes/1024/1024) from dba_segments where segment_name IN ('table_name','object_name','table_name','table_name'...) group by owner,segment_name,segment_type,tablespace_name ; example: --------------------------------------------------------------------------------------------------------------------------------- OWNE

This script is handy when you are having space challenge in your storage either +asm or filesyste. First connect to the target database and then to the catalog database. crosscheck archivelog all; resync catalog; run { allocate channel c1 TYPE 'sbt_tape'; allocate channel c2 TYPE 'sbt_tape'; allocate channel c3 TYPE 'sbt_tape'; backup archivelog all delete input; release channel c1; release channel c2; release channel c3; }

SQL> show parameter db_files NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ db_files                             integer     200 SQL> select count(*) from v$datafile;   COUNT(*) ----------        200 This issue result as a result of datafiles in the database has reached the number of db_files parameter in the database. To resolve this, follow the below steps: First, take the backup of the spfile with the below command: cd $ORACLE_HOME/dbs $ cp -p spfileSID.ora spfileSID.ora_11082014 Also create a backup copy of the pfile in /tmp directory with below command: SQL> create pfile='/tmp/pfileSID.ora' from spfile; File created. SQL> alter system set db_files=800 scope=spfile; System altered. Bounce the database for the change to take effect. SQL> shutdown immediate; SQL> startup

How to resolve error ORA-15032,ORA-15031,ORA-15025,ORA-15056 when adding a disk to asm. SQL> ALTER DISKGROUP ASM_FRA ADD DISK '/dev/dm-51'; ALTER DISKGROUP ASM_FRA ADD DISK '/dev/dm-51' * ERROR at line 1: ORA-15032: not all alterations performed ORA-15031: disk specification '/dev/dm-51' matches no disks ORA-15025: could not open disk "/dev/dm-51" ORA-15056: additional error message Linux-x86_64 Error: 13: Permission denied Additional information: 42 Additional information: 1927546192 I wanted to add  the following disk: (/dev/dm-51,/dev/dm-52,/dev/dm-53) First check the permissions on this files after the Unix admin has provision it. $ls -lrt /dev/dm* brw-rw---- 1 root disk 253, 19 Jan 31 13:46 /dev/dm-19 brw-rw---- 1 root disk 253, 53 Mar 2 21:04 /dev/dm-53 brw-rw---- 1 root disk 253, 52 Mar 2 21:04 /dev/dm-52 brw-rw---- 1 root disk 253, 51 Mar 2 21:04 /dev/dm-51 brw-rw---- 1 root disk 253, 46 Mar 2 21:12 /dev/dm-46 brw-r

CLEANING APPLICATION CONNECTIONS WHEN MAXIMUM NUMBER OF SESSIONS EXCEEDED. When users can't login to the database or their processes are hanging and probably getting this error: ERROR at line 1: ORA-12801: error signaled in parallel query server PZ99, instance <hostname>:<SID> (2) ORA-00018: maximum number of sessions exceeded Follow the below steps to resolve immediately so that users can connect to database: 1. login as a privilege OS user (example oracle owner) in the node having the issue. 2. run this command to show all the processes of application connection handles to the database: $ ps -ef|grep LOCAL=NO 3. Use this command to clean up connections so that users can connect: ps -ef|grep LOCAL=NO| awk '{print $2}'| xargs -i{} kill -9 {}

Script to get the USER CREATION SCRIPT in the database: set escape on select 'create user ' || U.username || ' identified ' || DECODE(password, NULL, 'EXTERNALLY', ' by values ' || '''' || password || '''' ) || chr(10) || 'default tablespace ' || default_tablespace || chr(10) || 'temporary tablespace ' || temporary_Tablespace || chr(10) || ' profile ' || profile || chr(10) || 'quota ' || decode ( Q.max_bytes, -1, 'UNLIMITED', NULL, 'UNLIMITED', Q.max_bytes) || ' on ' || default_tablespace || decode (account_status,'LOCKED', ' account lock', 'EXPIRED', ' password expire', 'EXPIRED \& LOCKED', ' account lock password expire', null) ||';' "Create user scripts" from dba_users U, dba_ts_quotas Q where U.username = '<USERNAME>' and U.username=Q.username(+) and U.default_tablespace=Q.tables

exec dbms_job.broken( , FALSE); exec dbms_job.run( ); Example: exec dbms_job.broken(111033, FALSE); exec dbms_job.run(111033);