Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to convert dictionary-managed tablespaces into locally managed tablespaces in Oracle". In daily operations, it is believed that many people have doubts about how to convert dictionary-managed tablespaces into locally managed tablespaces in Oracle. The editor consulted all kinds of materials and sorted out simple and useful operation methods. I hope it will be helpful to answer the question of "how to convert dictionary-managed tablespaces into locally managed tablespaces in Oracle!" Next, please follow the editor to study!

SQL > alter table emp move tablespace tbsp_new

SQL > alter index emp_pk_idx rebuild tablespace tbsp_idx_new

Or

SQL > exec dbms_space_admin.tablespace_migrate_to_loca ('user')

Note: however, when using the dbms_space_admin package, the conversion will not be set to ASSM, and the space debris will not disappear.

Create a tablespace

SQL > create tablespace test01 datafile'+ DATA' size 1024M autoextend on next 10M maxsize 4096m extent management local autoallocate segment space management auto

View how tablespaces are managed:

SQL > select tablespace_name,initial_extent,next_extent,min_extents,max_extents,extent_management,segment_space_management,allocation_type from dba_tablespaces order by 6, 7, 8

Syntax:

Create [undo | temporary] tablespace tablesapce_name

Datafile'+ DATA' size 100m

[reuse] when the specified data file already exists, you need to use this parameter to override

[autoextend [off | on]] disables or allows automatic expansion of data files. Default is off.

[next 100m] specify disk space when automatically extending data

Maximum disk space extended by [maxsize [unlimited | 100m]]

[minimum extent 10m] specify the size of the allocated extent

[blocksize 16k] set the size of the block. Note that the default is 8K. If it is changed to 16K, the db_16K_cache_size parameter must be set.

[default storage xx] configure default parameters

[online | offline]

[logging | nologging] indicates whether objects on the tablespace need to be logged. Default is logging.

[force logging] forces all changes on the tablespace to be recorded, except for temporary segments

[extent management [dictionary | local]] extent management method. Local management is recommended.

[autoallocate | uniform [size 20 M]] automatically assigns extent size or fixed extent size

[segment space management [auto | manual]] configure the management of segments. It is recommended to manage them automatically.

Increase tablespace size

SQL > alter tablespace test01 add datafile'+ DATA' size 1000m autoextent on next 10m maxsize 1000m

Redefine the data file size

SQL > alter database datafile 'xxxxx' resize 250m

Delete tablespace

SQL > drop tablespace test01 including contents and datafiles

SQL > drop tablespace test01 cascade constraints; contains foreign key constraints for other tables and that table

Sysaux tablespace removes users

SQL > select occupant_name,schema_name,space_usage_kbytes,move_procedure from v$sysaux_occupants

SQL > execute wksys.move_wk ('TEST01')

Manage tablespace availability

SQL > alter tablespace test01 online normal

SQL > alter tablespace test01 online temporary

SQL > alter tablespace test01 online immediate; execute checkpoint before performing this operation, and perform recover datafile operation before onine

SQL > alter tablespace tets01 offline

Rename tablespace

SQL > alter tablespace test01 rename to test02

Rename the data file

SQL > alter tablespace test01 offline normal

$cp / u01/app/oracle/test01.dbf / u02/app/oracle/test01.dbf

SQL > alter tablespace test01 rename datafile'/ u01ActionAccord Test01.dbf'Test01.dbf'U02According to alter tablespace test01 rename datafile'/ u01amp

SQL > alter tablespace test01 online

Read-only tablespace

SQL > alter tablespace test01 read only

SQL > alter tablespace test01 read write

Configure default tablespace

SQL > alter database default tablespace test01

Up to 64000 datafile in large file tablespace database

Create BFT

SQL > alter tablespace set default bigfile tablespace

SQL > create bigfile tablespace bigtbs_01 datafile'+ DATA' size 100G

SQL > select property_value from database_properties where property_name='DEFAULT_TBS_TYPE'

Change the BFT space

SQL > alter tablespace bigtbs resize 120g

SQL > alter tablespace bigtbs autoextend on next 20g

At this point, the study on "how to convert dictionary-managed tablespaces into locally managed tablespaces in Oracle" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.