Dicas ORACLE DBA

http://www.oracle.com/technetwork/java/javase/tech/vmoptions-jsp-140102.html

H ow to change preferences, and gather and publish statistics independently

Generate SQL to remove alerts from specific targets: select t.target_name , t.target_type , collection_timestamp , message , 'exec em_severity.delete_current_severity(''' || t.target_guid || ''',''' || metric_guid || ''',''' || key_value || ''')' em_severity from mgmt_targets t inner join mgmt_current_severity s on t.target_guid = s.target_guid where target_name like '&TARGET'; Sample output: TARGET_NAME TARGET_TYPE COLLECTION_TIMESTAMP MESSAGE EM_SEVERITY Denilson host 10/3/2010 12:03:30 PM Memory Utilization is 82.46%, crossed warning (80) or critical (95) threshold. exec em_severity.delete_current_severity('4D11D922179D4A9532397244E91478AE','86821B5F0CE858D6E4A7F7390E88B73C',' ')

DECLARE BEGIN /* Modify this query for whatever jobs you want to suspend, e.g. job_name like 'RMAN_%', or job_name like 'BACLUP%' */ FOR jguid IN (SELECT job_id FROM mgmt_job WHERE job_name like 'BACKUP_%' AND job_owner = 'SYSMAN' AND parent_job_id is null AND is_library = 0) LOOP BEGIN -- to suspend JOB mgmt_job_engine.suspend_job(jguid.job_id); -- to resume JOB mgmt_job_engine.resume_job (jguid.job_id); dbms_output.put_line(jguid.job_id); END; END LOOP; END; / * see mgmt_job_engine package for more options and details.

http://blogs.oracle.com/db/2010/09/oracle_support_master_note_for_10g_grid_control_oms_performance_issues_doc_id_11610031_1.html

[oracle@mumanga ~]$ adadmin adadmin: error while loading shared libraries: libclntsh.so.10.1: cannot open shared object file: No such file or directory Solution: [oracle@mumanga ~]$ export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib/ [oracle@mumanga ~]$ adadmin Copyright (c) 2002 Oracle Corporation Redwood Shores, California, USA Oracle Applications AD Administration Version 12.0.0

The file "fmrweb.res" contains a keyboard map. This map is used by Oracle Forms Server to detect key strokes entered in the user's browser and map them to Forms actions. The map assumes that the keyboard is similar to a VT100 keyboard. Therefore to change / customize mapping, one needs to modify the fmrweb.res file located under: + R12: $ORACLE_HOME/forms/admin/resource/US + R11i: $ORACLE_HOME/forms60/admin/resource/US Note: One needs to bounce the forms services for changes to take effect In case of multiple languages modify the appropriate mapping files (fmrweb.res) for that language E.g: "fmrweb .res" and "fmrpcweb .res", where is the Oracle language code.

SELECT FAV.APPLICATION_SHORT_NAME, FAV.APPLICATION_NAME,FRV.RESPONSIBILITY_KEY, FRV.RESPONSIBILITY_NAME FROM FND_APPLICATION_VL FAV, FND_RESPONSIBILITY_VL FRV WHERE FRV.APPLICATION_ID=FAV.APPLICATION_ID ORDER BY FRV.RESPONSIBILITY_NAME Using fnd_user_pkg: Input (Mandatory) -- username: User Name -- resp_app: Application Short Name -- resp_key: Responsibility Key -- security_group: Security Group Key -- description: Description -- start_date: Start Date -- end_date: End Date Sample Usage: set serveroutput on BEGIN fnd_user_pkg.addresp ('Username','OE','ORDER_ENTRY_SUPER_USER','STANDARD','Responsability added by Denilson Using PL/SQL',SYSDATE, null); COMMIT; DBMS_OUTPUT.put_line ('Responsibility Added Successfully'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line('Responsibility is not added due to'); DBMS_OUTPUT.PUT_LINE('ERROR:

11G has a new feature called Invisible Indexes. An invisible index is invisible to the optimizer as default. Using this feature we can test a new index without effecting the execution plans of the existing sql statements or we can test the effect of dropping an index without dropping it. SQL> create table teste_ii (a number, b varchar2(10)); SQL> create index idx_invisible on teste_ii(id); begin for i in 1..20 loop insert into teste_ii(a,b) values(i,'TESTE'||i); end loop; end; / commit; SQL> select * from teste_ii where a=10; A B ---------- ---------- 10 TESTE10 SQL> explain plan for 2 select * from teste_ii where a=10; Explained SQL> sELECT * FROM TABLE(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 726495292 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows |

For RMAN jobs, you can query the following columns in the V$SESSION_LONGOPS dynamic view: SID - The session ID for the RMAN channel SERIAL# - The serial number of the server session OPNAME - A description of what the row includes, such as RMAN: datafile copy, RMAN: full datafile backup, RMAN full datafile restore, RMAN: aggregate input, or RMAN: aggregate output CONTEXT - The context associated with the row, which is 2 for rows pertaining to backup output and 1 for other types of rows, except rows associated with proxy copies SOFAR - The work completed at the time the query is issued, which is the number of blocks read for image copies, the number of blocks read for files being backed up, the number of blocks written to the backup piece for backups, the number of blocks processed by a single job step for restores, and the number of files copied for proxy copies TOTALWORK - The work to be performed at the time the query was issued, which is the total number of blocks in the

Using Virtual Columns on 11g: Indexes defined against virtual columns are equivalent to function-based indexes. Virtual columns can be referenced in the WHERE clause of updates and deletes, but they cannot be manipulated by DML. Tables containing virtual columns can still be eligible for result caching. Functions in expressions must be deterministic at the time of table creation, but can subsequently be recompiled and made non-deterministic without invalidating the virtual column. In such cases the following steps must be taken after the function is recompiled: Constraint on the virtual column must be disabled and re-enabled. Indexes on the virtual column must be rebuilt. Materialized views that access the virtual column must be fully refreshed. The result cache must be flushed if cached queries have accessed the virtual column. Table statistics must be regathered. Virtual columns are not supported for index-organized, external, object, cluster, or temporary tables. The expression use

CONN DENILSON/DENILSON@ORCL SQL> CREATE TABLE teste(x number); Table created SQL> CREATE UNIQUE INDEX ix_teste ON teste(x); Index created SQL> begin 2 for i in 1..20 3 loop 4 insert into teste(x) values(i); 5 end loop; 6 end; 7 / PL/SQL procedure successfully completed SQL> COMMIT; Commit complete CONN DENILSON/DENILSON@ORCL2 SQL> CREATE TABLE teste(x number); Table created SQL> CREATE UNIQUE INDEX ix_teste ON teste(x); Index created SQL> begin 2 for i in 1..20 3 loop 4 insert into teste(x) values(i); 5 end loop; 6 end; 7 / PL/SQL procedure successfully completed SQL> COMMIT; Commit complete SQL> DELETE FROM teste WHERE x = 10; 1 row deleted SQL> COMMIT; Commit complete CONN DENILSON/DENILSON@ORCL SQL> ALTER SESSION SET max_dump_file_size=unlimited; Session altered. SQL> ALTER SESSION SET timed_statistics = true; Session altered. SQL> ALTER SESSION SET statistics_level = ALL ; Session altered. SQL>

To identify segment, then to the specified size: SELECT DISTINCT owner, segment_name, segment_type, tablespace_name, file_id FROM dba_extents WHERE ((block_id+1)*(SELECT value FROM v$parameter WHERE UPPER(name)='DB_BLOCK_SIZE')+BYTES) > (3000*1024*1024) AND tablespace_name= 'SYSAUX' ORDER BY file_id, owner, segment_name, segment_type;

The DRIVING_SITE hint instructs the CBO to execute the query at a different site than that selected by the database. This hint is useful if you are using distributed query optimization. For example: SELECT /*+ DRIVING_SITE(tab2) */ * FROM tab1, tab2@remote WHERE tab.id = tab2.id; If this query is executed without the hint, then rows from tab1 are sent to the local site, and the join is executed there. With the hint, the rows from tab2 are sent to the remote site, and the query is executed there and the result set is returned to the local site.

Using Extended Statistics to Optimize Multi-Column Relationships and Function-Based Statistics

Configure an Application Express Application as a Partner Application in Oracle AS Single Sign-On

http://www.oracle.com/technology/pub/articles/havawala-goldengate.html

Sample code to wrap any objects from schema. declare vFonte varchar2(32767); vCursor number; vError number; begin vCursor := dbms_sql.open_cursor; for rObjetos in (select owner, name, type from dba_source where type in ('PACKAGE BODY', 'PROCEDURE', 'FUNCTION') and line = 1 and instr(text, 'wrapped') "< " 1 and owner = 'DENILSON' order by owner, name, type) loop SELECT DBMS_METADATA.GET_DDL(rObjetos.type, rObjetos.name, rObjetos.owner) into vFonte FROM dual; dbms_sql.parse(vCursor, vFonte, dbms_sql.native); vError := dbms_sql.execute(vCursor); dbms_ddl.create_wrapped(ddl => vFonte); end loop; dbms_sql.close_cursor(vCursor); end; /

Sample scripts to make a logical backup using dbms_datapump -- BACKUP BY SCHEMA declare h1 NUMBER; begin h1 := dbms_datapump.open (operation => 'EXPORT', job_mode => 'SCHEMA', job_name => 'TESTE2', version => 'COMPATIBLE'); dbms_datapump.set_parallel(handle => h1, degree => 1); dbms_datapump.add_file(handle => h1, filename => 'BKP_SCHEMAS.LOG', directory => 'BKP_DATA_PUMP', filetype => 3); dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 0); dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_EXPR', value => 'IN(''DENILSON'')'); dbms_datapump.add_file(handle => h1, filename => 'BKP_SCHEMAS.DMP', directory => 'BKP_DATA_PUMP', filetype => 1); dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 1); dbms_datapump.set_parameter(handle => h1, name

http://www.oracle.com/technology/oramag/oracle/08-jan/o18dbasereplay.html

http://www.oracle.com/technology/oramag/oracle/09-jan/o19asktom.html

Drop manualy: Step 1: Drop AQ related objects in the SYSMAN schema Logon SQLPLUS as user SYSMAN SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'MGMT_NOTIFY_QTABLE',force =>TRUE); Step 2: Drop the DB Control Repository Objects Logon SQLPLUS as user SYS or SYSTEM, and drop the sysman account and management objects: SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP RESTRICT; SQL> EXEC sysman.emd_maintenance.remove_em_dbms_jobs; SQL> EXEC sysman.setEMUserContext('',5); SQL> REVOKE dba FROM sysman; SQL> DECLARE CURSOR c1 IS SELECT owner, synonym_name name FROM dba_synonyms WHERE table_owner = 'SYSMAN'; BEGIN FOR r1 IN c1 LOOP IF r1.owner = 'PUBLIC' THEN EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM '||r1.name; ELSE EXECUTE IMMEDIATE 'DROP SYNONYM '||r1.owner||'.'||r1.name; END IF; END LOOP; END; / SQL> DROP USER mgmt_view CASCADE; SQL> DROP ROLE mgmt_user; SQL> DROP USER sysman CASCADE; SQL> ALTER SYSTEM DISABLE

Sample code to send e-mail: DECLARE c utl_smtp.connection; PROCEDURE send_header(name VARCHAR2, header VARCHAR2) AS BEGIN utl_smtp.write_data(c,name ||':'|| header || UTL_TCP.CRLF); END; BEGIN c := utl_smtp.open_connection('mail.denilson.com.br'); utl_smtp.helo(c, 'Teste'); utl_smtp.mail(c, 'denilson@denilson.com.br'); utl_smtp.rcpt(c, 'denilson@denilson.com.br'); utl_smtp.open_data(c); send_header('From', 'denilson@denilson.com.br'); send_header('To', 'denilson@denilson.com.br'); send_header('Cc', 'denilson@denilson.com.br'); send_header('Subject', 'Teste'); utl_smtp.write_data(c, UTL_TCP.CRLF || 'Teste!'); -- Body message utl_smtp.close_data(c); utl_smtp.quit(c); EXCEPTION WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN BEGIN utl_smtp.quit(c); EXCEPTION WHEN utl_smtp.transient_error OR utl_smtp.permanent_

edit httpd.conf file in $ORACLE_HOME/Apache/Apache/conf change TimeOut parameter (default value is 300 seconds -5 minutes) The broken pipe reproduces each time the servlet is run with the following errors registered in Apache: sample error in error_log: oc4j_socket_recvfull timed out [ecid: 1126535220:144.23.227.233:11851:0:14,0] (4)Interrupted system call: MOD_OC4J_0038: Receiving data from oc4j exceeded the configured "Timeout" value and the error code is 4. [ecid: 1126535220:144.23.227.233:11851:0:14,0] MOD_OC4J_0054: Failed to call network routine to receive an ajp13 message from oc4j.

To Get server HOST NAME and HOST ADDRESS: SET serveroutput on BEGIN DBMS_OUTPUT.PUT_LINE(UTL_INADDR.GET_HOST_NAME); -- get local host name DBMS_OUTPUT.PUT_LINE(UTL_INADDR.GET_HOST_ADDRESS); -- get local IP addr END; / Using SYS_CONTEXT to get client host address: select SYS_CONTEXT('USERENV', 'IP_ADDRESS', 15) ipaddr from dual;