Category Archives: oracle

clean system data (oracle database)

Following, a useful sql request listing system tables sizes in descending order. It helps to detect which table data could be purged to save more disk space :

Audit table SYS.AUD$ could be the cause of full disk space. Administrator must control the growth and size of the audit trail.

Remove all references to the key before the key is to be dropped

if oracle db refuse to drop a constraint with message “Remove all references to the key before the key is to be dropped”. It’s clear that we must drop references before. But how to list these references.

Sql command bellow help to list all constraints which uses the constraint to be dropped “CONSTRAINT_TO_BE_DROPPED_NAME”

Oracle DB : Beware when specifying column max length

To create table STUDENT with single column NAME which must not excceed 6 characters, you may type the following

But this is wrong if NLS_LENGTH_SEMANTICS=BYTE (default value) : Typing VARCHAR2(6) means 6 bytes and not 6 characters.
In this case “Claude” will be accepted but not “Noémie”, because ‘é’ is encoded with 2 bytes and the sum will exceed 6 bytes.

The right way to specify column max length is as follow

NLS_LENGTH_SEMANTICS enables you to create CHAR and VARCHAR2 columns using either byte or character length semantics. Existing columns are not affected.

NCHAR, NVARCHAR2, CLOB, and NCLOB columns are always character-based. You may be required to use byte semantics in order to maintain compatibility with existing applications.

NLS_LENGTH_SEMANTICS does not apply to tables in SYS and SYSTEM. The data dictionary always uses byte semantics.

Get and/or set NLS_LENGTH_SEMANTICS value

ajax loader with APEX

1. Prerequisites in plsql oracle packages

1.1. Stored procedure GENERATE_REPORT

1.2. Stored function START_GENERATE_REPORT

1.3. Stored function GET_REPORT_STATUS

2. APEX application

HIDDEN TEXT ELEMENT ‘PAGE_REPORT_ID’
BUTTON ELEMENT ‘generate’: button click submits page
PAGE PROCESS ELEMENT ‘processGenerate’: activated on ‘generate’ button click

HTML FORM ELEMENT ‘loader’
– condition : PAGE_REPORT_ID content is not null
– content :

APPLICATION PROCESS ELEMENT : PROCESS_REPORT_STATUS
In apex go to application process and create new process. be sure you select “On Demand” as Point property of your process. 

IN PAGE HEADER
put javascript following javascript function :

3. how to use

click on button ‘generate’ and see the loader div which must change content according to REPORT.STATUS

oracle: exception : Ora-28001: the password has expired

May 6, 2013

this error tell us that user password has expired. check this with following request:
SELECT USERNAME, PROFILE, ACCOUNT_STATUS, EXPIRY_DATE FROM dba_users WHERE username=’your_oracle_username’;
value of ACCOUNT_STATUS column (expired or expired&locked)

password policies are defined in oracle PROFILE associated to user (value of PROFILE column). profile properties can be found here:
select * from dba_profiles where profile = ‘profile_name’;

take a look of line : resource_name =’PASSWORD_LIFE_TIME’ and depending on your security policies you can change the value to UNLIMITED to avoid password expiry

requête qui cherche dans tous les composants apex (à améliorer constemment)


May 2, 2012

–accept search_text prompt “Enter search text: “

select application_id, page_id, ‘Region’ objtype, region_name obj_name, region_source source
from   apex_application_page_regions
where lower(region_source) like lower(‘%&search_text%’)

UNION ALL
select application_id, page_id, ‘Region (condition)’ obj_type, region_name obj_name, to_clob(‘–EXPR1:’||CHR(13)||CHR(10)||condition_expression1||CHR(13)||CHR(10)||CHR(13)||CHR(10)||’–EXPR2:’||CHR(13)||CHR(10) || condition_expression2) source
from   apex_application_page_regions
where  lower(condition_expression1 || ‘ ‘ || condition_expression2) like  lower(‘%&search_text%’)

UNION ALL
select application_id, page_id, ‘Item’ obj_type, item_name obj_name, to_clob(item_source) source
from   apex_application_page_items
where  lower(item_source) like  lower(‘%&search_text%’)

UNION ALL
select application_id, page_id, ‘Item (condition)’ obj_type, item_name obj_name, to_clob(‘–EXPR1:’||CHR(13)||CHR(10)||condition_expression1||CHR(13)||CHR(10)||CHR(13)||CHR(10)||’–EXPR2:’||CHR(13)||CHR(10) || condition_expression2) source
from   apex_application_page_items
where  lower(condition_expression1 || ‘ ‘ || condition_expression2) like  lower(‘%&search_text%’)

