Category Archives: SQL

11g, Oracle, Oracle 10g, oracle 9i, SQL

How Oracle handles the distinct clause between 10gR1 and 10g R2

Leave a comment

Oracle has changed the algorithm used to perform “select distinct” operations from 10gR1 and earlier version to 10gR2 and new versions. This has caught a lot of custom application and script writers by surprise, when they depended on the ordering of returned values from 10gR1 and earlier versions. The problem is caused by the way in which the Oracle parser handles a “select distinct” operation: From Oracle 8 to 10gR1, the parser used a sort algorithm to determine distinct values and from 10gR2 and later, the parser uses a hash algorithm to determine distinct values. The end result is that 8 to 10gR1 will return “select distinct” operations in sorted order and new version will not. So to resolve this issue you must use the “order by” clause to guarantee values are returned in sorted order. This article shows the plan differences between the two parsed statements in Oracle 10gR1 and 10gR2.

1. Logon to Oracle 10gR1 and execute “select version from v$instance to show the exact version of this release.

SQL> select version from v$instance;

VERSION
—————–
10.1.0.3.0

SQL>

2. Create the table test1 with the following DDL and insert ten numerical values.

create table test1(v_number number(10));

declare

v_ct number:=1;

begin

while v_ct<10
loop
insert into test1(v_number) values(v_ct);

v_ct := v_ct+1;

end loop;
end;
/

3. Select from this table with “select distinct” clause, as you can see the values are returned in sorted order even though we did not specify the order by clause.

select distinct v_number from test1;

SQL> select distinct v_number from test1;

V_NUMBER
———-
1
2
3
4
5
6
7
8
9

9 rows selected.

SQL>

4. Execute explain plan on the previous statement and you can see that the parser is performing a sort to find all distinct values in the table, thus returning the values in sorted order.

SQL> explain plan for select distinct v_number from test1;

Explained.

SQL> @?/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
——————————————————————

Plan hash value: 1260548514

——————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————–
| 0 | SELECT STATEMENT | | 9 | 117 | 4 (25)| 00:00:01 |
| 1 | SORT UNIQUE | | 9 | 117 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TEST1 | 9 | 117 | 3 (0)| 00:00:01 |
——————————————————————–

Note

PLAN_TABLE_OUTPUT
———————————————————————
– dynamic sampling used for this statement

13 rows selected.

SQL>

5. Logon to 10gR2 server and select version from v$instance to show the exact RDBMS version.

SQL> select version from v$instance;

VERSION
—————–
10.2.0.4.0

SQL>

6. Create the table test1 and insert ten numerical values.

SQL> create table test1(v_number number(10));

Table created.

SQL>
SQL> declare
2
3 v_ct number:=1;
4
5 begin
6
7 while v_ct<10
8 loop
9 insert into test1(v_number) values(v_ct);
10
11 v_ct := v_ct+1;
12
13 end loop;
14 end;
15 /

PL/SQL procedure successfully completed.

SQL>

7. Perform select distinct operation on test1 table and note that the values are returned in an unsorted order.

SQL> select distinct v_number from test1;

V_NUMBER
———-
1
6
2
4
5
8
3
7
9

9 rows selected.

SQL>

8. Perform a explain plain on your “select distinct” statement and note that the sort operation has been replaced by a hash operation, thus values will not be returned in a sorted format.

SQL> explain plan for select distinct v_number from test1;

Explained.

SQL> @?/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
——————————————————————–

Plan hash value: 255531131

——————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————
| 0 | SELECT STATEMENT | | 9 | 117 | 4 (25)| 00:00:01 |
| 1 | HASH UNIQUE | | 9 | 117 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TEST1 | 9 | 117 | 3 (0)| 00:00:01 |
———————————————————————-

Note

PLAN_TABLE_OUTPUT
——————————————————————-
– dynamic sampling used for this statement

13 rows selected.

SQL>

9. The reason for the change in the parser’s behavior is speed of execution; the hash algorithm is much more efficient then the sort operation and thus execution time decreases. In order to have a guaranteed sorting of your value, you must us an “order by” clause.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g, oracle 9i, SQL

Recreating Oracle Public Database Link

