Saturday, October 22, 2011

Comparision between shrink space, shrink space compact and shrink space cascade

drop table big_table purge;
create table big_table (id number(18,0), name char(1000));
create index big_table_n1 on big_table(name);
create unique index big_table_pk on big_table(id);
alter table big_table add constraint big_table_pk primary key(id) using index big_table_pk;
insert into big_table select rownum, dbms_random.string('P',10) from dual connect by rownum < 10000;
delete from big_table where mod(id,10)<>0;
commit;
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';
alter table big_table enable row movement;
alter table big_table shrink space compact;
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';
/****************************************************************************************************
table BIG_TABLE dropped.
table BIG_TABLE created.
index BIG_TABLE_N1 created.
unique index BIG_TABLE_PK created.
table BIG_TABLE altered.
9,999 rows inserted.
9,000 rows deleted.
commited.
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 196608
BIG_TABLE_N1 19922944
BIG_TABLE 12582912

table BIG_TABLE altered.
table BIG_TABLE altered.
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 196608
BIG_TABLE_N1 19922944
BIG_TABLE 12582912
****************************************************************************************************/
drop table big_table purge;
create table big_table (id number(18,0), name char(1000));
create index big_table_n1 on big_table(name);
create unique index big_table_pk on big_table(id);
alter table big_table add constraint big_table_pk primary key(id) using index big_table_pk;
insert into big_table select rownum, dbms_random.string('P',10) from dual connect by rownum < 10000;
delete from big_table where mod(id,10)<>0;
commit;
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';
alter table big_table enable row movement;
alter table big_table shrink space;
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';
alter index big_table_n1 shrink space compact; -- same as 'alter index big_table_pk coalesce'
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';
alter index big_table_n1 coalesce;
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';
alter index big_table_n1 shrink space;
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';
alter index big_table_n1 rebuild online;
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';
alter index big_table_n1 rebuild;
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';

/****************************************************************************************************
table BIG_TABLE dropped.
table BIG_TABLE created.
index BIG_TABLE_N1 created.
unique index BIG_TABLE_PK created.
table BIG_TABLE altered.
9,999 rows inserted.
9,000 rows deleted.
commited.
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 196608
BIG_TABLE_N1 19922944
BIG_TABLE 12582912

table BIG_TABLE altered.
table BIG_TABLE altered.
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 196608
BIG_TABLE_N1 19922944
BIG_TABLE 1310720

index BIG_TABLE_N1 altered. //alter index big_table_n1 shrink space compact
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 196608
BIG_TABLE_N1 19922944
BIG_TABLE 1310720

index BIG_TABLE_N1 altered. //alter index big_table_n1 coalesce;
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 196608
BIG_TABLE_N1 19922944
BIG_TABLE 1310720

index BIG_TABLE_N1 altered. //alter index big_table_n1 shrink space;
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 196608
BIG_TABLE_N1 1376256
BIG_TABLE 1310720

index BIG_TABLE_N1 altered. //alter index big_table_n1 rebuild online;
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 196608
BIG_TABLE_N1 2097152
BIG_TABLE 1310720

index BIG_TABLE_N1 altered. //alter index big_table_n1 rebuild;
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 196608
BIG_TABLE_N1 2097152
BIG_TABLE 1310720
****************************************************************************************************/
drop table big_table purge;
create table big_table (id number(18,0), name char(1000));
create index big_table_n1 on big_table(name);
create unique index big_table_pk on big_table(id);
alter table big_table add constraint big_table_pk primary key(id) using index big_table_pk;
insert into big_table select rownum, dbms_random.string('P',10) from dual connect by rownum < 10000;
delete from big_table where mod(id,10)<>0;
commit;
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';
alter table big_table enable row movement;
alter table big_table shrink space cascade;
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';
/****************************************************************************************************
table BIG_TABLE dropped.
table BIG_TABLE created.
index BIG_TABLE_N1 created.
unique index BIG_TABLE_PK created.
table BIG_TABLE altered.
9,999 rows inserted.
9,000 rows deleted.
commited.
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 196608
BIG_TABLE_N1 19922944
BIG_TABLE 12582912

table BIG_TABLE altered.
table BIG_TABLE altered.
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 65536
BIG_TABLE_N1 1376256
BIG_TABLE 1310720
****************************************************************************************************/

Segment Advisor related SQL



/*
create table big_table (id number, name char(200));
insert into big_table select rownum,'a' from dual connect by rownum<100000;
delete from big_table where mod(id,10)<>0;
commit;
*/

