Modifying Oracle's data files

oracle| data
modifying Data files



Important NOTE:

Make sure to back up the data before you adjust the work, remember

1, Basic concepts

Within the Oracle system, a logical disk space called table space is given. Early systems are used primarily for storing table data, so they are called table spaces. A table space is a logical space in which each space corresponds to at least one or more data files. Look at the picture below.
................................

Other words:

The data of a database is stored together in the data file, and the data files are built in the table space of the database.



System/manager is used by administrators.


2 Displaying Data file names
View Data File Command 1

Select File_name,bytes,bytes/(1024*1024) from Dba_data_files;

View Data File command 2

Autoextensible indicates whether the data file is automatically growing.

The col command was ordered to be neatly printed.

Col Tablespace_name for A12

Col file_name for A48

Select File_id,file_name,tablespace_name,autoextensible from Dba_data_files order by file_id;


3 Oracle Database Optimization 3. 1 Adjusting the system table space
After Oracle completes the installation, the administrator should adjust the storage parameters of the system table space, mainly by adjusting the next value.



Sql>alter tablespace system default storage (next 1M pctincrease 0);


3. 2 Adding data files
You can use the following two ways:



1. Add a data file for this table space

sql> alter tablespace table space name add datafile '/u1/oradata/userdata_002.ora ' size 50m; In--unix

sql> alter tablespace table space name add datafile ' c:\oradata\userdata_002.ora ' size 50m; --windows NT Medium





2, resize the data file

sql> ALTER DATABASE datafile '/u1/oradata/userdata_001.ora ' resize 50M; In--unix

sql> ALTER DATABASE datafile ' C:\oradata\userdata_002.ora ' resize 50M; --windows NT Medium



If you log in with the system user, the table space name should be system, or it may be users, and you can use the syntax of 2 to retrieve it and check it out. The path of the data file you can retrieve the results of the 2 syntax, as long as the filename is not the same.

The following syntax appends a data file to a table space to indicate that the data file is automatically expanded.

Alter tablespace System

Add datafile ' C:\ORACLE\ORADATA\YBSTD99 YSTEM02. DBF ' size 100m

Autoextend on next 100m maxsize 1000M;

ALTER ROLLBACK SEGMENT RB5 STORAGE (Maxextents Unlimited);
3.3 Increase the size of the rollback segment
Retrieving rollback segments

--Method One

Select Segment_name, tablespace_name,status from Sys.dba_rollback_segs;

--Method 2

Col Segment_name for A10

Col Tablespace_name for A10

Select Segment_name, tablespace_name,bytes,extents from

sys.dba_segments where segment_type= ' ROLLBACK ';

--Add a data file for the rollback segment table space RBS, the path needs you to look up, as long as the filename is different--okay.

Alter tablespace RBS add datafile '/u/oradata/en73/rbs02.dat ' size 100M;

--RB5 the regression segment offline

Alter ROLLBACK segment RB5 offline;

--Modify its size

Alter ROLLBACK segment RB5 storage (next 10m maxextents 1024 optimal 10m);

--Rb5 The return segment online

Alter rollback segment RB5 online;
3.4 Modify system's space quota in User_data


1, query the user's resource limit information:

SELECT * from Dba_ts_quota;



2, add data files for tablespace user_data:



Alter tablespace User_data

Add datafile ' C:\ORACLE\ORADATA\YBSTD99\userdata01. DBF ' Size 500m

Autoextend on next 100m maxsize 1000M;



Add two, get it 1g, path with select to find.



3, allocating quotas for system users to user_data the table space

-for example

---alter user system quota 10m on user_data;

---means that the space allocated to the system in the User_data is 10m



--10m is too small,

---We make it quota unlimited:

Alter user system quota unlimited on user_data;



4, perform the 1 select to see the changes