OCP043 11th management Storage

 

The content of this lecture mainly includes:

1. segment shrinkage and table space usage monitoring

2. Perform segment contraction and roll back the high-water line

3. Set session suspension due to space problems

4. Transfer tablespace

I. segment shrinkage: Segment shrinkage: row chaining and row migrate

1. Simulate row chaining row links

When the row is inserted for the first time, because the row is too long to be contained in a data block, a row link occurs. In this case, oracle uses one or more data blocks linked to the block to accommodate the data of the row. Row connections often occur when large rows are inserted, such as long, long row, and lob data. In these cases, downstream links are inevitable.

[Root @ rhel6 ~] # Su-oracle

[Oracle @ rhel6 ~] $ Sqlplus/nolog

SQL * Plus: Release 10.2.0.1.0-Production on Wed Aug 3 09:28:11 2011

Copyright (c) 1982,200 5, Oracle. All rights reserved.

 

SQL> conn/as sysdba

Connected.

SQL> @? /Rdbms/admin/utlchain. SQL

Table created

 

SQL> create table hr. tocp11_long (a varchar2 (2000), B varchar2 (2000), c varchar2 (2000 ),

2 d varchar2 (2000), e varchar2 (2000), f varchar2 (2000), g varchar2 (2000), h varchar2 (2000 ),

3 I varchar2 (2000), j varchar2 (2000 ));

Table created.

 

SQL> insert into tocp11_long (a) values ('here is 2000 ');

1 row created.

 

SQL> update tocp11_long set B = ('here is 2000 ');

1 row created.

 

SQL> update tocp11_long set c = ('here is 2000 ');

1 row created.

 

SQL> commit;

Commit complete.

 

SQL> analyze table hr. tocp11_long list chained rows;

Table analyzed.

 

SQL> select * from chained_rows;

No rows selected

 

SQL> update tocp11_long set d = ('here is 2000 ');

1 row created.

 

SQL> commit;

Commit complete.

 

SQL> analyze table hr. tocp11_long list chained rows;

Table analyzed

 

SQL> select owner_name, table_name, head_rowid from chained_rows;

 

OWNER_NAME TABLE_NAME HEAD_ROWID

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

HR TOCP11_LONG AAAQxUAAEAAAHL9AAA

2. Simulate row migrate row migration

When the modified row is not a row link, when the modified row length is greater than the length of the row before the modification, and the free space in the data block is small, it cannot fully accommodate the data of the row, row migration will occur. In this case, Oracle will migrate the entire row of data to a new data block, and put only one pointer to the original space of the row, pointing to the new location of the row, the remaining space of the row is no longer used by the database.

