How many elements does the IN clause support

I had taken this quetions and reply from Best Practice PL/SQL
with Steven Feuerstein available at OTN.

How many elements does the IN clause support?
Asked by Alejandra Vergara from Somewhere in South America on Tuesday, July 27, 2004

Question:
I have a query of type select * from table1 where cod_table in ( 1,2,...,300). Does the IN clause in Oracle9i Database support more than 256 elements?

Answer:
Alejandra, in Oracle9i, you can have up to 1,000 elements in your IN list.

I take it you are worried about errors like this:

ORA-01795: maximum number of expressions in a list is 1000

And that is a valid concern....we might ask the same question about Oracle Database 10g and then the future releases as well. One way to get the answer is to check Oracle documentation, but an even better way is to check by running code.

I answered your question to my satisfaction by creating the following program:


CREATE OR REPLACE PROCEDURE test_in_limit (
table_in IN VARCHAR2
,int_column_in IN VARCHAR2
,limit_in IN PLS_INTEGER
)
IS
v_sql VARCHAR (32767);
i integer;
BEGIN
v_sql :=
'SELECT ' || int_column_in || ' FROM ' || table_in ||
' WHERE ' || int_column_in || ' IN (1';

FOR indx IN 2 .. limit_in
LOOP
v_sql := v_sql || ',' || indx;
END LOOP;

v_sql := v_sql || ')';

EXECUTE IMMEDIATE v_sql into i;
END test_in_limit;
/

(Source code available here.) I then ran it as follows without any error on Oracle9i:

BEGIN
test_in_limit ('sg_script', 'id', 1000);
END;
/

Yet here is what happened with 1,001:

SQL> BEGIN
2 test_in_limit ('sg_script', 'id', 1001);
3 END;
4 /
BEGIN
*
ERROR at line 1:
ORA-01795: maximum number of expressions in a list is 1000
ORA-06512: at "QNXO_DEV.TEST_IN_LIMIT", line 24
ORA-06512: at line 2

Running this program on Oracle Database 10g has identical results.

External Tables

Benefit of using External Tables is you can alleviates the need for staging tables and redundant flat file loading as the file expands.

1.First you need to diagnose which character you can able to define as a delimited file.
2.CREATE DIRECTORY data_dir as 'c:\test';

3.CREATE DIRECTORY log_dir as 'c:\test\log';

4.
CREATE TABLE EXTERNAL_FILE_READ
(
EMPNO NUMBER(5),
EMP_NAME VARCHAR2(80),
DEPT_ID NUMBER(5)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY data_dir
ACCESS PARAMETERS
(
RECORDs DELIMITED BY NEWLINE CHARACTERSET US7ASCII
BADFILE log_dir:'emp_%p.bad'
LOGFILE log_dir:'emp_%p.log'
FIELDS TERMINATED BY "|"LDRTRIM
)
location
('emp.csv')
)
REJECT LIMIT UNLIMITED NOPARALLEL
/
5.select * from external_file_read;
more information on external table you can read Database Utilities guide of oracle documentation.

Encrypting Data Travell withing or outside Network Through Oracle Advanced Security Option.

Data Transfer between network in plain text through Advance Security you can encrypt it.

You must need to configure oracle server and client.
You can do this through GUI tool.
But its also simple to do through without GUI. so below I had mention steps without GUI option.

You can use EtherDetect tool to check data traveling between Server and client to check this.

Steps at sever side
1.You need to edit sqlnet.ora which you will find in oracle home, network,admin folder (directory).
2.Add below three parameters.
SQLNET.ENCRYPTION_TYPES_SERVER = (3DES168)
SQLNET.ENCRYPTION_SERVER = accepted
SQLNET.CRYPTO_SEED= retquewruq

You can set any text in crypto_seed.

Only if we can set server side this will not work mean text will not be encrypted. You must need to specify at client side sqlnet.ora file.

3.Add below three parameters which you will find at same place.
SQLNET.ENCRYPTION_TYPES_CLIENT = (3DES168)
SQLNET.ENCRYPTION_CLIENT = requested
SQLNET.CRYPTO_SEED= sdfhasfasfha