SELECT
'Segment Advice --------------------------'|| chr(10)||
'TABLESPACE_NAME : ' || tablespace_name|| chr(10)||
'SEGMENT_OWNER: ' || segment_owner|| chr(10)||
'SEGMENT_NAME: ' || segment_name|| chr(10)||
'ALLOCATED_SPACE : ' || allocated_space|| chr(10)||
'RECLAIMABLE_SPACE: ' || reclaimable_space || chr(10)||
'RECOMMENDATIONS : ' || recommendations|| chr(10)||
'SOLUTION 1: ' || c1|| chr(10)||
'SOLUTION 2: ' || c2|| chr(10)||
'SOLUTION 3: ' || c3 Advice
FROM
TABLE(dbms_space.asa_recommendations(ALL_RUNS=>'FALSE', SHOW_MANUAL=>'FALSE',SHOW_FINDINGS=>'FALSE'));



SELECT 'Task Name : ' || f.task_name || chr(10) ||
'Start Run Time : ' || TO_CHAR(execution_start, 'dd-mon-yy hh24:mi') || chr (10) ||
'Segment Name : ' || o.attr2 || chr(10) ||
'Segment Type : ' || o.type || chr(10) ||
'Partition Name : ' || o.attr3 || chr(10) ||
'Message : ' || f.message || chr(10) ||
'More Info : ' || f.more_info || chr(10) ||
'------------------------------------------------------' Advice
FROM dba_advisor_findings f ,
dba_advisor_objects o ,
dba_advisor_executions e
WHERE o.task_id = f.task_id
AND o.object_id = f.object_id
AND f.task_id = e.task_id
AND e. execution_start > sysdate - 1
AND e.advisor_name = 'Segment Advisor'
ORDER BY f.task_name;




DECLARE
my_task_id NUMBER;
obj_id NUMBER;
my_task_name VARCHAR2(100);
my_task_desc VARCHAR2(500);
BEGIN
my_task_name := 'Adhoc Segment Advice for table Donghua.BIG_TABLE';
my_task_desc := 'Manual Segment Advisor Run';
---------
-- Step 1
---------
dbms_advisor.create_task ( advisor_name => 'Segment Advisor',
task_id => my_task_id,
task_name => my_task_name,
task_desc => my_task_desc);
---------
-- Step 2
---------
dbms_advisor.create_object ( task_name => my_task_name,
object_type => 'TABLE',
attr1 => 'DONGHUA',
attr2 => 'BIG_TABLE',
attr3 => NULL,
attr4 => NULL,
attr5 => NULL,
object_id => obj_id);
---------
-- Step 3
---------
dbms_advisor.set_task_parameter( task_name => my_task_name,
parameter => 'recommend_all',
value => 'TRUE');
---------
-- Step 4
---------
dbms_advisor.execute_task(my_task_name);
END;
/


SELECT
'Segment Advice --------------------------'|| chr(10)||
'TABLESPACE_NAME : ' || tablespace_name|| chr(10)||
'SEGMENT_OWNER: ' || segment_owner|| chr(10)||
'SEGMENT_NAME: ' || segment_name|| chr(10)||
'ALLOCATED_SPACE : ' || allocated_space|| chr(10)||
'RECLAIMABLE_SPACE: ' || reclaimable_space || chr(10)||
'RECOMMENDATIONS : ' || recommendations|| chr(10)||
'SOLUTION 1: ' || c1|| chr(10)||
'SOLUTION 2: ' || c2|| chr(10)||
'SOLUTION 3: ' || c3 Advice
FROM
TABLE(dbms_space.asa_recommendations(ALL_RUNS=>'TRUE', SHOW_MANUAL=>'TRUE',SHOW_FINDINGS=>'FALSE'));





-- 10gR2 view
-- DBA_AUTO_SEGADV_SUMMARY

select * from dictionary where table_name like '%AUTO%';

select job_start_time,job_status,job_duration,job_error
from DBA_AUTOTASK_JOB_HISTORY
where client_name='auto space advisor'
order by job_start_time;

select * from dba_autotask_client;



select * from DBA_HIST_SEG_STAT;

Using SSH tunneling to access database through ssh

rh6 is the client, vmxdb04b is the database server,

firewall port open is incomming port 22 for vmxdb04b


donghua@rh6:~$ ssh -N -p 22 oracle@vmxdb04b -L 8888/vmxdb04b/1521

oracle@vmxdb04b's password:


-p 22 oracle@vmxdb04b: build ssh connection to vmxdb04b


-L 8888/vmxdb04b/1521 : Start a local listening port 8888 on rh6, and forward all the packet data to vmxdb04b:1521. Here the vmxdb04b can change to any host for forwarding purpose.

-N instructs OpenSSH to not execute a command on the remote system.


donghua@rh6:~$ netstat -na|more

Active Internet connections (servers and established)

