ORA-01652: unable to extend temp segment by 1024 in tablespace

You may get this error when you create any object due to the tablespace is full where the objects is beign created. The possible solution.
(1). Extend the tablespace currently beign used by the objects (Schema).
To extend you should need the dba priveleges. Even if you are granted the neccessary privileges , you may have to bounce the db to reflect your changes.

(2) As a developer you may try this.
(a). Find out the autoextendable tablespaces
SELECT TABLESPACE_NAME
FROM DBA_DATA_FILES
WHERE autoextensible='YES';

(b) Apply the table space in the create table syntax

create table test TABLESPACE
as
select *
from all_objects;

How to connect to a Db via sqlplus without editing tnsnames.ora?

I've not been given admin privilege to edit the tnsnames.ora file in my pc.I had been facing difficulties to connect to other dbs which are not in my tnsnames.ora file. So two methods given to overcome this >10g .(Toad has implemented this feature)
(1). Use,
sqlplus user_name/password@//host/service_name
Eg: > sqlplus scott/tiger@//192.168.161.66/orcl
you may get "ORA-12154: TNS:could not resolve the connect identifier specified"
Check u have added/uncommented following line in sqlnet.ora which is at \11.1.0\client_1\network\admin
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
(2). Use, Here it's not necessary to have above entry in sqlnet.ora file.
sqlplus scott/tigger@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.161.66)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(service_name=orcl)))'
Note: No line breaks, No spaces and No semicolon(;) at the end
Reference : Oracle Magazine July/August 2010

How to setup Apex in Oracle 10g Xe

I've been given a task to setup the Oracle apex to the Oracle 10g xe. So below are the steps to be followed.
(1). Installed Orcale 10g xe db. download it.
Let ORACLE_HOME be d:/oraclexe
(2). Download Apex. Extract it into d:/oraclexe , you could see the apex folder created.
i.e d:/oraclexe/apex
(3). Now,Install the apex as

cd d:/oraclexe/apex
d:/oraclexe/apex\> / sys as sysdba
sql>@apexins sysaux sysaux temp /i/

The above means:
@apexins tablespace_apex tablespace_files tablespace_temp images
Where: tablespace_apex is the name of the tablespace for the Oracle Application Express application user.
tablespace_files is the name of the tablespace for the Oracle Application Express files user.
tablespace_temp is the name of the temporary tablespace.
images is the virtual directory for Oracle Application Express images. To support future Oracle Application Express upgrades, define the virtual image directory as /i/.
(4). Set the images PATH for APEX (APEX_HOME is the folder where you have unzipped APEX; e.g d:\oraclexe)

cd d:/oraclexe/apex
d:/oraclexe/apex\> / sys as sysdba
sql> @apxldimg.sql APEX_HOME

(5).Set the Administrator password for APEX:

cd d:/oraclexe/apex
d:/oraclexe/apex\> / sys as sysdba
sql>@apxxepwd.sql password

(6). Restart your XE instance.
(7). Your new APEX is available at http://localhost:port/apex or for administration purposes at http://localhost:port/apex/apex_admin
Now you are done!!!

For more reference :
http://radu.cotescu.com/2009/05/24/how-to-install-oracle-application-express-312-on-oracle-database-10g-xe/
http://download.oracle.com/docs/cd/E14373_01/install.32/e13366/otn_install.htm#BEHGEFDI

Connecting SQL SERVER 2005 from SQL Developer 1.5.1

The Following Steps would explain the Setup

Step1: Download the "jtds-1.2.5-dist.zip" from http://sourceforge.net/projects/jtds/files/jtds/1.2.5/jtds-1.2.5-dist.zip/download

Step2: Open the Sql Developer
Tools -> Preferences -> DataBase ->Third Party JDBC Drivers
and add the "jtds-1.2.5.jar" which you have unzip from the step1. and OK.

