H.Tonguç YILMAZ Blog

Steven Feuerstein’s answer;

http://htmldb.oracle.com/pls/otn/f?p=2853:4:::NO::P4_QA_ID:8323

My tests;

DROP TABLE aglbtemptbl purge ;

CREATE GLOBAL TEMPORARY TABLE aglbtemptbl ( acolumn VARCHAR2(64) ) ;

 

set sqlprompt “_user ‘on’ _date ‘at’ _connect_identifier > ”

set timing on

 

alter system flush shared_pool;

alter system flush buffer_cache;

 

set serveroutput off

declare

  l_start NUMBER;

  l_upto NUMBER := 10000;

begin

  l_start := dbms_utility.get_time;

  for i in 1..l_upto loop

    dbms_output.put_line(‘En büyük dba bizim dba ‘);

  end loop;

  INSERT INTO aglbtemptbl VALUES (’serveroutput off -> ‘|| (dbms_utility.get_time – l_start) || ‘ hsecs’);

end;

/

 

– Elapsed: 00:00:01.03

 

alter system flush shared_pool;

alter system flush buffer_cache;

 

set serveroutput on

declare

  l_start NUMBER;

  l_upto NUMBER := 10000;

begin

  l_start := dbms_utility.get_time;

  for i in 1..l_upto loop

    dbms_output.put_line(‘En büyük dba bizim dba ‘);

  end loop;

  INSERT INTO aglbtemptbl VALUES (’serveroutput on -> ‘||(dbms_utility.get_time – l_start) || ‘ hsecs’);

end;

/

 

– Elapsed: 00:00:06.90

 

On ile off arasındaki bu farkı nasıl yorumluyorsunuz? Cache etkisi flush edilerek yok edildi ve 5 kez çalıştırdım makineda başka bir şey çalışmıyorken;

 

HR on 22/09/2006 at XE > select * from aglbtemptbl  ;

 

ACOLUMN

————————————————————-

serveroutput off -> 50 hsecs

serveroutput on -> 41 hsecs

serveroutput off -> 47 hsecs

serveroutput on -> 36 hsecs

serveroutput off -> 48 hsecs

serveroutput on -> 33 hsecs

serveroutput off -> 68 hsecs

serveroutput on -> 31 hsecs

serveroutput off -> 83 hsecs

serveroutput on -> 32 hsecs

 

10 rows selected.

This release fixes several issues in Application Express 2.2, you find the details below. The full installation of Application Express 2.2.1 can be downloaded from the OTN Site.

The Application Express 2.2.1 patch set, to be applied to an Application Express 2.2 instance, can be downloaded from MetaLink (Patch Number 5500033) and is about 2.6MB.

http://dgielis.blogspot.com/2006/09/apex-patch-221.html

1- The following commands will work for versions 8, 9 and 10.

– Oracle 8i, 9i (Unix + Windows)
select NVL(substr(file_spec, 1, instr(file_spec, ‘\’, -1, 2) -1) , substr(file_spec, 1, instr(file_spec, ‘/’, -1, 2) -1)) folder
from dba_libraries
where library_name = ‘DBMS_SUMADV_LIB’

2- The get_env function was introduced in 10g although the DBMS_SYSTEM package exists since 8i. The only problem is that the DBMS_SYSTEM.get_env requires sysdba privileges which you may not have.

– Oracle 10g
DECLARE
folder VARCHAR2(100);
BEGIN
sys.dbms_system.get_env(‘ORACLE_HOME’, folder);
dbms_output.put_line(folder);
END;

3- You may use java SP but you must count that the user installed the java option.

conn hr/hr

set serveroutput on
– Creating a SQL Tuning Task
DECLARE
my_task_name VARCHAR2(30);
my_sqltext   CLOB;
BEGIN
my_sqltext := ‘SELECT /*+ ORDERED */ * ‘                      ||
‘FROM employees e, locations l, departments d ‘ ||
‘WHERE e.department_id = d.department_id AND ‘  ||
‘l.location_id = d.location_id AND ‘      ||
‘e.employee_id < :bnd';

-- uncomment to re-run
-- DBMS_SQLTUNE.drop_tuning_task(task_name   => ‘my_sql_tuning_task’);

my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text    => my_sqltext,
bind_list   => sql_binds(anydata.ConvertNumber(100)),
user_name   => ‘HR’,
scope       => ‘COMPREHENSIVE’,
time_limit  => 60,
task_name   => ‘my_sql_tuning_task’,
description => ‘Task to tune a query on a specified employee’);
END;
/

– Executing a SQL Tuning Task
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => ‘my_sql_tuning_task’ );
END;
/