UNION ALL
select application_id, page_id, ‘Item (default value)’ obj_type, item_name obj_name, to_clob(item_default) source
from   apex_application_page_items
where  lower(item_default) like  lower(‘%&search_text%’)

UNION ALL
select application_id, page_id, ‘Item (lov)’ obj_type, item_name obj_name, to_clob(lov_definition) source
from   apex_application_page_items
where  lower(lov_definition) like  lower(‘%&search_text%’)

UNION ALL
select application_id, page_id, ‘Item (post computation)’ obj_type, item_name obj_name, to_clob(source_post_computation) source
from   apex_application_page_items
where  lower(source_post_computation) like  lower(‘%&search_text%’)

UNION ALL
select application_id, page_id, ‘Item (readOnly condition)’ obj_type, item_name obj_name, to_clob(read_only_condition_exp1) source
from   apex_application_page_items
where  lower(read_only_condition_exp1) like  lower(‘%&search_text%’)

UNION ALL
select application_id, page_id, ‘Process’ obj_type, process_name obj_name, process_source source
from   apex_application_page_proc
where  lower(process_source) like  lower(‘%&search_text%’)

UNION ALL
select application_id, page_id, ‘Process (condition)’ obj_type, process_name obj_name, to_clob(‘–EXPR1:’||CHR(13)||CHR(10)||condition_expression1||CHR(13)||CHR(10)||CHR(13)||CHR(10)||’–EXPR2:’||CHR(13)||CHR(10) || condition_expression2) source
from   apex_application_page_proc
where  lower(condition_expression1 || ‘ ‘ || condition_expression2) like  lower(‘%&search_text%’)

UNION ALL
select application_id, page_id, ‘Calcul’ obj_type, execution_sequence||’ ‘||item_name obj_name, to_clob(computation) source
from   apex_application_page_comp
where  lower(computation) like  lower(‘%&search_text%’)

UNION ALL
select application_id, page_id, ‘Calcul (condition)’ obj_type, execution_sequence||’ ‘||item_name obj_name, to_clob(‘–EXPR1:’||CHR(13)||CHR(10)||condition_expression1||CHR(13)||CHR(10)||CHR(13)||CHR(10)||’–EXPR2:’||CHR(13)||CHR(10) || condition_expression2) source
from   apex_application_page_comp
where  lower(condition_expression1 || ‘ ‘ || condition_expression2) like  lower(‘%&search_text%’)

UNION ALL
select application_id, page_id, ‘Branch’ obj_type, TO_CHAR(process_sequence) obj_name, to_clob(branch_action) source
from   apex_application_page_branches
where  lower(branch_action) like  lower(‘%&search_text%’)

UNION ALL
select application_id, page_id, ‘Branch (condition)’ obj_type, to_char(process_sequence) obj_name, to_clob(‘–EXPR1:’||CHR(13)||CHR(10)||condition_expression1||CHR(13)||CHR(10)||CHR(13)||CHR(10)||’–EXPR2:’||CHR(13)||CHR(10) || condition_expression2) source
from   apex_application_page_branches
where  lower(condition_expression1 || ‘ ‘ || condition_expression2) like  lower(‘%&search_text%’)

UNION ALL
select application_id, page_id, ‘Button (condition)’ obj_type, BUTTON_SEQUENCE || ‘ ‘ || BUTTON_NAME obj_name, to_clob(‘–EXPR1:’||CHR(13)||CHR(10)||condition_expression1||CHR(13)||CHR(10)||CHR(13)||CHR(10)||’–EXPR2:’||CHR(13)||CHR(10) || condition_expression2) source
from   apex_application_page_buttons
where  lower(condition_expression1 || ‘ ‘ || condition_expression2) like  lower(‘%&search_text%’)

UNION ALL
select application_id, page_id, ‘Validation’ obj_type, VALIDATION_NAME obj_name, to_clob(‘–EXPR1:’||CHR(13)||CHR(10)||validation_expression1||CHR(13)||CHR(10)||CHR(13)||CHR(10)||’–EXPR2:’||CHR(13)||CHR(10) || validation_expression2) source
from   apex_application_page_val
where  lower(validation_expression1 || ‘ ‘ || validation_expression2) like  lower(‘%&search_text%’)

UNION ALL
select application_id, page_id, ‘Validation (condition)’ obj_type, VALIDATION_NAME obj_name, to_clob(‘–EXPR1:’||CHR(13)||CHR(10)||condition_expression1||CHR(13)||CHR(10)||CHR(13)||CHR(10)||’–EXPR2:’||CHR(13)||CHR(10) || condition_expression2) source
from   apex_application_page_val
where  lower(condition_expression1 || ‘ ‘ || condition_expression2) like  lower(‘%&search_text%’)

