Answer Happy

RUN THIS 3 sql file FIRST
FIrST-- connect as the user HR
drop table largetable;
create table largetable (msgid number, msg varchar2(25))tablespace users;
BEGIN for x3 in 1..100000 LOOP insert into largetable values(x3, 'Message is ' || x3); end loop;END;/
commit;
BEGIN for x4 in 1..50 LOOP execute immediate 'ALTERTABLE largetable ALLOCATE EXTENT'; end loop;END;/
BEGIN delete from largetable where mod(msgid,3) = 0and msgid < 700; commit; for x5 in 700..100000 LOOP if mod(x5, 20) = 0 then deletefrom largetable where msgid=x5; end if; end loop;commit;for x2 in 10000..950000 LOOP if mod(x2, 4) = 0 then deletefrom largetable where msgid=x2; end if; end loop;commit;
END;/
------
SECOND-
/* Run this code as SYSTEM */variable id number;begin declare name varchar2(100); descr varchar2(500); obj_id number; begin name:='Manual_LargeTable'; descr:='Segment Advisor Example';
dbms_advisor.create_task ( advisor_name => 'SegmentAdvisor', task_id =>:id, task_name =>name, task_desc =>descr);
dbms_advisor.create_object ( task_name =>name, object_type => 'TABLE', attr1 =>'HR', attr2 =>'LARGETABLE', attr3 =>NULL, attr4 =>NULL, attr5 =>NULL, object_id =>obj_id);
dbms_advisor.set_task_parameter( task_name =>name, parameter =>'recommend_all', value =>'TRUE');
dbms_advisor.execute_task(name); end;end; /select task_name, status from dba_advisor_tasks where owner = 'HR' and advisor_name = 'SegmentAdvisor';
select af.task_name, ao.attr2 segname, ao.attr3 partition,ao.type, af.message from dba_advisor_findings af, dba_advisor_objects ao where ao.task_id = af.task_id and ao.object_id = af.object_id and ao.owner = 'SYSTEM';
alter table hr.largetable enable row movement;alter table hr.largetable shrink space;======
THIRD_
/* start a new SQL*Plus session */sqlplus hr/hrCreate table employee_copy as select * from employees;Delete from employee_copy;
/* start a new SQL*Plus session and create the deadlock*/sqlplus hr/hrDelete from employee_copy;
Chapter 13 Exercises1. Open the associated files for this exercise. Start withlargetable.sql – open the file and use the codethere to create a table for the HR user and insert lots of rows,allocate 50 extents, and then deletevarious rows. Execute each command individually to see the resultsbefore continuing. This will giveus a table that needs attention since it will be wasting spaceafter our DML – it’s called LARGETABLEand should be owned by HR. The ADDM report that you will run laterin this exercise should findthis table as problematic.2. Open the file, SQL13-02.sql, from Canvas. Use the code in thisfile for the following: open a newSQL*Plus session (second one) and log in as HR. Run the createtable statement and the deletecommand. Do NOT commit or exit this session. Open a new SQL*Plussession (third one) and alsolog in as HR (leaving the other SQL*Plus sessions open.) Run thesame delete statement and keepthis session open. Open a session connected as SYSTEM. View thelock that has been created.What can be done to release the lock? (hint: commit where row islocked with update statement)3. Query the name of the tablespace that contains the tableHR.EMPLOYEES. Move theHR.EMPLOYEES table to a different tablespace (USERS or EXAMPLE)using the ALTER TABLE .. MOVE________ to tablespace __________ command. This will cause the rowsto move and indexes tobecome unusable.4. Run the query which will determine if you have any unusableindexes.5. Execute the statement(s) that will repair the unusableindexes.6. Run the query to determine if all of HR’s objects are valid andall indexes are usable.7. Run an ADDM report from the SQL prompt (SQL*Plus) and review thefindings.8. Review each recommendation made by ADDM (view the reportfindings)9. Open and read the commands in SQL13-09.sql. Run the SegmentAdvisor using the code found inthe attached file – reading and carefully executing one command ata time from the file, SQL13-09.sql (you may need to make some edits). Select the tableLARGETABLE and, once complete, viewthe report findings using the SQL in the file (use set long 100000and set longchunksize 32k)10. Repair (defragment as directed by the advisor) the table,LARGETABLE.