– Checking the Progress of the SQL Tuning Advisor
SELECT sofar, totalwork FROM V$ADVISOR_PROGRESS WHERE username = ‘HR’ AND task_id =
(SELECT TASK_ID FROM USER_ADVISOR_TASKS WHERE task_name = ‘my_sql_tuning_task’);

– Checking the Status of a SQL Tuning Task
SELECT TASK_ID, status FROM USER_ADVISOR_TASKS WHERE task_name = ‘my_sql_tuning_task’;

– Displaying the Results of a SQL Tuning Task
SET LONG 10000
SET LONGCHUNKSIZE 10000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( ‘my_sql_tuning_task’)
FROM DUAL;

http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14211/sql_tune.htm

An Oracle database server running on a Windows machine can manipulate COM Automation server objects from PL/SQL or Java stored in a database. To access a COM Automation server, a client application requests an instance of an object from the server that implements the IDispatch standard interface. Using this object, you can create instances of other objects and invoke methods on those objects with arguments.

An Oracle database on Windows can act as a COM Automation client by referencing orawpcom.dll through an external procedure callout, which maps stored procedure calls to DLL function calls. The script comwrap.sql will automatically create a package to wrap the external procedure calls. In order to install comwrap.sql under a schema, the schema must have CREATE LIBRARY privilege, which isn’t granted by default to non-DBA accounts.

For example, if you want to enable COM callouts from the HR account (i.e., the one used by the COM Automation demos), you could run the following:

cd %ORACLE_HOME%\com
sqlplus “/ as sysdba”
SQL> grant create library to hr;
SQL> connect hr/hr
SQL> @comwrap

The last step creates several objects, including the PL/SQL package ORDCOM, which provides a slightly friendlier interface on COM interface calls. Using this package, you can create instances of COM Automation objects, invoke their methods, and reference their properties. For example, to create an instance of an Excel spreadsheet, use the following code:

SQL> variable hr number;
SQL> variable app number;
SQL> execute :hr := ordcom.CreateObject(‘Excel.Application’,0,”,:app);

In the example, the variable hr contains the COM standard result number. If this value isn’t 0 (success), you can examine the error returned with this syntax:

SQL> variable error_src varchar2(255);
SQL> variable error_description varchar2(255);
SQL> variable error_helpfile varchar2(255);
SQL> variable error_helpid number;
SQL> execute
ordcom.GetLastError(:error_src,:error_description,:error_helpfile,:
error_helpid);
SQL> print

The app variable will contain a handle to the application instance. If you look at your Task Manager window, you should see an instance of EXCEL.EXE running in the background. You can access a property from your COM object, such as the current WorkBooks collection with this code:

SQL> variable wbs number;
SQL> execute :hr := ordcom.GetProperty(:app,’WorkBooks’,0,:wbs);

You can also invoke an objects method. This is how you would issue a Quit command to Excel when you finish using it:

SQL> variable dummy number;
SQL> execute :hr := ordcom.Invoke(:app,’Quit’,0,:dummy);

You can also wrap each one of these functions in a PL/SQL package. You can find the demos that have been provided since Oracle8i (and still say Oracle8i in the 10g versions) in the Windows ORACLE_HOME/com/demos directory. (For Oracle 10g, you need to install the companion disc for Oracle 10g). There is a package called ORDExcel, created in the script excelsol.sql and a demo, which depends on the HR schemas tables in the script exceldem.sql. There are also demos for MAPI (the mail exchange API), which sends an e-mail, and Microsoft PowerPoint and Microsoft Word, which create documents using PL/SQL.

Since stored procedures are accessible to Java stored procedures, you can also write procedures in Java to access COM objects. There is a Java package stored in the Oracle database under oracle.win.com.* that wraps the COM external procedure DLL. To create a client handle to a COM automation server, simply create an instance of an Automation object. Then, you can access properties or invoke methods in the same way as PL/SQL.

import oracle.win.com.*;

. . .
Automation word = new Automation(“Word.Basic”);
word.InitArg();
word.Invoke(“FileNewDefault”);

word.InitArg();
word.SetArg(“test.doc”);
word.Invoke(“FileSaveAs”);
word.Destroy();
word = null;
. . .

There is a Java demo program that creates a Word document in %ORACLE_HOME%\com\java\demos\TestWORD.java. It includes a wrapper for a handful of Word interface calls, as well as a test program. However, none of the demo application wrappers are complete. If you want to be complete, you may want to investigate creating a program that interrogates the IDispatch interface of an object and automatically generates a PL/SQL or Java wrapper-based on the ORDCOM package.

http://articles.techrepublic.com.com/5100-9592-5800468.html

Oracle’s export and import utilities were re-engineered in Release 10g.