Leave a comment

Oracle provides database links to create direct connections from one database instance to another. This feature can come in handy in the movement of small to moderate amounts of data between systems. In this article we will review the creation of the DDL to regenerate public database links defined within an Oracle database.

1. Connect to your Oracle database server and logon to SQL*PLUS.

MyLinux:>sqlplus ‘\ as sysdba’
SQL*Plus: Release 10.2.0.4.0 – Production on Tue Jul 3 19:21:09 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

2. Create a public database link from your database to another database to demonstrate generation of your DDL.

create public database link oracle11
connect to ljcatt identified by ljcatt
using ‘(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.103)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl11g)
)
)’;

SQL> create public database link oracle11
2 connect to ljcatt identified by ljcatt
3 using ‘(DESCRIPTION =
4 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.103)(PORT = 1522))
5 (CONNECT_DATA =
6 (SERVER = DEDICATED)
7 (SERVICE_NAME = orcl11g)
8 )
9 )’;

Database link created.

SQL>

3. Execute the following PL/SQL block to generate your DB Links DDL.

set serveroutput on
declare

v_pwd varchar2(30);

begin

for rec in(select * from dba_db_links where owner=’PUBLIC’)
loop

dbms_output.put_line(‘create public database link ‘||rec.db_link);
dbms_output.put_line(‘connect to ‘||rec.username||’ identified by ‘||'(password)’);
dbms_output.put_line(‘using ‘||””||rec.host||””||’;’);

end loop;
end;
/

SQL> set serveroutput on
SQL> declare
2
3 v_pwd varchar2(30);
4
5 begin
6
7 for rec in(select * from dba_db_links where owner=’PUBLIC’)
8 loop
9
10
11 dbms_output.put_line(‘create public database link ‘||rec.db_link);
12 dbms_output.put_line(‘connect to ‘||rec.username||’ identified by ‘||'(pass
word)’);
13 dbms_output.put_line(‘using ‘||””||rec.host||””||’;’);
14
15
16 end loop;
17 end;
18 /
create public database link ORACLE11.REGRESS.RDBMS.DEV.US.ORACLE.COM
connect to LJCATT identified by (password)
using ‘(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.103)(PORT =
1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl11g)

)
)’;

PL/SQL procedure successfully completed.

SQL>

4. Now you can use the DDL generated to move or recreate your public database link. NOTE: In Oracle 9i you can retrieve the password through the view sys.link$. However, in Oracle 10g and on, all passwords are encrypted, thus you must manually insert the actual password.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g, oracle 9i, SQL

Create Oracle Public Synonyms

Leave a comment

Oracle uses synonyms to point users to other schema objects when the user does not have direct access to that schema. This is generally accomplished by creating a synonym owned by public to a private schema object. Once the public synonym is created all users have access to the private schema object. In this article we will recreate the DDL for public synonyms referencing one private schema’s objects.

1. Connect to your Oracle database server and logon to SQL*PLUS.

MyLinux:>sqlplus ‘\ as sysdba’
SQL*Plus: Release 10.2.0.4.0 – Production on Tue Jul 15 02:57:41 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

2. Create public synonyms for all of the user SCOTT’s private tables.

Select ‘create public synonym ‘||table_name||’ for scott.’
||table_name||’;’ from dba_tables where owner=’SCOTT’;

SQL> Select ‘create public synonym ‘||table_name||’ for scott.’
2 ||table_name||’;’ from dba_tables where owner=’SCOTT’;

create public synonym DEPT for scott.DEPT;
create public synonym EMP for scott.EMP;
create public synonym BONUS for scott.BONUS;
create public synonym SALGRADE for scott.SALGRADE;

SQL> create public synonym DEPT for scott.DEPT;
Synonym created.

SQL> create public synonym EMP for scott.EMP;
Synonym created.

SQL> create public synonym BONUS for scott.BONUS;
Synonym created.

SQL> create public synonym SALGRADE for scott.SALGRADE;
Synonym created.

SQL>

3. Execute the following SQL statement to regenerate the DDL for public synonyms referring to private tables owned by the user SCOTT.

