Heap size xxxx exceeds notification threshold (8192K)

Set  _kgl_large_heap_warning_threshold  to a reasonable high value or zero to prevent these warning messages. Value needs to be set in bytes.

If you want to set this to 8192 (8192 * 1024) and are using an spfile:

(logged in as "/ as sysdba")

SQL> alter system set "_kgl_large_heap_warning_threshold"=12288000 scope=spfile ;

SQL> shutdown immediate

SQL> startup

If using an "old-style" init parameter,

Edit the init parameter file and add

_kgl_large_heap_warning_threshold=8388608

In my case heap size reported in error is 11677K

so multiplied to 12000*1024=12288000

Auto extend off all datafiles in database at once

select 'ALTER database datafile '''||FILE_NAME||''' autoextend off;'  from dba_data_files where tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS1','USERS','UNDOTBS2') and AUTOEXTENSIBLE='YES';

run the output :)

Drop all objects under schema

select 'drop  '||object_type||' '||owner||'.'||object_name||';' from dba_objects where OWNER= 'CAARS_DM_USER';

for sdwh refresh

select 'drop  '||object_type||' '||owner||'.'||object_name||';' from dba_objects where OWNER in ('CAARS_DM_USER','DM_PWR_USER','DM_USER','DPMS_DM_USER','DW_USER','EDQ','ETL','HARTS_DM_USER','HDD_DM_USER','HMA_CONTROL','HMA_CRM_USER','HMA_DM_INCENTIVES','HMA_DM_MARKETING','HMA_DM_SALES','HMA_DM_SERVICE','HMA_DM_SSBI','HMA_HIST','HMA_LOOKUP','HMA_MARKETING','HMA_ODS','HMA_PROD','HMA_STAGE','HMA_STG_MV_USER','INCENT_DM_USER','LMRS_DM_USER','MKTG_DM_USER','QIS_DM_USER','SALES_DM_USER','SCORE_DM_USER','SED_DM_USER','SSBI_DM_USER','VOC_DM_USER','WIS_DM_USER'
);

oem target availability report status

SELECT *
FROM sysman.mgmt$availability_current a
JOIN sysman.mgmt$target_properties b
    ON a.target_guid = b.target_guid
WHERE (a.target_type IN ('oracle_database', 'oracle_listener', 'rac_database', 'oracle_emd')
    OR (a.target_type='host' AND a.target_name NOT LIKE '%was%'))
AND b.property_name='orcl_gtp_department'
AND b.property_value NOT IN ('HHHHH CCCCC SSSS', 'SAP', 'weblogic')

Changing Database name with nid in oracle

To change the database name

First
old name = hello
New name(to be changed) =hey

Pre requisite: Create pfile from spfile;


Sql> shut immediate;
         startup mount;
         exit;

nid target=sys/oracle dbname=hey setname=yes

it will show control file locations and asks yes/no

enter yes

It will complete the name change. If any problem it will return error

Once its done.

Edit pfile to reflect the new name of database including filename.

Create a new password file
orapwd file=orapwhey password=heyhey entries=3

After this set environment variables and startup as normal.

You can also use the following syntax

nid target=sys/oracle@catalog as sysdba dbname=catalog1

I got an error saying dbname failed error saying open cursors not enough. I increased open cursors and restarted it and ran same command again and it completed fine..

Partitioning and formatting new drive in linux with fidsk

http://www.idevelopment.info/data/Unix/Linux/LINUX_PartitioningandFormattingSecondHardDrive_ext3.shtml

Brief introduction into Materialized Views

Great Article.. See here
http://uhesse.com/2009/07/08/brief-introduction-into-materialized-views/