Buried among the more flashy features, such as the ability to disconnect and reconnect to a running export or import job, are a number of smaller changes that help the DBA reorganize tablespaces. Here are three of them;

1- Export tablespaces as a unit

In the past, the export (exp) and import (imp) utilities had three modes:

You could export a single table and its dependent objects such as indexes;
you could export all objects owned by a specific user;
or you could export the entire database.

But tablespaces were a problem. Objects owned by many different users could be stored in a given tablespace, but some of their objects might be stored in other tablespaces.

So, the only solution was to query the data dictionary to find the exact list of tables and their owners and use table-mode export to export the objects individually.

In Oracle 10g, the Data Pump version of export (expdp) lets you directly export all the objects in a tablespace.

The TABLESPACES parameter lets you specify which tablespace(s) you want to export.

TABLESPACES=name [,...]

This is particularly useful if you’ve inherited a database with a lot of dictionary-based tablespaces, and you want to reduce fragmentation by recreating the tablespaces as locally managed, and then re-import the contents.

2- Rename datafile names during import

When migrating a database from one platform to another prior to 10g, the DBA was required to pre-create the tablespaces and their datafiles before importing.

Why? Because the dump file created by export contained datafile pathnames in the format of the original database’s operating system. These pathnames would cause errors if used with a different operating system on import.

In the 10g Data Pump version of import (impdp), the REMAP_DATAFILE parameter can be used to rename these datafiles on the fly. The format is:

REMAP_DATAFILE=source_datafile:target_datafile

This option is used with FULL imports only, and the userID you specify must have the IMP_FULL_DATABASE role.

3- Change tablespace names during import

The impdp utility also lets you load objects into different tablespaces than they came from originally. Before 10g, the way to do this was complex. First, you had to remove your quota on the original tablespace so that you had no privileges to write there. Then, you set your default tablespace to the desired one. During the import, objects that were in the original tablespace would be stored in the default tablespace for the user. Then you had to remember to set everything back again when you were done.

In 10g import, the REMAP_TABLESPACE parameter makes this a lot easier. You still need to have quota on the destination tablespace, but no other preparation is required. Simply add the parameter:

REMAP_TABLESPACE=source_tablespace:target_tablespace

Objects will be automatically sent to the new tablespace.

http://articles.techrepublic.com.com/5100-9592-6111806.html

http://blogs.oracle.com/carlback/2006/09/08#a53

If you install Apex 2.2 into an Oracle version 10.2.0.3 and above, it will use the native password validation function and everything will be fine; http://daust.blogspot.com/2006/09/apex-database-authentication.html

If you are using Oracle 9i or 10g, then you can create a user-defined aggregate function to concatenate the column values into one string in a row. The following example uses a concat_all function by James Padfield, which is a modification of a stragg function by Tom Kyte. James Padfield’s modification uses an object as a parameter, so that the concatenation character(s) can be passed to the function, overcoming the prior limitation of passing only one variable.

scott@ORA92> CREATE OR REPLACE TYPE concat_expr AS OBJECT (

  2    str VARCHAR2 (4000),

  3    del VARCHAR2 (4000));

  4  / 

Type created.

scott@ORA92> CREATE OR REPLACE TYPE concat_all_ot AS OBJECT (

  2    str VARCHAR2 (4000),

  3    del VARCHAR2 (4000),

  4  

  5    STATIC FUNCTION odciaggregateinitialize (

  6      sctx IN OUT concat_all_ot)

  7      RETURN NUMBER,

  8  

  9    MEMBER FUNCTION odciaggregateiterate (

 10      SELF IN OUT concat_all_ot,

 11      ctx IN concat_expr)

 12      RETURN NUMBER,

 13  

 14    MEMBER FUNCTION odciaggregateterminate (

 15      SELF IN concat_all_ot,

 16      returnvalue OUT VARCHAR2,

 17      flags IN NUMBER)

 18      RETURN NUMBER,

 19  

 20    MEMBER FUNCTION odciaggregatemerge (

 21      SELF IN OUT concat_all_ot,

 22      ctx2 concat_all_ot)

 23      RETURN NUMBER);

 24  / 

Type created.