select ‘create public synonym ‘||synonym_name||’ for ‘||table_owner||’.’||table_name||’;’
from dba_synonyms where table_owner=’SCOTT’;

SQL> select ‘create public synonym ‘||synonym_name||’ for ‘||table_owner||’.’||table_name||’;’
2 from dba_synonyms where table_owner=’SCOTT’;

create public synonym DEPT for SCOTT.DEPT;
create public synonym EMP for SCOTT.EMP;
create public synonym BONUS for SCOTT.BONUS;
create public synonym SALGRADE for SCOTT.SALGRADE;

SQL>

4. This DDL can now be used to rebuild the public synonyms of a give schema or move the synonyms to another database.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g, oracle 9i, SQL

Recreation of Oracle tablespace

Leave a comment

Oracle tablespaces provide storage of permanent and temporary data within the an Oracle database. The two forms of Oracle tablespaces are permanent which is used to store data which can survive restart of the database and temporary which is used for sort and other temporary storage requirements and does not require survival on restart of the database. In this article we will detail a simple script to recreation of the DDL necessary to transport a tablespace structure between databases. Feel free to modify the script to allow for more complex tablespace definitions.

1. Connect to your Oracle database server and logon to SQL*PLUS.

MyLinux:>sqlplus ‘\ as sysdba’
SQL*Plus: Release 10.2.0.4.0 – Production on Tue Aug 22 13:30:13 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

2. Create a permanent tablespace to demonstrate the recreation of your DDL with the following statements.

create tablespace test_data datafile ‘/u02/ORADATA/ORCL10G/test_data01.dbf’ size 1m, ‘/u02/ORADATA/ORCL10G/TEST_data02.dbf’ size 1m;

3. Execute the following PL\SQL block to reproduce the DDL for a tablespace defined by the variable V_TS_NM.

set serveroutput on

spool ./create_tablespace.lst

declare

v_ct number:=0;
v_ct_files number;
v_ts_nm varchar2(30) := ‘TEST_DATA’;

begin

dbms_output.put_line(‘create tablespace ‘||v_ts_nm||’ datafile ‘);

select count(*) into v_ct_files from dba_data_files where tablespace_name=v_ts_nm;

for v_file in(select file_name, bytes/1024/1024 as v_size from dba_data_files
where tablespace_name=v_ts_nm)
loop

dbms_output.put_line(””||v_file.file_name||””||’ size ‘||v_file.v_size||’m’);

v_ct:=v_ct+1;

if v_ct

4. The statements produced can now be executed against another database to recreate this tablespace structure. NOTE: The directory structure on the destination server may be different from this system, so edit the physical structure of the datafiles if necessary.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g, oracle 9i, SQL

Mid-Level DBA Interview Questions

Leave a comment

With well over a decade of Oracle experience from version 6 to 11g, I have had my share of interviewing prospective DBA employees in a range from Junior to Senior DBAs. Needless to say in this field, technical competence is the key to any new hire and early on I made the blunder of not asking the write questions during the interview. I am sure every senior person or manager has made this mistake and had to deal with either training the person from scratch or letting them go. Either of these options is not desirable in our fast past business world. So, I decided to write a couple articles which cover what I believe a Junior, Mid, and Senior Oracle DBA should know to fill their prospective positions.

In this article we will cover what a Mid Level DBA should know to be successful. If you have any comments or questions, please email me at larry.catt@relidb.com.

Questions:

1. Define the directory where Oracle developed scripts reside for the maintenance of an Oracle database and how would you call this directory from within the Oracle database?

2. Define the difference between, USER_, ALL_, and DBA_ data dictionary views and define the one column missing from USER_ which exists in both ALL_ and DBA_ views?

3. How would you find the number of invalid objects in an Oracle database and how would you resolve them?

4. Where are the network related files located on a standard Oracle installation and name two of the standard files you would find there?

5. Define the Oracle utility provided for backup and recovery of an Oracle database and the two options for storing information about backed up databases?

6. Define the Oracle utility used to manage an Oracle listener?

7. Define two UNIX layer system variables required for an Oracle database to operate properly?

8. Define the Oracle utility normally used to install CPU patches and where is this utility stored?