UNION ALL
select ap.application_id application_id, -1 page_id, ‘application_process’ obj_type, to_char(ap.PROCESS_NAME) obj_name, process source
  from apex_application_processes ap
where  lower(ap.process) like  lower(‘%&search_text%’)

UNION ALL
select aa.application_id application_id, -1 page_id, ‘authorization model’ obj_type, to_char(aa.authorization_scheme_name) obj_name, to_clob(aa.scheme) source
  from apex_application_authorization aa
where  lower(aa.scheme) like  lower(‘%&search_text%’)

———————————————————————
———————————————————————
—- replace authorization model request by the following if APEX 4
/*

UNION ALL
select aa.application_id application_id, -1 page_id, ‘authorization model’ obj_type, to_char(aa.authorization_scheme_name) obj_name,
       to_clob(aa.attribute_01 || ‘ || ‘ || aa.attribute_02 || ‘ || ‘ ||aa.attribute_03 || ‘ || ‘ ||aa.attribute_04 || ‘ || ‘ ||aa.attribute_05 || ‘ || ‘ ||
               aa.attribute_06 || ‘ || ‘ || aa.attribute_07 || ‘ || ‘ ||aa.attribute_08 || ‘ || ‘ ||aa.attribute_09 || ‘ || ‘ ||aa.attribute_10 || ‘ || ‘ ||
               aa.attribute_11 || ‘ || ‘ || aa.attribute_12 || ‘ || ‘ ||aa.attribute_13 || ‘ || ‘ ||aa.attribute_14 || ‘ || ‘ ||aa.attribute_15
       ) source
  from apex_application_authorization aa
where  lower(aa.attribute_01) like  lower(‘%&search_text%’) OR
       lower(aa.attribute_02) like  lower(‘%&search_text%’) OR
       lower(aa.attribute_03) like  lower(‘%&search_text%’) OR
       lower(aa.attribute_04) like  lower(‘%&search_text%’) OR
       lower(aa.attribute_05) like  lower(‘%&search_text%’) OR
       lower(aa.attribute_06) like  lower(‘%&search_text%’) OR
       lower(aa.attribute_07) like  lower(‘%&search_text%’) OR
       lower(aa.attribute_08) like  lower(‘%&search_text%’) OR
       lower(aa.attribute_09) like  lower(‘%&search_text%’) OR
       lower(aa.attribute_10) like  lower(‘%&search_text%’) OR
       lower(aa.attribute_11) like  lower(‘%&search_text%’) OR
       lower(aa.attribute_12) like  lower(‘%&search_text%’) OR
       lower(aa.attribute_13) like  lower(‘%&search_text%’) OR
       lower(aa.attribute_14) like  lower(‘%&search_text%’) OR
       lower(aa.attribute_15) like  lower(‘%&search_text%’)

*/

UNION ALL
select alov.application_id application_id, -1 page_id, ‘lov’ obj_type, to_char(alov.list_of_values_name) obj_name, to_clob(alov.list_of_values_query) source
  from apex_application_lovs alov
where  lower(alov.list_of_values_query) like  lower(‘%&search_text%’)

ORDER BY 1,2,3,4

exit imp prompt (IMP-0002 error)

You are trying to import oracle dump file with imp command, but imp don’t have access to that dump file. You will be prompted to enter a new dump file path name.

IMP-00002: failed to open expdat.dmp for read
Import file: expdat.dmp >

>> use CTRL-D to exit imp prompt

import oracle dump file

import dump file
>> imp witr/passwd@WITRDBIDENT file=/tmp/expdat.dmp fromuser=witr touser=witr log =/tmp/imp.log

import one table from dump file
>> imp witr/passwd@WITRDBIDENT file=/tmp/expdat.dmp fromuser=witr touser=witr tables=T_WITR log =/tmp/imp.log

start and stop oracle DB sql traces with shell scripts

startSqlTrace.sh : start sql traces

stopSqlTrace.sh : stop sql traces

find out oracle DB service name or SID

If I have system access to oracle DB, and want to find out the service name

> sqlplus system
password: XXXX
SQL> select sys_context(‘userenv’,’instance_name’) from dual;

output

SYS_CONTEXT(‘USERENV’,’INSTANCE_NAME’)
————————————————–
MY_INSTANCE_NAME

Now, to find out SID

SQL> select sys_context(‘userenv’,’sid’) from dual;