We had set encryption_client parameter to requested means during the handshake between client and server. Client will request to sever for encryption and we had set at server to be accepted so server will accept encryption.

You can find more information on parameter at oracle documentation at oracle technology site in Oracle Advance Security Administrator’s guide.

My new wrist wach

Brought new wrist watch on Independence Day.

Drop Constraints.

Once i had faced problem in one table which have more than 287 duplicate constraints.

So i had buid one solution as below.

I had create one script and saved in c:\ REMOVECONSTRAINT.sql.

Contains of the script REMOVECONSTRAINT.sql as below.
SET HEADING OFF
SPOOL C:\DROPCONSTRAINT.SQL
select 'alter table 'OWNER'.'table_name' drop constraint 'constraint_name' cascade;'
from dba_constraints where owner=UPPER('&owner') and table_name=UPPER('&table_name')
/
SPOOL OFF;
@C:\DROPCONSTRAINT.SQL

if you go through with script you come to know it will generate dropconstraint.sql and execute it.

This script will remove all constraint from table.
After this you need to Re-create constraints your constraints.

Creating temporary tables

Temporary tables are created using the global temporary clause

Create global temporary table Hr.employees_temp
As select * from hr.employees;

Use of Temporary Tables

• Tables retain data only for the duration of a transacton or session.
• DML locks are not acquired on the data
• Can create indexes,views and triggers on temporary tables
  

You can import and export the definition but you can able to import and export the data.

Demonstration

Create global temporary table tt1
(lname varchar2(25), fname varchar2(25),empno number) on commit delete rows;

desc tt1;

select count(*) from tt1;

insert into tt1 values (‘thaker’,’nikunj’,1);

select * from tt1;

commit;

select * from tt1;

create global temporary table tt2
(lname varchar2(25), fname varchar2(25),empno number) on commit preserve rows;

data reside in the table will remain in the table after commit but logs are remove of this tables on structure of the table will resides.

Select table_name,temporary from user_tables Where table_name like ‘TT%’;

Drop table tt1;

Drop table tt2;

This will gives you error because it contains row because of the table we had created with on commit preserve rows;

Because of preserve rows row will reside in the table till session is open.

So we reconnet with that user and try to

select count(*) from tt2;

there is no data now we can remove this tables.

MOVE DATA FILE FROM X LOCATION TO Y LOCATION

The section offers some procedures for renaming and relocating datafiles in a single tablespace. You must have the ALTER TABLESPACE system privilege to rename datafiles of a single tablespace.

1. The following query of the data dictionary view DBA_DATA_FILES lists the datafile names and respective sizes (in bytes) of the users tablespace:

2. SELECT FILE_NAME, BYTES FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'USERS';

3. Take the tablespace containing the datafiles offline, or shut down the database and restart and mount it, leaving it closed. Either option closes the datafiles of the tablespace.

ALTER TABLESPACE USERS OFFLINE NORMAL;

/*ALTER DATABASE DATAFILE 'C:\ORACLE\ORADATA\NIKUNJ\USERS01.DBF' OFFLINE NORMAL;*/

4. Copy the datafiles to their new locations and rename them using the operating system.

COPY C:\ORACLE\ORADATA\NIKUNJ\USERS01.DBF TO C:\ORACLE\ORADATA\USERS01.DBF

5. Rename the datafiles within Oracle. The datafile pointers for the files that make up the users tablespace, recorded in the control file of the associated database, must now be changed from the old names to the new names.

If the tablespace is offline but the database is open, use the ALTER TABLESPACE ... RENAME DATAFILE statement. If the database is mounted but closed, use the ALTER DATABASE ... RENAME FILE statement.

ALTER TABLESPACE users RENAME DATAFILE 'C:\ORACLE\ORADATA\NIKUNJ\USERS01.DBF' TO 'C:\ORACLE\ORADATA\USERS01.DBF';

6. Bring the tablespace online, or open the database. If the users tablespace is offline and the database is open, bring the tablespace back online. If the database is mounted but closed, open the database.

