Oracle Delete user commands and partial commands

Oracle Delete user commands and partial commands

Drop user user_name cascade;
Drop user user_name cascade;

CREATE TABLE Space

CREATE tablespace DATA01
DataFile '/oracle/oradata/db/data01.dbf ' SIZE 500M
UNIFORM SIZE 128k; #指定区尺寸为128k, if not specified, the zone size defaults to 64k

Delete Table space

DROP tablespace data01 including CONTENTS and datafiles;

I. Create a table space

CREATE tablespace DATA01
DataFile '/oracle/oradata/db/data01.dbf ' SIZE 500M
UNIFORM SIZE 128k; #指定区尺寸为128k, if not specified, the zone size defaults to 64k

Ii. Creating an undo table space

CREATE UNDO tablespace UNDOTBS02
DataFile '/oracle/oradata/db/undotbs02.dbf ' SIZE 50M

#注意: In the open state, you can use only one undo tablespace at some point, and if you want to use a new tablespace, you must switch to that tablespace:

ALTER SYSTEM SET Undo_tablespace=undotbs02;

Iii. Creating a temporary table space

CREATE Temporary tablespace temp_data
Tempfile '/oracle/oradata/db/temp_data.dbf ' SIZE 50M

Iv. changing the state of the table space

1. Take the table space offline

ALTER tablespace game OFFLINE;

If the data file was accidentally deleted, you must have the recover option

ALTER tablespace game OFFLINE for RECOVER;

2. Bring the table space online

ALTER tablespace game ONLINE;

3. Take the data file offline

ALTER DATABASE datafile 3 OFFLINE;

4. Bring the data file online

ALTER DATABASE datafile 3 ONLINE;

5. Make Tablespace read-only

ALTER tablespace game READ only;

6. Make table space readable and writable

ALTER tablespace game READ WRITE;

V. Delete table space

DROP tablespace data01 including CONTENTS and datafiles;

Vi. Extending table Space

First, look at the name of the tablespace and the file it belongs to

Select Tablespace_name, file_id, file_name,
Round (bytes/(1024*1024), 0) Total_space
From Dba_data_files
Order BY Tablespace_name;

1. Add Data files
ALTER tablespace Game
ADD datafile '/oracle/oradata/db/game02.dbf ' SIZE 1000M;

2. Manually add data file size
ALTER DATABASE datafile '/oracle/oradata/db/game.dbf '
RESIZE 4000M;

3. Set data File Auto-expansion
ALTER DATABASE datafile '/oracle/oradata/db/game.dbf
Autoextend on NEXT 100M
MAXSIZE 10000M;

View tablespace information after setting

SELECT a.tablespace_name,a.bytes total,b.bytes used, c.bytes free,
(b.bytes*100)/a.bytes "% used", (c.bytes*100)/a.bytes "% free"
From SYS. Sm$ts_avail A,sys. Sm$ts_used B,sys. Sm$ts_free C
WHERE A.tablespace_name=b.tablespace_name and A.tablespace_name=c.tablespace_name;

This article is from a "a little" blog, make sure to keep this source http://pengai.blog.51cto.com/6326789/1976374

Oracle Delete user commands and partial commands