Proto Recv-Q Send-Q Local Address Foreign Address State

tcp 0 0 127.0.0.1:8307 0.0.0.0:* LISTEN

tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN

tcp 0 0 127.0.0.1:631 0.0.0.0:* LISTEN

tcp 0 0 127.0.0.1:8888 0.0.0.0:* LISTEN



donghua@rh6:~$ sqlplus donghua/donghua@localhost:8888/orcl

SQL*Plus: Release 11.2.0.3.0 Production on Sat Oct 22 09:40:32 2011

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select username,program,machine,paddr from v$session where sid=userenv('SID');

USERNAME PROGRAM

------------------------------ ------------------------------------------------

MACHINE

----------------------------------------------------------------

PADDR

----------------

DONGHUA sqlplus@rh6 (TNS V1-V3)

rh6

00000000914A0B88

Wednesday, October 5, 2011

there is still one advantage from 'exp', comparing to 'expdp'

The 'exp' can be used in 'read only' database, which is good to ensure data integrity.

[oracle@vmxdb01 ~]$ expdp \"/ as sysdba\" directory=DUMPDIR dumpfile=aud.dmp logfile=aud.log tables=sys.aud$

Export: Release 11.2.0.2.0 - Production on Wed Oct 5 23:01:38 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: unable to create master table "SYS.SYS_EXPORT_TABLE_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1020
ORA-16000: database open for read-only access


[oracle@vmxdb01 ~]$ exp \"/ as sysdba\" file=aud.dmp tables=sys.aud$;

Export: Release 11.2.0.2.0 - Production on Wed Oct 5 23:02:00 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table AUD$ 254 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.

SQL Server release mapping (using @@version)

