Monday 15 September 2008

How to generate External Table from sql loader


In many cases we might want to use external table to load data rather than Sql*Loader because in external table we can load data parallel. In this post I will show how we can generate external table easily with help of sql loader. With this method we can easily get rid of writing too many codes for external tables.

Suppose my data file is 3.txt which is under C drive on my windows machine and contains a single record,
1, momin

The next step is to create a control file for SQL loader. I named it control.ctl made it as follows,
LOAD DATA
infile 'c:\3.txt'
into table std
fields terminated by ','
(id , name)

Now invoke the Sql loader as below. Note that addition word that need is external_table=generate_only. With this keyword only externa table will be created but will not load any data actually on table.

sqlldr user_id/password control=c:\control.ctl.txt log=5.txt external_table=generate_only

After invoking I open the log file and important contents from logfile is,

CREATE DIRECTORY statements needed for files
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS 'C:\'
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS 'c:\'


CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_STD"
(
"ID" NUMBER,
"NAME" VARCHAR2(10)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8MSWIN1252
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'3.bad'
LOGFILE 'c:\5.txt_xt'
READSIZE 1048576
FIELDS TERMINATED BY "," LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"ID" CHAR(255)
TERMINATED BY ",",
"NAME" CHAR(255)
TERMINATED BY ","
)
)
location
(
'3.txt'
)
)REJECT LIMIT UNLIMITED


INSERT statements used to load internal tables:
------------------------------------------------------------------------
INSERT /*+ append */ INTO STD
(
ID,
NAME
)
SELECT
"ID",
"NAME"
FROM "SYS_SQLLDR_X_EXT_STD"


statements to cleanup objects created by previous statements:
------------------------------------------------------------------------
DROP TABLE "SYS_SQLLDR_X_EXT_STD"
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000

Drop Temporary Tablespace Hangs

Problem Description
The DROP temporary tablespace operations take long time and in fact it hangs. If you take a 10046 trace of the session it shows "enqueue" wait.

Cause of The Problem In the section http://hemora.blogspot.com/2008/09/operation-that-require-sort-area-or.html I discussed about the operation that needs sort space. Whenever an operation is using sort space an entry is found in the v$sort_usage. After the operation finishes entry from $sort_usage vanishes. But dead connections (while running a query) may leave entries in v$session (status inactive) and in v$sort_usage. The query about the users who is Performing Sort operation in Temp Segments can be found in http://hemora.blogspot.com/2008/09/information-about-temporary-segments.html
Solution of The Problem
1.Create a new temporary tablespace and assign all users to this new tablespace. You can easily do this task on unix system as,
CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE '/oradata2/temp02.dbf' SIZE 100m;
Change it for all by
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;

2.Find out the all the sessions that are not active and have an entry in V$sort_usage.
You can do it by,
SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
a.username,a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr;

3.Kill those session.
Using
alter system kill session 'SID_NUMBER, SERIAL#NUMBER'; kill those session that are not being used actually.
where SID_NUMBER and SERIAL#NUMBER is found in step 2.

4. Now dropping the previous tablespace
DROP TABLESPACE previous_temp_tbs;

The operation that require sort area or Temporary Tablespace


Whenever a sort occurs within a database it needs sort area. Primarily memory area is used to sort. If there is not sufficient memory then it is needed temporary segments where database writes data in order to sort. There are several operation which needs sort space. They are,

1)Index creation.
The CREATE INDEX statement causes the server process to sort the index values before building the tree. After the sort a final index is built in the tablespaces by using a temporary segment.

2)ORDER BY or GROUP BY clauses of SELECT statements.

The server process must sort on the values in the ORDER BY or GROUP BY clauses.

3)DISTINCT values of SELECT statements.

For the DISTINCT keyword, the data is at first sorted in order to eliminate duplicates.

4)UNION, INTERSECT or MINUS operations.

Servers need to sort the tables they are working on to eliminate duplicates.

5)Sort-Merge joins.
If no index is available, an equivalent-join request needs to perform full table scans and sort each row source separately. After that, the sorted sources are merged together, combining each row from one source with each matching row of the other source.

6)Analyze command execution.
The Analyze command sorts the data to provide summarized information.

7)Various SQL Statements.
The CREATE PRIMARY KEY CONSTRAINT, ENABLE CONSTRAINT, and CREATE TABLE statements require sort segment.

