Modify data file
important hint:
Be sure to back up the data before adjusting, remember 1, basic concept
An Oracle system gives a logical disk space called "Table Space". The early system is mainly used to store the data of the table, so it is called a table space. The tablespace is a logical space, and each space corresponds to one or more data files. Look down
. ..............................
That is to say:
The data of a database is stored in the data file, and the data file is built in the table space of the database.
System / manager is administrator.
2 Display data file name
/ / 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 increased.
// COL command is to output neatly
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 Adjust the System Table Space
Oracle After the installation, the administrator should adjust the storage parameters of the System table space, mainly to adjust the NEXT value.
SQL> ALTER TABLESPACE SYSTEM Default Storage (Next 1M Pctincrease 0);
3.2 Adding data files
The following two methods can be used:
1. Add a data file for this table space
SQL> ALTER TABLESPACE Table Space Name Add DataFile '/u1/oradata/UserData_002.ora' size 50m; - inunix
SQL> ALTER TABLESPACE Table Space Name Add DataFile 'C: /oradata/UserData_002.ora' size 50m; - Windows NT
2, re-adjust the size of the data file
SQL> ALTER DATABASE DATAFILE '/U1/Ordata/UserData_001.ora' resize 50m; - inunix
SQL> ALTABASE DATAFILE 'C: /ORADATA/UserData_002.ora' resize 50m; - Windows NT
If you log in with a System user, the table space name should be system, or it may be users, you use 2 syntax to search, check the situation. The path of the data file You can simultaneously retrieve the results of 2 syntax, as long as the file name is different.
The following syntax adds data files to a table space, indicating that the data file is automatically expanded.
Alter TableSpace System
Add DataFile 'C: /oracle/oradata/ybstd99/system02.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
Retrieve the rollback segment
--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 split segment table space RBS, the path requires you to find, as long as the file name is different - you can
Alter TableSpace Rbs Add DataFile '/u/oradata/en73/rbs02.dat' size 100m;
- Re-return paragraph RB5 offline
Alter Rollback Segment RB5 OFFLINE;
- Modify its small
Alter Rollback Segment RB5 Storage (Next 10M MaxExtents 1024 Optimal 10M);
- will return to the paragraph RB5 online
Alter rollback segment rb5 online;
3.4 Modify System Space Quota in User_Data
1. Query users' 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, do 1G, the path is found with SELECT.
3. Assign the quota of the system user_data in the table space
--E.g
--- Alter User System Quota 10M on User_Data;
--- meaning user_data to allocate 10M space to System
--10m too small,
--- We make it quotas unlimited:
Alter User System Quota Unlimited on User_Data;
4, execute 1 SELECT to see the modification