ALTER TABLESPACE USERS ONLINE;

/*ALTER DATABASE DATAFILE 'C:\ORACLE\ORADATA\USERS01.DBF' ONLINE;*/

7. Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.

How to move system tablespace datafile

You can use one of two techniques as below.

1. Shutdown, move the file, re-create the control file, and startup.
2. Shutdown, stat up in mount mode, move them, and then open the database.

Here I am demonstrating 2^nd option because its easy.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>startup mount;

ORACLE instance started.

System Global Area 122754516 bytes

---

---

---

Database mounted.

SQL> !copy ‘c:\oracle\ora92\oradata\nikunj\system.dbf’ to ‘c:\oracle\ora92\oradata\system.dbf’

SQL>alter database rename file ‘c:\oracle\ora92\oradata\nikunj\system.dbf’ to ‘c:\oracle\ora92\oradata\system.dbf’;

Database altered.

SQL>alter database open;

Database altered.

Backup your control file again, and that’s it.

How to run Oracle listener on non-default port

You can able to run Oracle listener on non default oracle port.
open listener.ora file you can find that in oracle_home/network folder

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1577))
)
)
)

change port=

then open tnsnames.ora in the same directory.
test =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1577))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)

mention same port no.

Now oracle listen on non-default oracle port.

Also you can run multiple listener for one database.
change your listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1577))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1521))
)
)
)


I had asked thomas kyte for proc and cros there input as below.
Hi Tom,

If two listener on two port i.e. 1521 and 1522 are listening for one (X
database) database.

What are the pros and cros if one database have 2 listener and have 1 listener ?

Regards,
Nikunj

Followup:
pros - extreme cases.

cons - extra work, maintainance, monitoring.

if and only if a single listener was getting so nailed, hit so hard, that it
built up a queue of requests and started failing the requests because the queue
was too long would I consider another listener.

And even then, it would likely be on another machine, for another instance,
using the same database - if you are nailing a listener that hard....

Worksheet: Windows Server 2003 default services

Click above link.
Microsoft has made several key changes to Windows Server 2003. One of the best ways for admins to evaluate these changes is to examine the core services of the OS. This Excel worksheet breaks down the default services installed in Windows Server 2003.

TO RUN A COMMAND FROM ORACLE FOR ORACLE 9I

/* RUN BELOW SCRIPT LOGIN WITH SYS ACCOUNT */
begin
dbms_java.grant_permission
('TIMETRACK',
'java.io.FilePermission',
'C:\WINNT\*',
'execute');
dbms_java.grant_permission
('TIMETRACK',
'java.lang.RuntimePermission',
'*',
'writeFileDescriptor' );
end;
/
/* RUN BELOW SCRIPT LOGIN WITH APPLICATION USER */
create or replace and compile
java source named "Util"
as
import java.io.*;
import java.lang.*;
public class Util extends Object
{
public static int RunThis(String[] args)
{
Runtime rt = Runtime.getRuntime();
int rc = -1;
try
{
Process p = rt.exec(args[0]);
int bufSize = 4096;
BufferedInputStream bis =
new BufferedInputStream(p.getInputStream(), bufSize);
int len;
byte buffer[] = new byte[bufSize];
// Echo back what the program spit out
while ((len = bis.read(buffer, 0, bufSize)) != -1)
System.out.write(buffer, 0, len);
rc = p.waitFor();
}
catch (Exception e)
{
e.printStackTrace();
rc = -1;
}
finally
{
return rc;
}
}
}
/

create or replace
function RUN_CMD( p_cmd in varchar2) return number
as
language java
name 'Util.RunThis(java.lang.String[]) return integer';
/
create or replace procedure RC(p_cmd in varchar2)
as
x number;
begin
x := run_cmd(p_cmd);
end;
/
variable x number;
set serveroutput on
exec dbms_java.set_output(100000);
exec :x := run_cmd('net send nikunj test');
10514.ksh
10565.sql
...
xxx.dat
xxx.dbf
zz