The goal of the first command is to tell you how long the next command takes to execute. The flush.sql script flushes both the shared pool and the buffer cache to
7) From your terminal session, execute the correlation_cleanup.sh script to clean up your environment
set timing on
set echo on SELECT COUNT(*)
FROM products p, (SELECT prod_id, AVG(unit_cost) ac FROM costs GROUP BY prod_id) c
WHERE p.prod_id = c.prod_id AND
p.prod_list_price < 1.15 * c.ac;
6) Exit from your SQL*Plus session.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining and Real Application Testing options
[oracle@edrsr33p1-orcl Common_Mistakes]$
7) From your terminal session, execute the correlation_cleanup.sh script to clean up your environment.
[oracle@edrsr33p1-orcl Common_Mistakes]$ ./correlation_cleanup.sh SQL*Plus: Release 11.1.0.6.0 - Production on Wed Mar 26 20:49:22 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining and Real Application Testing options
SQL> SQL>
Revoke succeeded.
SQL> SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining and Real Application Testing options
[oracle@edrsr33p1-orcl Common_Mistakes]$
---
#!/bin/bash
cd /home/oracle/solutions/Common_Mistakes export ORACLE_SID=orcl
Oracle Internal & Or acle Academy Use Only
export ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1 export
PATH=/u01/app/oracle/product/11.1.0/db_1/bin:/bin:/usr/bin:/usr/loca l/bin:/usr/X11R6/bin:/usr/java/jdk1.5.0_11/bin:/bin
sqlplus / as sysdba <<EOF revoke dba from sh;
EOF
8) Before continuing, execute the setup_rest.sh script to set up the environment for all the examples that follow. Make sure you run the script from a terminal session connected as the oracle user. You can find the scripts for all the following cases in your $HOME/solutions/Common_Mistakes directory.
[oracle@edrsr33p1-orcl Common_Mistakes]$ ./setup_rest.sh
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Mar 26 20:59:53 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining and Real Application Testing options
SQL> SQL> SQL> SQL> drop user jfv cascade *
ERROR at line 1:
ORA-01918: user 'JFV' does not exist
SQL> SQL>
User created.
SQL> SQL>
Grant succeeded.
SQL> SQL> Connected.
SQL> SQL> drop table orders purge *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> SQL>
Table created.
SQL> SQL> 2 3 4 5 6 7 PL/SQL procedure successfully completed.
Oracle Internal & Or acle Academy Use Only
SQL> SQL> 2 3 4 5 6 7 PL/SQL procedure successfully completed.
SQL> SQL> drop table employees purge *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> drop table job_history purge *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> SQL>
Table created.
SQL> SQL> 2 3 4 5 6 7 PL/SQL procedure successfully completed.
SQL> SQL>
Table created.
SQL> SQL> 2 3 4 5 6 7 PL/SQL procedure successfully completed.
SQL> SQL>
Index created.
SQL> SQL> SQL> drop table old purge *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> drop table new purge *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> SQL>
Table created.
SQL>
Table created.
SQL> SQL> 2 3 4 5 6 7 PL/SQL procedure successfully completed.
SQL> SQL> 2 3 4 5 6 7 PL/SQL procedure successfully completed.
SQL> SQL> SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
Oracle Internal & Or acle Academy Use Only
With the Partitioning, Oracle Label Security, OLAP, Data Mining and Real Application Testing options
[oracle@edrsr33p1-orcl Common_Mistakes]$
---
#!/bin/bash
cd /home/oracle/solutions/Common_Mistakes export ORACLE_SID=orcl
export ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1 export
PATH=/u01/app/oracle/product/11.1.0/db_1/bin:/bin:/usr/bin:/usr/loca l/bin:/usr/X11R6/bin:/usr/java/jdk1.5.0_11/bin:/bin
sqlplus / as sysdba <<EOF set echo on
drop user jfv cascade;
create user jfv identified by jfv default tablespace users temporary tablespace temp;
grant connect, resource, dba to jfv;
connect jfv/jfv
drop table orders purge;
create table orders (order_id_char varchar2(50) primary key, order_total number, customer_name varchar2(300));
begin
for i in 1..500000 loop insert into orders
values(i,100,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaa');
end loop;
commit;
end;
/ begin
for i in 1..500000 loop insert into orders
values(500000+i,100,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
end loop;
commit;
Oracle Internal & Or acle Academy Use Only
end;
/
drop table employees purge;
drop table job_history purge;
create table employees (employee_id number primary key, name varchar2(500));
begin
for i in 1..500000 loop insert into employees
values(i,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaa');
end loop;
commit;
end;
/
create table job_history (employee_id number, job varchar2(500));
begin
for i in 1..500000 loop insert into job_history
values(mod(i,1000),'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
end loop;
commit;
end;
/
create index job_history_empid_indx on job_history(employee_id);
drop table old purge;
drop table new purge;
create table old(name varchar2(10), other varchar2(500));
create table new(name varchar2(10), other varchar2(500));
begin
for i in 1..500000 loop insert into old
values(i,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaa');
end loop;
commit;
end;
/ begin
Oracle Internal & Or acle Academy Use Only
for i in 1..500000 loop insert into new
values(500000+i,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaa');
end loop;
commit;
end;
/
EOF