Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as extoperSQL> set serveroutput on
SQL>
SQL> DECLARE
2 test VARCHAR2(20) := '';
3 BEGIN
4 IF test IS NULL
5 THEN
6 dbms_output.put_line(''''' is equal to NULL');
7 END IF;
8
9 IF test = ''
10 THEN
11 dbms_output.put_line(''''' is equal to ''''');
12 END IF;
13 END;
14 /
'' is equal to NULL
PL/SQL procedure successfully completed
SQL>
Permalink
2 Comments
That error occurs when failed to allocate an extent of the required number of blocks for a temporary segment in the tablespace indicated.
So we can this in two ways.
1.
Check the datafiles sizes..
SELECT * FROM DBA_DATA_FILES;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
1 D:\ORACLEXE\ORADATA\XE\USERS.DBF 4 USERS 104857600 12800 AVAILABLE
2 D:\ORACLEXE\ORADATA\XE\SYSAUX.DBF 3 SYSAUX 450887680 55040 AVAILABLE
3 D:\ORACLEXE\ORADATA\XE\UNDO.DBF 2 UNDO 94371840 11520 AVAILABLE
4 D:\ORACLEXE\ORADATA\XE\SYSTEM.DBF 1 SYSTEM 629145600 76800 AVAILABLE
Then resize your datafile or add a new datafile to current tablespace
Resizing Datafile :
ALTER DATABASE DATAFILE ‘c:\oracle\oradata\orabase\USERS.DBF’ RESIZE 200M;
Addind Datafile to existing Tablespace:
ALTER TABLESPACE USERS ADD DATAFILE ‘c:\oracle\oradata\orabase\USERS2.DBF’ SIZE 50M;
2.
Change the user’s default tablespace to a bigger one 
SELECT * FROM Dba_Users;
USERNAME USER_ID ACCOUNT_STATUS DEFAULT_TABLESPACE
1 ASD 36 OPEN SYSTEM
2 SYS 0 OPEN SYSTEM
3 SYSTEM 5 OPEN SYSTEM
4 ANONYMOUS 28 OPEN SYSAUX
ALTER USER asd DEFAULT TABLESPACE users;
SELECT * FROM Dba_Users;
USERNAME USER_ID ACCOUNT_STATUS DEFAULT_TABLESPACE
1 ASD 36 OPEN USERS
2 SYS 0 OPEN SYSTEM
3 SYSTEM 5 OPEN SYSTEM
4 ANONYMOUS 28 OPEN SYSAUX
References :
http://www.psoug.org/reference/tablespaces.html
http://www.psoug.org/reference/datafiles.html
Permalink
3 Comments
Warning,
If you user rownum like this,
SELECT *
FROM TABLE
WHERE rownum < 2
ORDER BY column
you do this : get the first row that readed from database, and sort the 1 row.
Correct :
SELECT *
FROM (SELECT *
FROM TABLE
ORDER BY COLUMN
)
WHERE rownum < 2
That means : sort the rows and get the first row.
Permalink
No Comments
SQL> set autotrace traceonly
SQL> SET serveroutput ON
–let’s create a table that contains country names and codes from apex environment
SQL> CREATE TABLE t_func AS select * from flows_020100.wwv_flow_countries t;
Table created.
–then write a query that searches brazil
SQL> SELECT * FROM t_func WHERE upper(country_name) LIKE ‘BRAZIL’;
Execution Plan
———————————————————-
Plan hash value: 175459743
—————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 146 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_FUNC | 1 | 146 | 2 (0)| 00:00:01 |
—————————————————————————-
Predicate Information (identified by operation id):
—————————————————
1 - filter(UPPER(”COUNTRY_NAME”) LIKE ‘BRAZIL’)
Note
—–
- dynamic sampling used for this statement
Statistics
———————————————————-
48 recursive calls
0 db block gets
12 consistent gets
1 physical reads
0 redo size
482 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
–then let’s create an function based index on column country_name
SQL> CREATE INDEX t_func_idx ON t_func(upper(country_name));
Index created.
–write the query again
SQL> SELECT * FROM t_func WHERE upper(country_name) LIKE ‘BRAZIL’;
Execution Plan
———————————————————-
Plan hash value: 3533916538
——————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
——————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 146 | 1 (0)| 00:00:01
| 1 | TABLE ACCESS BY INDEX ROWID| T_FUNC | 1 | 146 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_FUNC_IDX | 1 | | 1 (0)| 00:00:01 |
——————————————————————————————
Predicate Information (identified by operation id):
—————————————————
2 - access(UPPER(”COUNTRY_NAME”) LIKE ‘BRAZIL’)
Note
—–
- dynamic sampling used for this statement
Statistics
———————————————————-
28 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
482 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> spool off
In the first example, query made a FULL TABLE SCAN and cost was 2.
In the second example, our index worked and it made an INDEX RANGE SCAN by using T_FUNC_IDX and cost was 1. These tables that we query on are small tables. The difference will be increase in larger tables…
tested in Oracle XE Edition 10g Release 2 (10.2)
And Code :
SET serveroutput ON
set autotrace traceonly
alter session set QUERY_REWRITE_ENABLED=TRUE;
alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;
drop table t_func;
drop index t_func_idx;
CREATE TABLE t_func AS select * from flows_020100.wwv_flow_countries t;
SELECT * FROM t_func WHERE upper(country_name) LIKE ‘BRAZIL’;
CREATE INDEX t_func_idx ON t_func(upper(country_name));
SELECT * FROM t_func WHERE upper(country_name) LIKE ‘BRAZIL’;
A advised and different perspective to function based index is
http://oracle-unix.blogspot.com/2007/07/performance-tuning-how-to-make-like.html
Permalink
No Comments
–flush buffer cache and shared pool
alter system flush buffer_cache;
alter system flush SHARED_POOL;
=======================================================
–increase a column’s type value
CREATE TABLE a (aa VARCHAR(10));
INSERT INTO a VALUES (’sdfsdf’);
INSERT INTO a VALUES (’sdxxxf’);
INSERT INTO a VALUES (’sddsdf’);
ALTER TABLE a modify aa VARCHAR2(50);
=======================================================
–create a sequence
CREATE SEQUENCE my_table_seq
START WITH 1
INCREMENT BY 1
NOMAXVALUE
NOCYCLE;
=======================================================
–create a table
CREATE TABLE T_E_LOC_UPD_LOGS(
NLOG_ID NUMBER(7, 0) NOT NULL,
NSTART_LOGID NUMBER(10, 0) NOT NULL,
NFINISH_LOGID NUMBER(10, 0),
BSUCCESS NUMBER(1, 0) NOT NULL,
DLOG_DATE DATE NOT NULL,
NERROR_CODE NUMBER(6, 0),
VERROR_DESC VARCHAR2(500),
CONSTRAINT T_E_LOC_UPD_LOGS_PK PRIMARY KEY (NLOG_ID)
);
=======================================================
–create a directory
CREATE DIRECTORY oraload AS ‘c:\oraload\’;
GRANT READ,WRITE ON DIRECTORY oraload TO hr;
SELECT * FROM all_directories;
=======================================================
–grant access to any table to a schema
GRANT SELECT, INSERT, UPDATE, DELETE ON my_table TO hr;
=======================================================
–add primary key
ALTER TABLE my_table ADD ( CONSTRAINT my_table_PK PRIMARY KEY (LOG_ID));
=======================================================
–crate database link
– requires “xxxxxxxxx.xxxx” entry in TNSNAMES.ORA
CREATE DATABASE LINK my_link
CONNECT TO <<username>> IDENTIFIED BY <<password>>
USING ‘xxxxxxxxx.xxxx’;
SELECT * FROM all_db_links;
DROP DATABASE LINK my_link;
SELECT * FROM my_table@my_link;
=======================================================
–some tablespace options
SELECT * FROM User_Tablespaces;
ALTER TABLESPACE akdora AUTOEXTEND ON NEXT 30M MAXSIZE UNLIMITED;
Alter table FR_TBL_loc_upd_data storage (maxextents unlimited);
CREATE TABLESPACE
akdora
DATAFILE
‘/akdora.dbf’
size
300M
AUTOEXTEND ON
next 30M
MAXSIZE UNLIMITED;
ALTER TABLESPACE akdora NOLOGGING;
=======================================================
–ANALYZE Statement
http://www.ss64.com/ora/analyze.html
=======================================================
Permalink
2 Comments
I could not send a sequence next value directly as a parameter in a pl/sql block. Then,
begin
pr_insert_mytable
(
mytable_seq.NEXTVAL ,
xx,
yy,
0,
zz,
);
end;
Error: PLS-00357: Table,View Or Sequence reference mytable_seq.NEXTVAL not allowed in this context
Line: 301
Text: pr_insert_log(mytable_seq.NEXTVAL,
Error: PL/SQL: Statement ignored
Line: 301
Text: pr_insert_mytable(mytable_seq.NEXTVAL,
then we can set it the sequence to a variable then insert it
declare
ln_id number(10);
begin
select mytable_seq.NEXTVAL into ln_id
from dual;
pr_insert_mytable
(
ln_id,
xx,
yy,
0,
zz,
);
end;
Permalink
1 Comment
Last day, a mission is given to me about calling a web services by pl/sql. Firstly, i started to search ; “What is a web service?” The term Web services describes a standardized way of integrating Web-based applications using the XML, SOAP, WSDL and UDDI open standards over an Internet protocol backbone. Actually we can summarize it;
Web Services can convert your applications into Web-applications.
By using Web services, your application can publish its function or message to the rest of the world.
Web Services can be used by other applications.
With Web services your accounting department’s Win 2k servers can connect with your IT supplier’s UNIX server.
The basic Web Services platform is XML + HTTP.
Web services uses XML to code and decode your data and SOAP to transport it.
We have some input parameters and make a request. Then, we get a response from it with output parameters. All this syntax is in XML format. So, we will use XMLtype in our pl/sql package.
Let’s start to write a soap access package or we can use this package (www.oracle-base.com/dba/miscellaneous/soap_api.sql)
Let’s assume we have a function “myFunction” in this page http://www.mywebservice.com/web Then,start to write our code
CREATE OR REPLACE PACKAGE pkg_my_webservice IS
FUNCTION call_myfuntion
(
vp_parameter1 VARCHAR2,
vp_parameter2 VARCHAR2
) RETURN VARCHAR2;
END pkg_my_webservice;
/
CREATE OR REPLACE PACKAGE BODY pkg_my_webservice IS
vg_funciton_fnc VARCHAR2(256) := ‘myFunction’;
vg_ws_address VARCHAR2(255) := ‘http://www.mywebservice.com/web’;
FUNCTION call_myfuntion(
–lets assume that it inputs two parameters called string1, string2
vp_parameter1 VARCHAR2,
vp_parameter2 VARCHAR2)
RETURN VARCHAR2 AS
ol_req soap_api.t_request;
ol_resp soap_api.t_response;
BEGIN
– we initilize a new request
ol_req := soap_api.new_request(vg_funciton_fnc,
‘xmlns=”‘ || vg_ws_address || ‘”‘);
– we started to add parameters
soap_api.add_parameter(ol_req,
’string1′,
‘partns:string’,
vp_parameter1);
soap_api.add_parameter(ol_req,
’string2′,
‘partns:string’,
vp_parameter1);
– we call the web service
ol_resp := soap_api.invoke(ol_req,
vg_ws_address,
vg_funciton_fnc);
– we get back the results
RETURN soap_api.get_return_value(ol_resp,
‘result’, — result tag name
‘xmlns:m=”‘ || –can be change as “xmlns:n1″
vg_ws_address || ‘”‘);
END call_myfuntion;
END pkg_my_webservice;
/
pkg_my_webservice_blog.txt
You may need to change some tag attributes. The main basic function syntax is like this. If you have more than one return xml tags, then you need to call the get_return_value function as much as result attributes. You may also store the results in a pl/sql type and return it back.
There are also other ways to do this. You may also use oracle’s “utl_dbws” package, however “utl_http” is more faster than it. You can test it.
In my next post, i am gonna explain soap_api package in more details.
Permalink
1 Comment
--IN THIS DEMO, WE WILL CALCULATE THE REQUIRED SPACE FOR AN INDEX THAT CONTAINING 3 COLUMNS OF A TABLE
–TOTAL BLOCK SIZE = TOTOL BLOCKS - UNUSED BLOCKS
–I FOUND THIS EXAMPLE FROM “EXPERT ONE-TO-ONE ORACLE” BOOK OF “TOMS KYTE”
–WE CAN SEE THAT TO COMPRESS A INDEX GAINS US MORE FREE SPACE SQL>
SQL> set serverout on
SQL> create or replace procedure show_space
2 (
3 p_segname in varchar2,
4 p_owner in varchar2 default user,
5 p_type in varchar2 default ‘TABLE’,
6 p_partition in varchar2 default NULL
7 )
8 as
9 l_free_blks number;
10 l_total_blocks number;
11 l_total_bytes number;
12 l_unused_blocks number;
13 l_unused_bytes number;
14 l_LastUsedExtFileId number;
15 l_LastUsedExtBlockId number;
16 l_last_used_block number;
17
18 procedure p( p_label in varchar2, p_num in number )
19 is
20 begin
21 dbms_output.put_line( rpad(p_label,40,’.') || p_num );
22 end;
23 begin
24
25 dbms_space.free_blocks
26 ( segment_owner => p_owner,
27 segment_name => p_segname,
28 segment_type => p_type,
29 partition_name => p_partition,
30 freelist_group_id => 0,
31 free_blks => l_free_blks );
32
33 dbms_space.unused_space
34 ( segment_owner => p_owner,
35 segment_name => p_segname,
36 segment_type => p_type,
37 partition_name => p_partition,
38 total_blocks => l_total_blocks,
39 total_bytes => l_total_bytes,
40 unused_blocks => l_unused_blocks,
41 unused_bytes => l_unused_bytes,
42 last_used_extent_file_id => l_LastUsedExtFileId,
43 last_used_extent_block_id => l_LastUsedExtBlockId,
44 last_used_block => l_last_used_block );
45
46 p( ‘Free Blocks’, l_free_blks );
47 p( ‘Total Blocks’, l_total_blocks );
48 p( ‘Total Bytes’, l_total_bytes );
49 p( ‘Unused Blocks’, l_unused_blocks );
50 p( ‘Unused Bytes’, l_unused_bytes );
51 p( ‘Last Used Ext FileId’, l_LastUsedExtFileId );
52 p( ‘Last Used Ext BlockId’, l_LastUsedExtBlockId );
53 p( ‘Last Used Block’, l_last_used_block );
54
55 end;
56 /
Procedure created.
SQL>
SQL>create table table123 as select * from all_objects;
Table created.
SQL>
SQL> create index t_idx on table123(owner,object_type,object_name);
Index created.
SQL> exec show_space(’T_IDX’,user,’INDEX’);
Free Blocks………………………..0
Total Blocks……………………….80
Total Bytes………………………..655360
Unused Blocks………………………7
Unused Bytes……………………….57344
Last Used Ext FileId………………..1
Last Used Ext BlockId……………….43265
Last Used Block…………………….1
PL/SQL procedure successfully completed.
–TOTAL BLOCK SIZE = 80 - 7 = 73
SQL>
SQL> drop index t_idx;
Index dropped.
SQL> create index t_idx on table123(owner,object_type,object_name) compress 1;
Index created.
SQL> exec show_space(’T_IDX’,user,’INDEX’);
Free Blocks………………………..0
Total Blocks……………………….64
Total Bytes………………………..524288
Unused Blocks………………………1
Unused Bytes……………………….8192
Last Used Ext FileId………………..1
Last Used Ext BlockId……………….43249
Last Used Block…………………….7
PL/SQL procedure successfully completed.
–TOTAL BLOCK SIZE = 64 - 1 = 63
SQL>
SQL> drop index t_idx;
Index dropped.
SQL> create index t_idx on table123(owner,object_type,object_name) compress 2;
Index created.
SQL> exec show_space(’T_IDX’,user,’INDEX’);
Free Blocks………………………..0
Total Blocks……………………….56
Total Bytes………………………..458752
Unused Blocks………………………4
Unused Bytes……………………….32768
Last Used Ext FileId………………..1
Last Used Ext BlockId……………….42897
Last Used Block…………………….4
PL/SQL procedure successfully completed.
–TOTAL BLOCK SIZE = 56 - 4 = 52
SQL> spool off
–FINALLY WE CAN OBVIOUSLY SEE THAT WE CAN HOLD THE SAME INDEX DATA IN LESS MEMORY SPACE BY COMPRESSING IT..
Permalink
No Comments