8)CREATE TABLE AS SELECT.
The creation of a new table can start as a temporary segment if MINEXTENTS is larger than 1 or when using the statement CREATE TABLE AS SELECT.

Information about Temporary Segments.


A)The users who is Performing Sort operation in Temp Segments:
--------------------------------------------------------------------
SQL> SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,a.username,a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;


TABLESPACE SEGFILE# SEGBLK# BLOCKS SID SERIAL# USERNAME OSUSER STATUS
----------- ------------- ---------- ---------- ---------- ---------- ------ ------ -------
TEMP 201 217865 748928 277 1185 PROD7 oracle ACTIVE


B)Information about Tablespace Containing sort Segments.

SQL> SELECT tablespace_name, extent_size, total_extents, used_extents,
free_extents, max_used_size
FROM v$sort_segment;


TABLESPACE_NAME EXTENT_SIZE TOTAL_EXTENTS USED_EXTENTS FREE_EXTENTS MAX_USED_SIZE
------------------------------- ----------- ------------- ------------ ------------ -------------
TEMP 128 11896 5851 6045 11322

Here,
Extent_size : size of one extent, in number of Oracle blocks
Total_extents: total number of extents in the segment (free or in use)
Used_extents : total number of extents currently in use
Free_extents : total number of extents currently marked as free
Max_used_size: maximum number of extents ever needed by an operation

C)If you want to keep interest of how much space is used in temporary segments then query as
,

SELECT EXTENT_SIZE*DB_BLOCK_SIZE*USED_EXTENTS/1024/1024/1024 "Space used in GigaByte" FROM v$sort_segment ;
If your database block size is 8192 (Query from select value from v$parameter where NAME='db_block_size';) then the space used by temp segments is ,

SQL>SELECT EXTENT_SIZE*8192*USED_EXTENTS/1024/1024/1024 "Space used in GigaByte" FROM v$sort_segment;
Space used in GigaByte
----------------------
11.6181641

From V$SORT_SEGMENT you can also query like,
SQL> SELECT TABLESPACE_NAME,EXTENT_SIZE,TOTAL_EXTENTS,USED_EXTENTS,FREE_EXTENTS,MAX_USED_BLOCKS,TOTAL_BLOCKS from V$SORT_SEGMENT;

TABLESPACE EXTENT_SIZE TOTAL_EXTENTS USED_EXTENTS FREE_EXTENTS MAX_USED_BLOCKS TOTAL_BLOCKS
---------- ----------- ------------- ------------ ------------ --------------- ------------
TEMP 128 11896 4526 7370 1449216 1522688


D)In order to know SQL ID and which type of Sort Segment issue,

SQL>SELECT USERNAME, SQLADDR,SQL_ID,TABLESPACE,SEGTYPE,EXTENTS,BLOCKS FROM V$TEMPSEG_USAGE;


USERNAME SQLADDR SQL_ID TABLESP SEGTYPE EXTENTS BLOCKS
------------ ---------------- ------------- ------- --------- ---------- ----------
SYSTEM 00000004129E28C0 b058ymxj1rvkg TEMP LOB_DATA 1 128
PROD7 0000000412E1C9F8 1t1v0wvyzwzuj TEMP SORT 5193 664704


E)To know which SQL is is using Temporary Segments query,
SQL> SELECT s.SQL_TEXT, t.USERNAME,t.TABLESPACE,t.SEGTYPE,t.BLOCKS,t.EXTENTS from V$SQL s, V$TEMPSEG_USAGE t WHERE t.SQL_ID=s.SQL_ID;

SQL_TEXT
------------------------------------------------------------------------------------
-------------------------------------------------------------
USERNAME TABLESP SEGTYPE BLOCKS EXTENTS
------------------------------ ------- --------- ---------- ----------
SELECT sql_id,sql_text from v$sql WHERE sql_id in (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10)
SYSTEM TEMP LOB_DATA 128 1

select * from user_activity order by ACTION,COOKIE_INFO
PROD7 TEMP SORT 36736 287

Thursday 11 September 2008

Create Read only user for a Schema

One thing you need to remember before read this post is there is no easy or shortcut way to make a read only user of another schema. Like grant select on username to another_username- there is no such single command like this. However you may have several alternatives to make read only user for a schema.