SQL> create table hr. t04311_migrate (a varchar2 (2000) pctfree 0;

Table created.

 

SQL> begin

2 for I in 1 .. 2000

3 loop

4 insert into hr. t04311_migrate values ('A ');

5 end loop;

6 commit;

7 end;

8/

PL/SQL procedure successfully completed.

 

SQL> update hr. t04311_migrate set a = 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaa ';

2000 rows updated.

 

SQL> commit;

Commit complete.

 

SQL> analyze table hr. t04311_migrate list chained rows;

Table analyzed.

 

SQL> select owner_name, table_name, head_rowid from chained_rows where rownum <= 5;

 

OWNER_NAME TABLE_NAME HEAD_ROWID

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

HR TOCP11_LONG AAAQxUAAEAAAHL9AAA

HR T04311_MIGRATE AAAQxWAAEAAAHMFAAA

HR T04311_MIGRATE AAAQxWAAEAAAHMFAAB

HR T04311_MIGRATE AAAQxWAAEAAAHMFAAC

HR T04311_MIGRATE AAAQxWAAEAAAHMFAAD

3. Use dbconsole to monitor table space usage

 

2. Perform segment contraction and roll back the high-water line. After segment contraction, the system will not be able to flash back. At the same time, row movement must be enabled. For tablespaces not managed by ASSM, segment contraction cannot be performed.

SQL> create table hr. t04311_big as select * from dba_source;

Table created.

 

SQL> select count (*) from dba_extents e where e. owner = 'hr' and e. segment_name = 't04311 _ Day ';

 

COUNT (*)

----------

67

SQL> delete from hr. t04311_big;

323191 rows deleted.

 

SQL> select count (*) from dba_extents e where e. owner = 'hr' and e. segment_name = 't04311 _ Day ';

 

COUNT (*)

----------

67

 

SQL> alter table hr. t04311_big enable row movement;

Table altered.

 

SQL> alter table hr. t04311_big shrink space compact;

Table altered.

 

SQL> select count (*) from dba_extents e where e. owner = 'hr' and e. segment_name = 't04311 _ Day ';

 

COUNT (*)

----------

67

SQL> alter table hr. t04311_big shrink space;

Table altered.

 

SQL> select count (*) from dba_extents e where e. owner = 'hr' and e. segment_name = 't04311 _ Day ';

 

COUNT (*)

----------

1

 

If there is an index, you can add the cascade keyword when shrinking segments, or use the following command to contract separately to recreate the index.

SQL> alter index hr. EMP_LAST_NAME_IDX coalesce;

Index altered

 

SQL> alter index hr. EMP_LAST_NAME_IDX rebuild online;

Index altered.

 

3. Due to space problems, set the session to suspend. The default timeout value is 7200 seconds.

SQL> revoke unlimited tablespace from hr;

Revoke succeeded

 

SQL> grant resumable to hr;

Grant succeeded.

 

SQL> create table t100 as select * from orders;

Create table t100 as select * from orders *

ERROR at line 1:

ORA-01536: space quota exceeded for tablespace 'users'

 

SQL> alter session enable resumable timeout 5;

Session altered.

 

SQL> create table t100 as select * from orders;

Create table t100 as select * from orders ments

*

ERROR at line 1:

ORA-30032: the suincluded (resumable) statement has timed out

ORA-01536: space quota exceeded for tablespace 'users'

 

SQL> select status, SQL _text from dba_resumable;

 

STATUS SQL _TEXT

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

Sushortded create table t100 as select *

From orders ments

Use triggers to automatically handle session suspension due to space problems

 

 

SQL> create tablespace tbs04311_small datafile '/u01/app/oradata/ora10g/tbs04311_small01.dbf' size 512 k autoextend off;

Tablespace created.

 

SQL> grant unlimited tablespace to hr;

Grant succeeded.

 

SQL> conn hr/hr

Connected.

SQL> create table t04311_t (a number) tablespace tbs04311_small storage (initial 1 M );

Create table t04311_t (a number) tablespace tbs04311_small storage (initial 1 M)

*

ERROR at line 1:

ORA-30032: the suincluded (resumable) statement has timed out

ORA-01659: unable to allocate MINEXTENTS beyond 7 in tablespace TBS04311_SMALL

 

SQL> conn/as sysdba

SQL> CREATE OR REPLACE TRIGGER SYS. TRG_SUSPEND

2 AFTER SUSPEND

3 ON DATABASE

4 declare

5 v_size number;

6 pragma AUTONOMOUS_TRANSACTION;

7 begin

8 select bytes into v_size from dba_data_files where file_name = '/u01/app/oradata/ora10g/tbs04311_small01.dbf ';

9 v_size: = v_size + 1048576;

10 execute immediate 'alter database datafile'/u01/app/oradata/ora10g/tbs04311_small01.dbf ''resize' | v_size;

11 commit;

12 * end;

SQL>/

 

Trigger created.

 

SQL> select * from user_errors;

No rows selected

 

 

SQL> select bytes from dba_data_files where tablespace_name = upper ('tbs04311 _ small ');

 

BYTES

----------

524288

SQL> conn hr/hr

Connected.

SQL> alter session enable resumable;

Session altered.

 

SQL> create table t04311_t (a number) tablespace tbs04311_small storage (initial 1 M );

Table created.

 

SQL> select bytes from dba_data_files where tablespace_name = upper ('tbs04311 _ small ');

 

BYTES

----------

1572864

4. Transfer tablespace

It can be cross-platform and cross-indian format, but the character set must be consistent. The COMPATIBLE parameter must be greater than 10.0.0. The tablespace must be in read-only status before transmission.

Source Server:

C: \> set ORACLE_SID = winorcl2

C: \> sqlplus/nolog

 

SQL * Plus: Release 10.2.0.1.0-Production on Wednesday August 3 13:23:27 2011

Copyright (c) 1982,200 5, Oracle. All rights reserved.

SQL> conn/as sysdba

Connected.

 

SQL> select property_value from database_properties where property_name = 'nls _ CHARACTERSET ';

 

PROPERTY_VALUE

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

AL32UTF8

 

SQL> create tablespace tbs_transport datafile 'e: \ oracle \ product \ 10.2.0 \ oradata \ winorcl2 \ tbs_transport01.dbf' size 10 M;

The tablespace has been created.

 

SQL> create table hr. t04311_trans (a number) tablespace tbs_transport;

The table has been created.

 

SQL> insert into hr. t04311_trans values (1000 );

One row has been created.

 

SQL> commit;

Submitted.

 

SQL> create directory dir01 as 'e: \ data ';

The directory has been created.

 

SQL> alter tablespace tbs_transport read only;

The tablespace has been changed.

 

C: \> expdp 'sys/123456 as sysdba 'directory = dir01 dumpfile = 1.dmp transport_tablespaces = tbs_transport

 

Export: Release 10.2.0.1.0-Production on Wednesday, 03 August 1, 2011 13:39:08

Copyright (c) 2003,200 5, Oracle. All rights reserved.

Connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production

With the Partitioning, OLAP and Data Mining options

Start "SYS". "SYS_EXPORT_TRANSPORTABLE_01": 'sys/******** as sysdba 'didirectory = di

R01 dumpfile = 1.dmp transport_tablespaces = tbs_transport

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/TABLE

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

The master table "SYS". "SYS_EXPORT_TRANSPORTABLE_01" is successfully loaded/uninstalled"

**************************************** **************************************

The dump file set of SYS. SYS_EXPORT_TRANSPORTABLE_01 is:

E: \ DATA \ 1.DMP

The job "SYS". "SYS_EXPORT_TRANSPORTABLE_01" was successfully completed at 13:40:13.

 

C: \> sqlplus/nolog

SQL * Plus: Release 10.2.0.1.0-Production on Wednesday August 3 13:42:02 2011

Copyright (c) 1982,200 5, Oracle. All rights reserved.

 

SQL> conn/as sysdba

Connected.

SQL> alter tablespace tbs_transport read write;

The tablespace has been changed.

 

Destination:

[Root @ rhel6 ~] # Mount-t cifs-o username = yang // 192.168.50.195/data/mnt

Password:

[Root @ rhel6 ~] # Ls/mnt

1. DMP export. log TBS_TRANSPORT01.DBF

 

[Oracle @ rhel6 ~] $ Cp/mnt/*/home/oracle/data/

[Oracle @ rhel6 ~] $ Ls/home/oracle/data/

1. dmp export. log tbs_transport01.dbf

[Oracle @ rhel6 ~] $ Cp/home/oracle/data/tbs_transport01.dbf/u01/app/oradata/ora10g/

 

[Oracle @ rhel6 ~] $ Sqlplus/nolog

SQL * Plus: Release 10.2.0.1.0-Production on Wed Aug 3 13:50:59 2011

Copyright (c) 1982,200 5, Oracle. All rights reserved.

SQL> conn/as sysdba

Connected.

SQL> create directory dir01 as '/home/oracle/data ';

Directory created.

 

 

[Oracle @ rhel6 data] $ impdp \ 'sys/123456 as sysdba \ 'directory = dir01 dumpfile = 1.dmp transport_datafiles ='/u01/app/oradata/ora10g/tbs_transport01.dbf'

Import: Release 10.2.0.1.0-64bit Production on Wednesday, 03 August, 2011 14:16:46

Copyright (c) 2003,200 5, Oracle. All rights reserved.

 

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bit Production

With the Partitioning, OLAP and Data Mining options

Master table "SYS". "SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded

Starting "SYS ". "SYS_IMPORT_TRANSPORTABLE_01": 'sys/******** as sysdba 'directory = dir01 dumpfile = 1.dmp transport_datafiles =/u01/app/oradata/ora10g/tbs_transport01.dbf

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/TABLE

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Job "SYS". "SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 14:16:54

 

SQL> select file_name from dba_data_files where tablespace_name = 'tbs _ TRANSPORT ';

FILE_NAME

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

/U01/app/oradata/ora10g/tbs_transport01.dbf

 

SQL> select * from hr. t04311_trans;

 

A

----------

1000

 

This article is from the "yueda tianchong" blog