Not Null constraints

CREATE OR REPLACE PROCEDURE check_uncons_columns_for_null (
p_schema VARCHAR2,
p_table_name VARCHAR2 DEFAULT NULL
)
IS
CURSOR x
IS
SELECT table_name, column_name,
MAX (column_name) OVER (PARTITION BY table_name) AS lastcol
FROM (SELECT a.table_name, a.column_name
FROM dba_tab_columns a
WHERE a.owner = p_schema
AND a.table_name = NVL (UPPER (p_table_name), a.table_name)
MINUS
SELECT a.table_name, b.column_name
FROM dba_cons_columns b, dba_constraints a
WHERE a.owner = p_schema
AND a.constraint_type = 'C'
AND a.table_name = NVL (UPPER (p_table_name), a.table_name)
AND a.table_name = b.table_name
AND a.owner = b.owner
AND a.constraint_name = b.constraint_name)
ORDER BY 1, 2;

str0 VARCHAR2 (32767);
str1 VARCHAR2 (32767);
str2 VARCHAR2 (32767);
str3 VARCHAR2 (32767);
prev VARCHAR2 (100) := '*';
cnt NUMBER;
trailer VARCHAR2 (5);

PROCEDURE do_sql (thesql VARCHAR2)
IS
tcursor INTEGER;
dummy INTEGER;
BEGIN
tcursor := DBMS_SQL.open_cursor;
DBMS_SQL.parse (tcursor, thesql, 2);
dummy := DBMS_SQL.EXECUTE (tcursor);
DBMS_SQL.close_cursor (tcursor);
END;
BEGIN
FOR i IN x
LOOP
IF prev != i.table_name
THEN
str0 := 'declare ';
str1 := 'begin select ';
str2 := ' into ';
str3 := ' ';
cnt := 1;
END IF;

IF i.column_name = i.lastcol
THEN
trailer := ' ';
ELSE
trailer := ',' || CHR (10);
END IF;

str0 := str0 || 'v' || LTRIM (cnt) || ' number;';
str1 :=
str1 || 'sum(decode(' || i.column_name || ',null,1,0))' || trailer;
str2 := str2 || 'v' || LTRIM (cnt) || trailer;
str3 :=
str3
|| 'if v'
|| LTRIM (cnt)
|| ' = 0 then '
|| 'dbms_output.put_line(''alter table '
|| p_schema
|| '.'
|| i.table_name
|| ' modify ('
|| i.column_name
|| ' not null);''); end if;'
|| CHR (10);

IF i.column_name = i.lastcol
THEN
str2 := str2 || ' from ' || p_schema || '.' || i.table_name || ';';
str3 := str3 || ' end;';
do_sql (str0 || ' ' || str1 || ' ' || str2 || ' ' || str3);
END IF;

prev := i.table_name;
cnt := cnt + 1;
END LOOP;
END;
/

calculate time between to columns

SELECT TO_DATE( '27-NOV-07 15:35:44', 'DD-MON-YY HH24:MI:SS' )
- TO_DATE( '27-NOV-07 15:02:50', 'DD-MON-YY HH24:MI:SS' ) days
, TO_CHAR( TRUNC( sysdate )
+ ( TO_DATE( '27-NOV-07 15:35:44', 'DD-MON-YY HH24:MI:SS' )
- TO_DATE( '27-NOV-07 15:02:50', 'DD-MON-YY HH24:MI:SS' )
)
, 'MI:SS' ) minsec
FROM dual;
DAYS MINSE
---------- -----
.022847222 32:54

composite unique constraint mysql

alter table room_new add unique key(room_no,rack_no,slot_no)

stealthpuppy / Script Corner

stealthpuppy / Script Corner

Index on Miscellaneous Items (JL Comp)

Index on Miscellaneous Items (JL Comp)

www.oracledba.co.uk

www.oracledba.co.uk

Joomla! Extensions Directory - Tools

Joomla! Extensions Directory - Tools

How to drop a dismounted ASM disk group?

Problem :

I was not able to Mount or Drop ASM disk group.

SQL> select group_number, name, STATE from v$asm_diskgroup;

GROUP_NUMBER NAME STATE
------------ ------------------------------ -----------
3 RAC_DATA1 DISMOUNTED
4 RAC_DATA2 DISMOUNTED
3 RAC_DG1 MOUNTED
4 RAC_DG2 MOUNTED

SQL> drop diskgroup RAC_DATA1;
drop diskgroup RAC_DATA1
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15001: diskgroup "RAC_DATA1" does not exist or is not mounted

SQL> alter diskgroup RAC_DATA1 mount;
alter diskgroup RAC_DATA1 mount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15024: discovered duplicately numbered ASM disk 0