I will demonstrate the procedure with examples to make a read only user for a schema. In the example I will make devels user which will have read only permission on prod schema.
Let's start by creating PROD user.
SQL> CREATE USER PROD IDENTIFIED BY P;
User created.

SQL> GRANT DBA TO PROD;
Grant succeeded.

SQL> CONN PROD/P;
Connected.

SQL> CREATE TABLE PROD_TAB1 ( A NUMBER PRIMARY KEY, B NUMBER);
Table created.

SQL> INSERT INTO PROD_TAB1 VALUES(1,2);
1 row created.

SQL> CREATE TABLE PROD_TAB2(DATE_COL DATE);
Table created.

SQL> CREATE OR REPLACE TRIGGER PROD_TAB2_T AFTER INSERT ON PROD_TAB1
BEGIN
INSERT INTO PROD_TAB2 VALUES(SYSDATE);
END;
/
Trigger created.

SQL>CREATE VIEW A AS SELECT * FROM PROD_TAB2;

View created.


Method 1: Granting Privilege Manually


Step 1: Create devels user
SQL> CREATE USER DEVELS IDENTIFIED BY D;
User created.

Step 2: Grant only select session and create synonym privilege to devels user.
SQL> GRANT CREATE SESSION ,CREATE SYNONYM TO DEVELS;
Grant succeeded.

Step 3:Make script to grant select privilege.
$vi /oradata2/script.sql
SET PAGESIZE 0
SET LINESIZE 200
SET HEADING OFF
SET FEEDBACK OFF
SET ECHO OFF
SPOOL /oradata2/select_only_to_prod.sql
@@/oradata2/select_only_script.sql
SPOOL OFF


This script will run the /oradata2/select_only_script.sql and generate a output script /oradata2/select_only_to_prod.sql which need to be run in fact.

Step 4:
Prepare the /oradata2/select_only_script.sql script which will work as input for /oradata2/script.sql file.

$vi /oradata2/select_only_script.sql
SELECT 'GRANT SELECT ON PROD.' ||TABLE_NAME || ' TO DEVELS;' FROM DBA_TABLES WHERE OWNER='PROD';
SELECT 'GRANT SELECT ON PROD.' ||VIEW_NAME || ' TO DEVELS;' FROM DBA_VIEWS WHERE OWNER='PROD';


Step 5:
Now execute the /oradata2/script.sql which will in fact generate scipt /oradata2/select_only_to_prod.sql.
SQL> @/oradata2/script.sql
GRANT SELECT ON PROD.PROD_TAB1 TO DEVELS;
GRANT SELECT ON PROD.PROD_TAB2 TO DEVELS;


Step 6:
Execute the output script select_only_to_prod.sql which will be used to grant read only permission of devels user to prod schema.
SQL> @/oradata2/select_only_to_prod.sql

Step 7:
Log on devels user and create synonym so that the devels user can access prod's table without any dot(.). Like to access prod_tab2 of prod schema he need to write prod.prod_tab2. But after creating synonym he simply can use prod_tab2 to access devels table and views.
To create synonym do the following,

SQL>CONN DEVELS/D;

SQL>host vi /oradata2/script_synonym.sql
SET PAGESIZE 0
SET LINESIZE 200
SET HEADING OFF
SET FEEDBACK OFF
SET ECHO OFF
SPOOL /oradata2/synonym_to_prod.sql
@@/oradata2/synonym_script.sql
SPOOL OFF


SQL>host vi /oradata2/synonym_script.sql
SELECT 'CREATE SYNONYM ' ||TABLE_NAME|| ' FOR PROD.' ||TABLE_NAME||';' FROM ALL_TABLES WHERE OWNER='PROD';
SELECT 'CREATE SYNONYM ' ||VIEW_NAME|| ' FOR PROD.' ||VIEW_NAME||';' FROM ALL_VIEWS WHERE OWNER='PROD';

SQL>@/oradata2/script_synonym.sql
SQL>@/oradata2/synonym_to_prod.sql


Step 8: At this stage you have completed your job. Log on as devels schema and see,
SQL> select * from prod_tab1;
1 2

SQL> show user
USER is "DEVELS"

Only select privilege is there. So DML will throw error. Like,

SQL> insert into prod_tab1 values(4,3);

