Capítulo 2. MEMORIA
2.5 TIA Portal
2.5.3 Conexión con el HMI
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