Step3 : Right Click and select the "New Connection" at the "Connection" Node.
You could see sqlserver and sybase tabs have come. Click sqlserver tab. and fill the required
info. and click test. You may get the following error.
"I/O Error: SSO Failed: Native SSPI library not loaded. Check the java.library.path system property."
To overcome:
Copy the file 'jtds-1.2.2-dist\x86\SSO\ntlmauth.dll' from the unzipped JTDS and paste it at
sql_devloper_home\jdk\jre\bin.
Then restart SQL Developer.
Now you are done.
References :
http://www.oracle.com/technology/oramag/oracle/08-may/o38sql.html
http://www.dbforums.com/microsoft-sql-server/1612362-java-sql-sqlexception-i-o-error-sso-failed-native-sspi-library-not-loaded.html

File Transfering From Unix to Windows

Recently I've been given a task of finding errors in some script files.Hence I need to transfer the files to my pc.(i.e Unix to Windows). So as easy way to use the filezilla and just drag and dropping you could FTP. but I wanted to try by the command line too. The following are the commands.

open the command prompt in windows.

C:\job_file>ftp
ftp> open 169.226.1.101
Connected to 169.226.1.101.
220 169.226.1.101 FTP server (Version 4.1 Tue May 15 16:38:46 CDT 2001) ready.
User (169.226.1.101:(none)): test
331 Password required for test.
Password:*****
230 User test logged in.
ftp> pwd
257 "/home/job" is current directory.
ftp> get testjob.job
200 PORT command successful.
150 Opening data connection for testjob.job
688 bytes).226 Transfer complete.
ftp: 727 bytes received in 0.00Seconds 727000.00Kbytes/sec.
ftp>

Now the file has been tranferred and it exists at C:\job_file\

For more Reference: http://www.albany.edu/its/quickstarts/qs-ftp.html

How to Call a C program in PL/SQL?

For the DB installed in Linux platform
Compiled the C code and generated .so file
gcc -o calc_tax.so -shared calc_tax.c

Step1: Conn scott/tiger@orcl

Step2: CREATE OR REPLACE LIBRARY c_lib
AS
'/tmp/calc_tax.so';

Note : You should have CREATE LIBRARY privelege

Step3: CREATE OR REPLACE FUNCTION tax_amt(x BINARY_INTEGER)
RETURN BINARY_INTEGER
AS LANGUAGE C
LIBRARY c_lib
NAME "calc_tax";

calc_tax.c is given below
-----------------------------------------------------
calc_tax(n)

int n;

{

int tax;

tax = (n*8)/100;

return(tax);

}



main()

{

int tot_ord;

printf("Enter the Order Total ");

scanf("%d",&tot_ord);

printf("\n%d", calc_tax(tot_ord));

printf("\n\n");

}



Step4: SELECT tax_amt(10000)
FROM DUAL;

Fine Grained Access Control in Oracle 10g

Fine-Grained Access is also known as a virtual private database (VPD) because it implements row-level security, essentially giving user access to his or her own private database. Fine-grained means at the individual row level.

SYS_CONTEXT

SELECT sys_context('', '');
FROM dual;

System defined:
Eg:
Attribute_Name Attribute_Value
USERENV IP_ADDRESS
USERENV SESSION_USER
USERENV CURRENT_SCHEMA
USERENV DB_NAME
USERENV TERMINAL

To View:
SELECT sys_context(‘USER_ENV’,’IP_ADDRESS’)
FROM dual;

Create your own context by

CREATE OR REPLACE CONTEXT name_of_context
USING package_name;

Eg:
CREATE CONTEXT my_context
USING my_pack;

• At the time of the creation of the context, it is not necessary to exist the package.
• You should have the CREATE ANY CONTEXTprivilege.
• To view your context use, dba_context



Set the context using

DBMS_SESSION.SET_CONTEXT(‘context_name’,’attribute_name’,’attribute_value’);

Note: This has to be in a package body.

CREATE OR REPLACE PACKAGE my_pack
IS

procedure set_application_context;

END;

CREATE OR REPLACE PACKAGE BODY my_pack
IS