insert into prod_tab1 values(4,3)
*
ERROR at line 1:
ORA-01031: insufficient privileges

Method 2: Writing PL/SQL Code
This is script for table :

set serveroutput on
DECLARE
sql_txt VARCHAR2(300);
CURSOR tables_cur IS
SELECT table_name FROM dba_tables where owner='PROD';
BEGIN
dbms_output.enable(10000000);
FOR tables IN tables_cur LOOP
sql_txt:='GRANT SELECT ON PROD.'||tables.table_name||' TO devels';
execute immediate sql_txt;
END LOOP;
END;
/


This is the script for grant select permission for views.

DECLARE
sql_txt VARCHAR2(300);
CURSOR tables_cur IS
SELECT view_name FROM dba_views where owner='PROD';
BEGIN dbms_output.enable(10000000);
FOR tables IN tables_cur LOOP
sql_txt:='GRANT SELECT ON PROD.'||tables.view_name||' TO devels';
--dbms_output.put_line(sql_txt);
execute immediate sql_txt;
END LOOP;
END;
/



To create synonym on prod schema,
Log on as devels and execute the following procedure.
SQL>CONN DEVELS/D
SQL>
DECLARE
sql_txt VARCHAR2(300);
CURSOR syn_cur IS
SELECT table_name name FROM all_tables where owner='PROD'
UNION SELECT VIEW_NAME name from all_views where owner='PROD' ;
BEGIN dbms_output.enable(10000000);
FOR syn IN syn_cur LOOP
sql_txt:='CREATE SYNONYM '||syn.name|| ' FOR PROD.'||syn.name ;
dbms_output.put_line(sql_txt);
execute immediate sql_txt;
END LOOP;
END;
/


Method 3: Writing a Trigger


After granting select permission in either of two ways above you can avoid creating synonym by simply creating a trigger.

Create a log on trigger that eventually set current_schema to prod just after log in DEVELS user.

create or replace trigger log_on_after_devels
after logon ON DEVELS.SCHEMA
BEGIN
EXECUTE IMMEDIATE 'alter session set CURRENT_SCHEMA = prod';
END;
/


Related Documents

Drop User in Oracle

ORA-01940: Cannot drop a user that is currently connected

Create user in oracle

A user can do work in his schema with only Create Session Privilege.

How to get port number list of EM and isqlplus

You can get your portlist information of enterprise manger or isqlplus or others web services in the location of $ORACLE_HOME/install/portlist.ini.

On my system the output is,
bash-3.00$ cat /oracle/app/oracle/product/10.2.0/db_1/install/portlist.ini
iSQL*Plus HTTP port number =5560
Enterprise Manager Console HTTP Port (THERAP) = 1158
Enterprise Manager Agent Port (THERAP) = 3938
Enterprise Manager Console HTTP Port (arjudba) = 5500
Enterprise Manager Agent Port (arjudba) = 1830
Enterprise Manager Console HTTP Port (arju) = 5501
Enterprise Manager Agent Port (arju) = 1831

But note that the ports that are listed in portlist.ini file were assigned during the installation. This file is not updated if port numbers are changed after the installation.

Clusterware Installation fails at the end of CRS on Red Hat Linux 5

Clusterware Installation fails at the end of CRS on Red Hat Linux 5

Problem Description
While installing oracle clusterware on Red Hat Enterprise Linux 5.0 at then end of the installation of Cluster Ready Services OUI prompts for the $CRS_HOME/root.sh script to be run on all
of the nodes in the cluster. The fact is when the root.sh script is run on the last node in
the cluster, the script calls the VIPCA utility which fails in RHEL 5. The same failure occurs on Red Hat Enterprise Linux 5.0, and SUSE Linux Enterprise Linux 10.

Solution of The Problem
As a workaround to solve the problem before running the root.sh script on the last node in the cluster,
alter the $CRS_HOME/bin/vipca script commenting out lines 119 through 123:

arch=’uname -m’
# if [ "$arch" = "i686" -o "$arch" = "ia64" -o "$arch" = "x86_64" ]
# then
# LD_ASSUME_KERNEL=2.4.19
# export LD_ASSUME_KERNEL
# fi


After commenting out run root.sh and it should be able to call VIPCA successfully.
But don't comment out the line 118 that is arch=’uname -m’ as it is needed by the root.sh script to set the arch variable.