scott@ORA92> CREATE OR REPLACE TYPE BODY concat_all_ot

  2  AS

  3    STATIC FUNCTION odciaggregateinitialize (

  4      sctx IN OUT concat_all_ot)

  5      RETURN NUMBER

  6    IS

  7    BEGIN

  8      sctx := concat_all_ot (NULL, NULL);

  9      RETURN odciconst.success;

 10    END;

 11    MEMBER FUNCTION odciaggregateiterate (

 12      SELF IN OUT concat_all_ot,

 13      ctx IN concat_expr)

 14      RETURN NUMBER

 15    IS

 16    BEGIN

 17      IF SELF.str IS NOT NULL THEN

 18        SELF.str := SELF.str || ctx.del;

 19      END IF;

 20      SELF.str := SELF.str || ctx.str;

 21      RETURN odciconst.success;

 22    END;

 23  

 24    MEMBER FUNCTION odciaggregateterminate (

 25      SELF IN concat_all_ot,

 26      returnvalue OUT VARCHAR2,

 27      flags IN NUMBER)

 28      RETURN NUMBER

 29    IS

 30    BEGIN

 31      returnvalue := SELF.str;

 32      RETURN odciconst.success;

 33    END;

 34  

 35    MEMBER FUNCTION odciaggregatemerge (

 36      SELF IN OUT concat_all_ot,

 37      ctx2 IN concat_all_ot)

 38      RETURN NUMBER

 39    IS

 40    BEGIN

 41      IF SELF.str IS NOT NULL THEN

 42        SELF.str := SELF.str || SELF.del;

 43      END IF;

 44      SELF.str := SELF.str || ctx2.str;

 45      RETURN odciconst.success;

 46    END;

 47  END;

 48  / 

Type body created.

scott@ORA92> CREATE OR REPLACE FUNCTION concat_all (

  2    ctx IN concat_expr)

  3    RETURN VARCHAR2 DETERMINISTIC PARALLEL_ENABLE

  4    AGGREGATE USING concat_all_ot;

  5  / 

Function created.

scott@ORA92> -- test data:

scott@ORA92> select * from x

  2  / 

C

-

A

B

C

scott@ORA92> -- usage of concat_all function above:

scott@ORA92> SELECT concat_all (concat_expr (col_string, '->'))

  2  FROM   x

  3  / 

CONCAT_ALL(CONCAT_EXPR(COL_STRING,'->'))

----------------------------------------------------------------------------------------------------

A->B->C

set pagesize 0

drop table emp_40 purge;
drop table emp_50 purge;
drop table emp_left_over purge;
drop table err$_emp_left_over  purge;
drop table err$_emp_40 purge;
drop table err$_emp_50 purge;

create table emp_40 nologging parallel 2 as SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,DEPARTMENT_ID FROM employees where 1 = 2;
create table emp_50 nologging parallel 2 as SELECT EMPLOYEE_ID,EMAIL,PHONE_NUMBER,HIRE_DATE,DEPARTMENT_ID FROM employees where 1 = 2;
create table emp_left_over nologging parallel 2 as SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID FROM employees where 1 = 2;

alter table emp_left_over
add constraint NN_emp_left_over_DEPARTMENT_ID
check (DEPARTMENT_ID IS NOT NULL);

exec dbms_errlog.create_error_log(‘EMP_40′);
exec dbms_errlog.create_error_log(‘EMP_50′);
exec dbms_errlog.create_error_log(‘EMP_LEFT_OVER’);

SELECT DEPARTMENT_ID,count(*) FROM employees
group by DEPARTMENT_ID order by DEPARTMENT_ID;

set timing on
ALTER SESSION ENABLE PARALLEL DML;

INSERT /*+ append parallel(2) */ ALL
WHEN (DEPARTMENT_ID=40) THEN
INTO emp_40 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,DEPARTMENT_ID)
VALUES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,DEPARTMENT_ID)
LOG ERRORS REJECT LIMIT UNLIMITED
WHEN (DEPARTMENT_ID=50) THEN
INTO emp_50 (EMPLOYEE_ID,EMAIL,PHONE_NUMBER,HIRE_DATE,DEPARTMENT_ID)
VALUES (EMPLOYEE_ID,EMAIL,PHONE_NUMBER,HIRE_DATE,DEPARTMENT_ID)
LOG ERRORS REJECT LIMIT UNLIMITED
ELSE
INTO emp_left_over (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
VALUES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
LOG ERRORS REJECT LIMIT UNLIMITED
SELECT /*+ PARALLEL(b 2) */ * FROM employees b ;

commit;

SELECT DEPARTMENT_ID,count(*) FROM emp_40
group by DEPARTMENT_ID order by DEPARTMENT_ID;

SELECT DEPARTMENT_ID,count(*) FROM emp_50
group by DEPARTMENT_ID order by DEPARTMENT_ID;

SELECT DEPARTMENT_ID,count(*) FROM emp_left_over
group by DEPARTMENT_ID order by DEPARTMENT_ID;

SELECT COUNT(*) FROM err$_emp_left_over emp_left_over ;
SELECT COUNT(*) FROM err$_emp_40 emp_40 ;
SELECT COUNT(*) FROM err$_emp_50 emp_50 ;

SELECT * FROM err$_emp_left_over emp_left_over ;