procedure set_application_context
IS
BEGIN
DBMS_SESSION.SET_CONTEXT('my_context','name','GR10’);
END;

END;

You may get “ORA-01031: insufficient privileges” error while running the above.
You should:
SQL> conn sys as sysdba
SQL> grant execute on dbms_session to imran;


And log in as

SQL> CONN imran/imran@orcl
SQL> execute my_pack.set_application_context;

SQL> SELECT sys_context(‘my_context’,’name’)
FROM dual;

SYS_CONTEXT('MY_CONTEXT','NAME')
-------------------------------------------
GR10

Real Scenario of implementing Fine Grained Access

Steps to be followed:
1. Set up a driving context.
2. Create the package associated with the context you defined in the step1. In the package:
a. Set the context.
b. Define the predicate.
3. Define the policy.
4. Set up a logon trigger to call the package at logon time and set the context.
5. Test the policy.



1. Set up a driving context.

CREATE CONTEXT user_group_context
USING emp_group_pack;

2. Create the package

CREATE OR REPLACE PACKAGE emp_group_pack
IS

procedure show_app_context;
procedure set_application_context;
Function the_predicate (p_schema Varchar2,p_name Varchar2)
Return varchar2;
END;



CREATE OR REPLACE PACKAGE BODY IMRAN.emp_group_pack
IS
vc_context_name CONSTANT varchar2(30) := 'USER_GROUP_CONTEXT';
vc_attribute_name CONSTANT varchar2(30) := 'GROUP_ID';

procedure set_application_context
IS
vc_user VARCHAR2(50);
BEGIN
SELECT user INTO vc_user
FROM dual;

DBMS_SESSION.SET_CONTEXT(vc_context_name,vc_attribute_name,user);
END;

procedure show_app_context
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(' Context Name:'||vc_context_name||' Context attribute:'||vc_attribute_name||
' Attribute Value:'||sys_context(vc_context_name,vc_attribute_name));

END;


Function the_predicate (p_schema Varchar2,p_name Varchar2)
Return varchar2
IS
vc_restriction VARCHAR2(100);
vc_context Varchar2(30) := SYS_CONTEXT(vc_context_name,vc_attribute_name);
BEGIN

IF vc_context like 'USER%' THEN
vc_restriction := ' GROUP_ID = '||SUBSTR(vc_context,5);
END IF;

RETURN vc_restriction;

END;

END;
/



3. Define the policy.

DBMS_RLS.ADD_POLICY( Object_schema IN VARCHAR2 := NULL,
Object_name IN VARCHAR2,
Policy_Name IN VARCHAR2,
Function_Schema IN VARCHAR2,
Policy_Function IN VARCHAR2,
Statement_Types IN VARCHAR2 := NULL,
Update_check IN BOOLEAN := FALSE,
Enable IN BOOLEAN := TRUE );


SQL> conn sys as sysdba
Enter password: ***
Connected.
SQL> BEGIN
2 DBMS_RLS.ADD_POLICY
3 ( 'IMRAN',
4 'EMP',
5 'GROUP_ID_POLICY',
6 'IMRAN',
7 'EMP_GROUP_PACK.THE_PREDICATE',
8 'SELECT,UPDATE,DELETE',
9 TRUE,
10 TRUE);
11 END;
12 /

To Drop the policy:

BEGIN
DBMS_RLS.DROP_POLICY
( 'IMRAN',
'EMP',
'GROUP_ID_POLICY'
);
END;


SQL> create user USER3
2 identified by USER3;

User created.

SQL> grant connect,resource to user3;

Grant succeeded.


SQL> conn sys/sys as sysdba
Connected.
SQL> create or replace trigger set_id_on_logon
2 after logon on database
3 BEGIN
4 imran.EMP_GROUP_PACK.set_application_context;
5 END;
6 /

Trigger created.

SQL> conn user1/user1

SQL> select *
2 from imran.emp;

EMP_ID NAME SAL GROUP_ID
---------- ------------------------------ ---------- ----------
200 aaa 5000 1
100 King 24000 1
101 Dehan 17000 1
110 Stepens 20500 1