9. Name the Oracle package which allows you to schedule routine jobs within the Oracle database, just like CRON in UNIX or Scheduler in Windows?

10. Name the utility used to format perfstat report trace files?

Answers:

1. Define the directory where Oracle developed scripts reside for the maintenance of an Oracle database and how would you call this directory from within the Oracle database?

– The directory where Oracle stores the maintenance scripts is $ORACLE_HOME/rdbms/admin.
– The question mark represents the ORACLE_HOME within the database, thus you would call this directory with ‘?/rdbms/admin’. Another acceptable answer is ‘@?/rdbms/admin’.

2. Define the difference between, USER_, ALL_, and DBA_ data dictionary views and define the one column missing from USER_ which exists in both ALL_ and DBA_ views?

– USER_ – These views only contain definition of objects which you own.
– ALL_ – These views contain definitions of all objects which you have access to.
– DBA_ – These views contain definitions of all objects in the database, even if you do not have access to them.

– The USER_ views do not contain a column named OWNER, by definition of the view you are the owner.

3. How would you find the number of invalid objects in an Oracle database and how would you resolve them?

– By using the data dictionary view DBA_OBJECTS with the statement “select count(*) from dba_objects where status=’INVALID’;”
– You would use the Oracle script, “UTLRP.SQL” located in directory $ORACLE_HOME/rdbms/admin.

4. Where are the network related files located on a standard Oracle installation and name two of the standard files you would find there?

– In a standard Oracle installation the network files are located under “$ORACLE_HOME/network/admin”.
– Files located in this directory are: “LISTENER.ORA”, “TNSNAMES.ORA”, or “SQLNET.ORA”.

5. Define the Oracle utility provided for backup and recovery of an Oracle database and the two options for storing information about backed up databases?

– RMAN (Recovery Manager)
– The two methods for storing information about backed up database are: the controlfile or Recovery Catalog.

6. Define the Oracle utility used to manage an Oracle listener?

– LSNRCTL (LISTENER CONTROL)

7. Define two UNIX layer system variables required for an Oracle database to operate properly?

– $ORACLE_HOME – defines the directory where the Oracle binaries are installed.
– $ORACLE_SID – defines the instance name used to access a database.

8. Define the Oracle utility normally used to install CPU patches and where is this utility stored?

– “opatch” is the utility which is used to install CPU patches.
– The “opatch” utility is located in the directory “$ORACLE_HOME/OPatch”. NOTE: “opatch” is not a valid answer in a standard Oracle installation the directory has a capital O and P.

9. Name the Oracle package which allows you to schedule routine jobs within the Oracle database, just like CRON in UNIX or Scheduler in Windows?

– DBMS_JOBS – This answer is valid for all versions of Oracle.
– DBMS_SCHEDULER – This answer is only valid for Oracle 10g or higher.

10. Name the utility used to format perfstat report trace files?

– TKPROF

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g, SQL

Gathering SQL statements in Oracle 10g

Leave a comment

Oracle 10g allows for increase amount of information to be extracted about SQL statements executed against the database since last instance startup. In Oracle 10g we have the new V$BIND_VALUE_CAPTURE, which allows you see the bind variables attached to most SQL statements executed in the database. Additionally, the view V$SQL has several improvements over it’s 9i counterpart to include: Full contents of the SQL statement; last execution time in date format; and actually username of parsing user. This article will cover how to extract previously and currently executed SQL statements with a few different definable variables from you Oracle 10g database.

1. Logon to your Oracle 10g database as sysdba.

MyLinux:>sqlplus ‘/ as sysdba’
SQL*Plus: Release 10.2.0.4.0 – Production on Tue Aug 16 13:30:13 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

2. To extract all SQL executed since the last system startup, execute the following PL/SQL block. NOTE: This may contain a lot of information, so you should spool the contents of this execution to an OS layer file with the command: spool ./

/********************
// Larry Catt
// 15 AUG 2009
// All SQL since instance startup.
//
// 10g version
/***********************/

set serveroutput on size 1000000

declare
v_ct number;
v_bind varchar2(4000);
begin

