Shulou(Shulou.com)06/01 Report--

I. create tablespaces

SQL > Create smallfile/bigfile tablespace tbs_newstores

DATAFILE'/ u01 apprenticeship oracle11gUniverse tbswords newstores.dbf'

Size 100m autoextend on next 10m maxsize 300m

Logging

Extent management local

Segment space management auto

Default nocompress

Second, change the tablespace

(1) rename tablespaces and data files

# # CREATETABLESPACE

SQL > Create smallfile / bigfile tablespace TBS_NEWSTORES

DATAFILE'/ u01 SIZE SIZE 5m

'/ u01Applicon SIZE oracle11g Universe tbswords newstores02.dbf' oracle50m

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 5M

# # RENAMETABLESPACE

SQL > ALTER TABLESPACE TBS_NEWSTORES RENAME to TBS_NEWSTORE

SQL > SELECT NAME,DATAFILE,STATUS FROM V$DATAFILE

WHERE NAME LIKE'%TBS_NEWSTORES%'

# # MAKETABLESPACE OFFLINE

SQL > ALTER TABLESPACE TBS_NEWSTORE OFFLINE

SQL > SELECT NAME,DATAFILE,STATUS FROM V$DATAFILE

WHERE NAME LIKE'%TBS_NEWSTORES%'

# # RENAMEDATAFILE

SQL >! RENAME / u01/app/oracle/oracle11g/tbs_newstores01.dbf tbs_newstore01.dbf

SQL >! RENAME / u01/app/oracle/oracle11g/tbs_newstores02.dbf tbs_newstore02.dbf

SQL > SELECT T.NAME TABLESPACE_NAME, D.NAME DATAFILE_NAME

FROM V$TABLESPACE T JOIN V$DATAFILE D USING (TS#)

WHERE T.NAME=TBS_NEWSTORE

# # RENAMEDATAFILE_NAME

SQL > ALTER DATABASE RENAME FILE'/ u01ActionApplicationoracle11g Universe Tbswords newstores01.dbf'

TO'/ u01qapplash oracle11gUniverse tbswarehouse newstore01.dbf'

SQL > ALTER DATABASE RENAME FILE'/ u01ActionApplicationoracle11g Universe Tbswords newstores02.dbf'

TO'/ u01qapplash oracle11g Universe newstore02.dbf'

# # MAKETABLESPACE ONLINE

SQL > ALTER TABLESPACE TBS_NEWSTORE ONLINE

(2) tablespaces offline and online

ALTER TABLESPACE tablespacename offline [NORMAL | IMMEDIATE | TEMPORARY]

(3) Mark tablespaces as read-only

ALTER TABLESPACE tablespacename [READ ONLY | READ WRITE]

(4) resize the tablespace

ALTER DATABASE DATAFILE filename RESIZE n [M | G | T]

# eg.

SQL > alter database datafile'/ oradata/users02.dbf' resize 20m

-- add a 50m data file to the tablespace

SQL > alter tablespace ts_newstore

Add datafile'/ oradata/ts_newstore03.dbf' size 50m

You can add an automatic extension sentence, or start the automatic extension later using the following command

SQL > alter database datafile'/ oradata/ts_newstore03.dbf'

Autoextendon next 50m maxsize 2G

(5) OMF (Oracle-Managed Files)

To enable OMF, some or all of the following parameters must be set:

DB_CREATE_FILE_DEST-the parameter specifies the default location of all data files

DB_CREATE_ONLINE_LOG_DEST_n-specifies the default location of the online redo log file

DB_RECOVERY_FILE_DEST-sets the default location for archive redo log files and backup files

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.