Solution


I have followed ML Note:268481.1
Re-creating ASM Instances and Diskgroups

I have used force option while creating diskgroups

Complete checklist for manual upgrades to 10gR2

I referred the following Metalink Notes for the same.

316889.1
233436.1

How to calculate folder size in Linux

du -ahc folder_name

Solaris User Profile Customization

Edit /etc/profile

#User Profile Customization
PS1="\u@\h\w>"
export PS1
TERM=vt100
export TERM

It will set prompt as USER@HOSTNAME

How to find out which version of Linux

How to determine if Linux is 64bit ?

uname -a

Linux snook.srv.bang.indorion.org 2.6.9-42.ELsmp #1 SMP Wed Jul 12 23:27:17 EDT 2006 i686 i686 i386 GNU/Linux

x86_64 GNU/Linux indicates I have 64bit CPU. If you use see i386/i486/i586/i686 it is a 32 bit CPU.

How to find out which version of Linux you are running ?
Method 1

ls /etc/*release*
ls /etc/*version*

This will give you a file that contains the version.
cat file_that_ls_found

Method 2

uname -a (prints all information)

uname -r (prints the kernel release)

uname -v (prints the kernel version)

uname -o (prints the operating system)

Oracle Edition Version Check

select * from v$version;

select * from PRODUCT_COMPONENT_VERSION;

SET SERVEROUT ON
EXEC DBMS_OUTPUT.PUT_LINE (DBMS_DB_VERSION.VERSION);

query CRS version

srvctl -V
export ORACLE_SID=+ASM

select status from v$instance;

select * from v$version;

crsctl query crs softwareversion
crsctl query crs activeversion

cemutlo -w

The cemutlo command is used by the EM agent to query CRS version.

Enter your search terms Submit search form

How to speed up Import

How to speed up Import
Here are are some suggestions.
1. Check the database alert log. If you see messages like Checkpoint Incomplete, or if log files are switching frequently, then you need to increase the size and number of redo log files.
2. Set or increase the size of the BUFFER parameter on your import. This will increase the size of the buffer that Oracle uses to do the bulk inserts.
3. If possible, create a large UNDO tablespace, and switch to that large UNDO tablespace by using the alter system undo_tablespace=Large_Undo before the import. Then, run the IMP with the COMMIT=N option. After the import completes, switch back to the old UNDO tablespace and then drop the large one if it is no longer necessary. If you do not have the enough space for a larger redo then try increasing the COMMIT parameter setting on the IMP command.
4. Increase the SORT_AREA_SIZE parameter. Increasing this parameter will provide more space for sorting operations when creating indexes.
5. Increase the LOG_CHECKPOINT_INTERVAL. This will help to reduce the number of checkpoints.
Here are are some suggestions from Chris Marquez
———————–
Faster IMPORTS
———————–
Set IMP parameter COMMIT = Y.
Set IMP parameter RECORDLENGTH >= EXP RECORDLENGTH value.
Set IMP parameter BUFFER = MB (Set in the MB range not KB range)
Set IMP parameter STATISTICS = NONE (9i, n/a 8i)
Set IMP parameter RECALCULATE_STATISTICS = N. (8i, n/a 9i)
Set IMP parameter ANALYZE = N. (8i, n/a 9i)
Set IMP parameter INDEXES = N (Import them later…separately)
Set init.ora parameter LOG_ARCHIVE_START = FALSE *&* alter database
noarchivelog;
Set init.ora parameter _disable_logging = TRUE (Warning, this could be
dangerous and unrecoverable in failure. Backup immediately after import
- * Redo records (to disk) will NOT be generated (redo WAS generated in
the log buffer.) [Even with _disable_logging you still “switch” logs
when they get “full”])
Set init.ora parameter _wait_for_sync = FALSE (Warning, this could be
dangerous and unrecoverable in failure. Backup immediately after import
- _wait_for_sync: Wait_for_sync is an oracle generic parameter which,
when set to false, will allow the system to complete commits without
waiting for the redo-log buffer flushes to complete.)
Make redo logs enormous; 500MB, 1GB, etc.
Use Locally Managed Tablespaces on target database.

csv format with select *

Hi,

Here you have both - XML extraction to generate CSV:http://laurentschneider.com/wordpress/2007/05/csv-format-with-select.htmlRegards,

Gathering Index Statistics

Oracle can gather some statistics automatically while creating or rebuilding a B*-tree or bitmap index. The COMPUTE STATISTICS option of CREATE INDEX or ALTER INDEX … REBUILD enables this gathering of statistics.

set vi as default editor

I have set vi as default editor by editing glogin.sql

define_editor=vi