for v_sql in(select sql_id, PARSING_SCHEMA_NAME, SQL_TEXT,
to_char(LAST_ACTIVE_TIME, ‘DD-MON-YYYY HH:MI:SS’) as v_time
from v$sql order by LAST_ACTIVE_TIME)
loop

dbms_output.put_line(‘##########################’);
dbms_output.put_line(‘#’);
dbms_output.put_line(v_sql.parsing_schema_name);
dbms_output.put_line(v_sql.sql_text);
dbms_output.put_line(v_sql.v_time);

for v_b in(select VALUE_STRING from V$SQL_BIND_CAPTURE where sql_id=v_sql.sql_id
order by position)
loop

dbms_output.put_line(v_b.value_string);

end loop;
end loop;
end;
/

3. The following PL/SQL block will give you all the SQL for a particular user defined by the variable V_USER.

/********************
// Larry Catt
// 15 AUG 2009
// ALL SQL from a particular user defined as the variable V_USER.
//
// 10g version
/***********************/

set serveroutput on size 1000000
declare
v_ct number;
v_bind varchar2(4000);
v_user varchar2(30):=’SYS’;

begin

for v_sql in(select sql_id, PARSING_SCHEMA_NAME, SQL_TEXT,
to_char(LAST_ACTIVE_TIME, ‘DD-MON-YYYY HH:MI:SS’) as v_time
from v$sql where parsing_schema_name= v_user order by LAST_ACTIVE_TIME)
loop

dbms_output.put_line(‘##########################’);
dbms_output.put_line(‘#’);
dbms_output.put_line(v_sql.parsing_schema_name);
dbms_output.put_line(v_sql.sql_text);
dbms_output.put_line(v_sql.v_time);

for v_b in(select VALUE_STRING from V$SQL_BIND_CAPTURE where sql_id=v_sql.sql_id
order by position)
loop

dbms_output.put_line(v_b.value_string);
end loop;
end loop;
end;
/

4. The following PL/SQL block will give you all the SQL statements executed during the time range of variables V_START_TIME and V_END_TIME.

/********************
// Larry Catt
// 15 Aug 2009
// All SQL statements executed between the times V_START_TIME and V_END_TIME.
//
// 10g version
/***********************/

set serveroutput on size 1000000

declare

v_ct number;
v_bind varchar2(4000);
v_start_time date := to_date(’15-AUG-2009 13:15:00′,’DD-MON-YYYY HH24:MI:SS’);
v_end_time date:=to_date(’15-AUG-2009 15:15:00′,’DD-MON-YYYY HH24:MI:SS’);

begin

for v_sql in(select sql_id, PARSING_SCHEMA_NAME, SQL_TEXT,
to_char(LAST_ACTIVE_TIME, ‘DD-MON-YYYY HH:MI:SS’) as v_time
from v$sql where LAST_ACTIVE_TIME between v_start_time and
v_end_time order by LAST_ACTIVE_TIME)
loop

dbms_output.put_line(‘##########################’);
dbms_output.put_line(‘#’);
dbms_output.put_line(v_sql.parsing_schema_name);
dbms_output.put_line(v_sql.sql_text);
dbms_output.put_line(v_sql.v_time);

for v_b in(select VALUE_STRING from V$SQL_BIND_CAPTURE where sql_id=v_sql.sql_id
order by position)
loop

dbms_output.put_line(v_b.value_string);

end loop;
end loop;
end;
/

5. The following PL/SQL block will give you all the SQL statements currently being executed in the system.

/********************
// Larry Catt
// 15 Aug 2009
// All SQL statements currently executing.
//
// 10g version
/***********************/

set serveroutput on size 1000000

declare

v_ct number;
v_bind varchar2(4000);

begin

for v_sql in(select sql_id, PARSING_SCHEMA_NAME, SQL_TEXT,
to_char(LAST_ACTIVE_TIME, ‘DD-MON-YYYY HH:MI:SS’) as v_time
from v$sql where address in
(select sql_address from v$session where status=’ACTIVE’) order by
LAST_ACTIVE_TIME)
loop

dbms_output.put_line(‘##########################’);
dbms_output.put_line(‘#’);
dbms_output.put_line(v_sql.parsing_schema_name);
dbms_output.put_line(v_sql.sql_text);
dbms_output.put_line(v_sql.v_time);

for v_b in(select VALUE_STRING from V$SQL_BIND_CAPTURE where sql_id=v_sql.sql_id
order by position)
loop

dbms_output.put_line(v_b.value_string);

end loop;
end loop;
end;
/

6. This completes extraction of Oracle SQL statements in Oracle 10g.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, oracle 9i, SQL

Gathering SQL statements in Oracle 9i

Leave a comment

Oracle 9i provides a way of gathering previously executed SQL in a system when tracing was not enabled. This feature is possible through the system view V$SQL, while not as robust as the newer Oracle releases, it still provides the DBA with the ability to view the exact user activities which are occurring in the database. Viewing previously executed statements during and after a system slowdown is highly valuable in determining the cause of certain system problems. In this article we will review the system view V$SQL and the columns in it which would allow you to regenerate previously executed statements.

1. Logon to SQL*PLUS as sysdba.

mylinux$ sqlplus ‘/ as sysdba’
SQL*Plus: Release 9.2.0.8.0 – Production on Tue Aug 16 10:53:45 2009

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production
SQL>

2. Describe the system view V$SQL and you will find three columns of interest for extraction of SQL are: SQL_TEXT, PARSING_USER_ID, and LAST_LOAD_TIME. SQL_TEXT provides the first 1000 characters of the SQL statement executed. NOTE: this may not be the entire SQL text. PARSING_USER_ID provides the User ID which last successfully parsed the statement. NOTE: This may not be the last person to execute the statement. Finally, LAST_LOAD_TIME provides you the time of last execution.

SQL> desc v$sql
Name Null? Type
—————————————– ——– —————
SQL_TEXT VARCHAR2(1000)
PARSING_USER_ID NUMBER
LAST_LOAD_TIME VARCHAR2(19)
…
…
…

3. Due to the limitation of DBMS_OUTPUT package in 9i we will create a dummy table to temporarily hold our SQL extracts. Execute the collowing DDL to create the table SQL_LONG.

SQL> create table sql_long(sql_text long, exec_time varchar2(19));

Table created.

SQL>

4. To extract all SQL in your database since instance startup, execute the following PL/SQL Block to populate your dummy table SQL_LONG.

set serveroutput on size 1000000
declare

v_ct number;

begin

for v_sql in(select SQL_TEXT, LAST_LOAD_TIME from v$sql order by last_load_time)
loop

insert into sql_long(sql_text, exec_time) values(v_sql.sql_text,
v_sql.last_load_time);

end loop;
end;
/

5. Now the only thing left is to extract the SQL from your dummy table. NOTE: You do not have to order by time of execution, because the SQL was inserted in ordered fashion.

set long 10000
select * from sql_long;

6. To retrieve the currently executing SQL statements issue the following PL/SQL block. NOTE: truncate the table SQL_LONG before every execution to ensure that you only received the information from the previous block.

Truncate table sql_long;

set serveroutput on size 1000000
declare

v_ct number;

begin

for v_sql in(select SQL_TEXT, LAST_LOAD_TIME from v$sql where address in
(select sql_address from v$session where status=’ACTIVE’) order by
last_load_time)
loop

insert into sql_long(sql_text, exec_time) values(v_sql.sql_text,
v_sql.last_load_time);

end loop;
end;
/

set long 10000
select * from sql_long;

Larry Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, Oracle, Oracle 10g, oracle 9i, SQL

Oracle auditing of user’s connections

Leave a comment

Oracle provides the ability to audit a great range of activities within the Oracle RDBMS environment. An administrator has the ability to audit as much as the activities of the entire database all the way to any select, insert, or update on a single table. Care should be taken on what you wish to audit, due to the amount of disk space required to store all of this information. In this article, we will discuss the auditing of connects by a single user account.

1. Logon to SQL*PLUS as sysdba

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Mon Jul 27 19:13:26 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

2. Ensure that audit_trail and audit_sys_operations parameters are set to TRUE.

SQL> show parameter audit

NAME TYPE VALUE
———————————— ———– ——————————
audit_file_dest string /U01/ORACLE/PRODUCT/10.2.0/ADMIN
/ORCL10G/ADUMP
audit_sys_operations boolean FALSE
audit_trail string NONE

SQL> alter system set audit_trail=TRUE scope=spfile;

System altered.

SQL> alter system set audit_sys_operations= TRUE scope=spfile;

System altered.

SQL>

3. Restart the database if you had to edit the init parameters.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 209718148 bytes
Database Buffers 394264576 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL>

4. Ensure that the new values took effect.

SQL> show parameter audit

NAME TYPE VALUE
———————————— ———– ——————————
audit_file_dest string /U01/ORACLE/PRODUCT/10.2.0/ADMIN
/ORCL10G/ADUMP
audit_sys_operations boolean TRUE
audit_trail string TRUE
SQL>

5. Begin auditing of acccount which you suspect is causing an issue.

SQL> audit connect by ljcatt;

Audit succeeded.

SQL>

6. Logon as the user and perform some actions.

SQL> connect ljcatt/ljcatt
Connected.
SQL>

7. Log back in to oracle as sysdba

SQL> connect sys as sysdba
Connected.
SQL>

8. Perform the following SQL to extract the connection information for the USER LJCATT.

SQL> select userhost, terminal, timestamp, action_name from dba_audit_session wh
ere username=’LJCATT’;

USERHOST TERMINAL TIMESTAMP ACTION_NAME
———— ——– ——— ——————
Mylinux Mylinux1 27-JUL-09 LOGON
Mylinux Mylinux1 27-JUL-09 LOGOFF

9. There is a wealth of information that can be obtained by looking at the various dictionary views which cover the Oracle auditing process. You can easily see if someone is accessing your database in an improper way or manipulating data with a direct connection such as SQL*PLUS instead of through an application. This article covered a brief overview of auditing a single user’s connections; however it is easy to see the power that Oracle auditing can provide.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g, oracle 9i, SQL

Recreate Oracle View

Leave a comment

Oracle provides views to allow you to hide the structures of under lying tables, to combine multiple tables into a single logical structure, and to reduce the complexity of SQL being generated against a database. Views allow the developer the flexibility to give users the look and feel of the database they desire, while maintaining a normalized structure under the covers. In this article we will review the SQL to generate a view’s DDL for review or transport.

1. Connect to your Oracle database server and logon to SQL*PLUS.

MyLinux:>sqlplus ‘\ as sysdba’
SQL*Plus: Release 10.2.0.4.0 – Production on Tue Jul 20 23:51:33 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

2. Logon as the user SCOTT and generate the view TEST_VIEW to demonstrate the DDL extraction.

SQL> grant create view to scott;

Grant succeeded.

SQL> connect scott/tiger
Connected.
SQL> create view TEST_VIEW as
2 select emp.ename as employee, dept.dname as department, bonus.sal as salary
from
3 dept, emp, bonus where dept.deptno=emp.deptno and emp.ename=bonus.ename;

View created.

SQL>

3. Execute the following PL/SQL block to generate your DDL.

set serveroutput on
declare

v_ct number;
v_owner varchar2(30):=’SCOTT’;

begin

for v_view in(select view_name, text from dba_views where owner=v_owner)
loop

dbms_output.put_line(‘create view ‘||v_view.view_name||’ as’);
dbms_output.put_line(v_view.text||’;’);

end loop;
end;
/

SQL> connect / as sysdba
Connected.
SQL>
SQL> set serveroutput on
SQL> declare
2
3 v_ct number;
4 v_owner varchar2(30):=’SCOTT’;
5
6 begin
7
8 for v_view in(select view_name, text from dba_views where owner=v_owner)
9 loop
10
11 dbms_output.put_line(‘create view ‘||v_view.view_name||’ as’);
12 dbms_output.put_line(v_view.text||’;’);
13
14 end loop;
15 end;
16 /
create view TEST_VIEW as
select emp.ename as employee, dept.dname as department, bonus.sal as salary from
dept, emp, bonus where dept.deptno=emp.deptno and emp.ename=bonus.ename;

PL/SQL procedure successfully completed.

SQL>
SQL>

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com