SQL Server 2008 R2
10.50.1765.0 SQL Server 2008 R2 CU6 21 Feb 2011
10.50.1753.0 SQL Server 2008 R2 CU5 20 Dec 2010
10.50.1746.0 SQL Server 2008 R2 CU4 18 Oct 2010
10.50.1734.0 SQL Server 2008 R2 CU3 17 Aug 2010
10.50.1720.0 SQL Server 2008 R2 CU2 25 Jun 2010
10.50.1702.0 SQL Server 2008 R2 CU1 18 May 2010
10.50.1600.1 SQL Server 2008 R2 RTM 12 Apr 2010
SQL Server 2008
10.00.4272 SQL Server 2008 SP2 CU2 17 Jan 2011
10.00.4266 SQL Server 2008 SP2 CU1 15 Nov 2010
10.00.4000 SQL Server 2008 SP2 29 Sep 2010
10.00.2808 SQL Server 2008 SP1 CU12 17 Jan 2011
10.00.2804 SQL Server 2008 SP1 CU11 15 Nov 2010
10.00.2799 SQL Server 2008 SP1 CU10 21 Sep 2010
10.00.2789 SQL Server 2008 SP1 CU9 19 Jul 2010
10.00.2775 SQL Server 2008 SP1 CU8 17 May 2010
10.00.2766 SQL Server 2008 SP1 CU7 15 Mar 2010
10.00.2757 SQL Server 2008 SP1 CU6 18 Jan 2010
10.00.2746 SQL Server 2008 SP1 CU5 24 Nov 2009
10.00.2734 SQL Server 2008 SP1 CU4 22 Sep 2009
10.00.2723 SQL Server 2008 SP1 CU3 21 Jul 2009
10.00.2714 SQL Server 2008 SP1 CU2 18 May 2009
10.00.2710 SQL Server 2008 SP1 CU1 16 Apr 2009
10.00.2531 SQL Server 2008 SP1 7 Apr 2009
10.00.1835 SQL Server 2008 RTM CU10 15 Mar 2010
10.00.1828 SQL Server 2008 RTM CU9 18 Jan 2009
10.00.1823 SQL Server 2008 RTM CU8 16 Nov 2009
10.00.1818 SQL Server 2008 RTM CU7 21 Sep 2009
10.00.1812 SQL Server 2008 RTM CU6 21 Jul 2009
10.00.1806 SQL Server 2008 RTM CU5 18 May 2009
10.00.1798 SQL Server 2008 RTM CU4 17 Mar 2009
10.00.1787 SQL Server 2008 RTM CU3 19 Jan 2009
10.00.1779 SQL Server 2008 RTM CU2 17 Nov 2008
10.00.1763 SQL Server 2008 RTM CU1 22 Sep 2008
10.00.1600 SQL Server 2008 RTM 6 Aug 2008
SQL Server 2005
9.00.5266 SQL Server 2005 SP4 CU3 21 Mar 2011
9.00.5259 SQL Server 2005 SP4 CU2 22 Feb 2011
9.00.5254 SQL Server 2005 SP4 CU1 20 Dec 2010
9.00.5000 SQL Server 2005 SP4 17 Dec 2010
9.00.4325 SQL Server 2005 SP3 CU15 21 Mar 2011
9.00.4317 SQL Server 2005 SP3 CU14 21 Feb 2011
9.00.4315 SQL Server 2005 SP3 CU13 20 Dec 2010
9.00.4311 SQL Server 2005 SP3 CU12 18 Oct 2010
9.00.4309 SQL Server 2005 SP3 CU11 17 Aug 2010
9.00.4305 SQL Server 2005 SP3 CU10 23 Jun 2010
9.00.4294 SQL Server 2005 SP3 CU9 19 Apr 2010
9.00.4285 SQL Server 2005 SP3 CU8 16 Feb 2010
9.00.4273 SQL Server 2005 SP3 CU7 21 Dec 2009
9.00.4266 SQL Server 2005 SP3 CU6 19 Oct 2009
9.00.4230 SQL Server 2005 SP3 CU5 17 Aug 2009
9.00.4226 SQL Server 2005 SP3 CU4 16 Jun 2009
9.00.4220 SQL Server 2005 SP3 CU3 21 Apr 2009
9.00.4211 SQL Server 2005 SP3 CU2 17 Feb 2009
9.00.4207 SQL Server 2005 SP3 CU1 20 Dec 2008
9.00.4053 SQL Server 2005 SP3 GDR (Security Update) 13 Oct 2009
9.00.4035 SQL Server 2005 SP3 16 Dec 2008
9.00.3356 SQL Server 2005 SP2 CU17 21 Dec 2009
9.00.3355 SQL Server 2005 SP2 CU16 19 Oct 2009
9.00.3330 SQL Server 2005 SP2 CU15 18 Aug 2009
9.00.3328 SQL Server 2005 SP2 CU14 16 Jun 2009
9.00.3325 SQL Server 2005 SP2 CU13 21 Apr 2009
9.00.3315 SQL Server 2005 SP2 CU12 17 Feb 2009
9.00.3310 SQL Server 2005 SP2 Security Update 10 Feb 2009
9.00.3301 SQL Server 2005 SP2 CU11 15 Dec 2008
9.00.3294 SQL Server 2005 SP2 CU10 20 Oct 2008
9.00.3282 SQL Server 2005 SP2 CU9 18 Aug 2008
9.00.3257 SQL Server 2005 SP2 CU8 16 Jun 2008
9.00.3239 SQL Server 2005 SP2 CU7 14 Apr 2008
9.00.3233 SQL Server 2005 QFE Security Hotfix 8 Jul 2008
9.00.3228 SQL Server 2005 SP2 CU6 18 Feb 2008
9.00.3215 SQL Server 2005 SP2 CU5 17 Dec 2007
9.00.3200 SQL Server 2005 SP2 CU4 15 Oct 2007
9.00.3186 SQL Server 2005 SP2 CU3 20 Aug 2007
9.00.3175 SQL Server 2005 SP2 CU2 28 Jun 2007
9.00.3161 SQL Server 2005 SP2 CU1 15 Apr 2007
9.00.3152 SQL Server 2005 SP2 Cumulative Hotfix 7 Mar 2007
9.00.3077 SQL Server 2005 Security Update 10 Feb 2009
9.00.3054 SQL Server 2005 KB934458 5 Apr 2007
9.00.3042.01 SQL Server 2005 "SP2a" 5 Mar 2007
9.00.3042 SQL Server 2005 SP2 1 Feb 2007
9.00.2047 SQL Server 2005 SP1
9.00.1399 SQL Server 2005 RTM 1 Nov 2005
SQL Server 2000
8.00.2039 SQL Server 2000 SP4
8.00.0760 SQL Server 2000 SP3
8.00.0534 SQL Server 2000 SP2
8.00.0384 SQL Server 2000 SP1
8.00.0194 SQL Server 2000 RTM
SQL Server 7
7.00.1063 SQL Server 7 SP4
7.00.0961 SQL Server 7 SP3 15 Dec 2000
7.00.0842 SQL Server 7 SP2 20 Mar 2000
7.00.0699 SQL Server 7 SP1 15 Jul 1999
7.00.0623 SQL Server 7 RTM
SQL Server 6.5
6.50.416 SQL Server 6.5 SP5a
6.50.415 SQL Server 6.5 SP5
6.50.281 SQL Server 6.5 SP4
6.50.258 SQL Server 6.5 SP3
6.50.240 SQL Server 6.5 SP2
6.50.213 SQL Server 6.5 SP1
6.50.201 SQL Server 6.5 RTM

You can determine what version SQL Server is running by running

Select @@version