Oracle Modified table Space Self-growth

The detailed procedures are listed below:

1. Log in to the database via the SQL Plus command.

Enter Sqlplus "Login username/password as login type" on the command line to log in, the system built user name commonly used is sys, password is set during the installation of Oracle password, clear must remember, if you log in with the SYS account, login type must be SYSDBA.

2. View the allocation of each table space.

Select Tablespace_name, sum (bytes)/1024/1024 from Dba_data_files

Group BY Tablespace_name;

3. View the table space idle situation.

Select Tablespace_name, sum (bytes)/1024/1024 from Dba_free_space Group by Tablespace_name;

4. Change data table size (10G)

ALTER DATABASE datafile '/ORA/ORADATA/RADIUS/UNDO.DBF ' resize 10240m;

5. Set up auto-grow when table space is low

5.1 See if Table spaces grow automatically

SELECT file_name,tablespace_name,autoextensible from Dba_data_files;

5.2 Setting table space Auto-growth

ALTER DATABASE datafile ' C:\SmartDB01.ora ' autoextend on;//turn on auto-grow

ALTER DATABASE datafile ' C:\SmartDB01.ora ' autoextend on NEXT 200M;//auto-grow 200m per time

ALTER DATABASE datafile ' C:\SmartDB01.ora ' autoextend on NEXT 200M MAXSIZE 1024m;//each auto-growth 200m, data table maximum not more than 1G

 1. View the default tablespace and data files used by Oracle users sql> Select Username,default_tablespace,temporary_tablespace   from dba _users where username = ' SCOTT ';     username  default_tablespace  TEMPORARY_TABLESPACE   ------------------------------------------------------------    SCOTT     USERS               temp     sql> col name format a50    Sql> Select t1.name,t2.name,t2.file#  --View tablespace and data files       2  from v$tablespace t1,v$datafile t2      3  where t1.ts# = t2.ts#; 2. See if the tablespace is an auto-growing   SELECT file_name,tablespace_name,autoextensible from Dba_data_files; 3. If the table space does not grow automatically, you need to change the tablespace to autogrow. The statement follows:  ALTER DATABASE datafile ' ... ' | Fileno Autoextend on | OFF NEXT 20M MAXSIZE 300m;  ALTER DATABASE datafile ' C:\SmartDB01.ora ' autoextend on;  --Set data file autogrow    ALTER DATABASE datafile ' C:\SmartDB02.ora ' &NBsp MAXSIZE UNLIMITED;  --Set data file capacity Unlimited    If you have multiple data files, set these data files to self-growth   

First log in to a user to see which table space a user is in:

Select Username,default_tablespace from User_users;

View all the tables under the user:

SELECT * from User_tables;

View all table names, paths, and autogrow:

Select tablespace_name,file_name,autoextensible from Dba_data_files;

To modify table Space autogrow properties:

ALTER DATABASE datafile ' table space path ' autoextend on;

It is possible to execute the previous query again, whether it succeeds or not, and how the table space is self-growing.

Modify Size:

Alter database datafile ' table space path ' resize 2000m;

To view the current number of connections:

Sql> Select COUNT (*) from v$session;

To view the maximum number of processes:

Sql> Show parameter processes;

To view the maximum number of connections:

Sql> Show parameter sessions;

Oracle Modified table Space Self-growth