How to Oracle Data Base for Beginners II

1.- Increase Undo tablespace database

SQL>select * from V$undostat;
SQL>select * from dba_data_files where Tablespace_Name like 'UNDO%'
SQL>ALTER DATABASE DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\TORITO\UNDOTBS01.DBF' RESIZE 100M;

2- Increase TEMP tablespace database

 2.1 Check temp file location

 SQL>select * from V$TEMPFILE;
 SQL>ALTER DATABASE TEMPFILE C:\ORACLE\PRODUCT\10.2.0\ORADATA\TORITO\TEMP01.DBF RESIZE 300M;

3.-Verify TEMP tablespace database

SQL>select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;
TABLESPACE_NAME                BYTES_USED BYTES_FREE
------------------------------ ---------- ----------
TEMP                           2021654528 2273312768

4.-Check what TEMP tablespace are using the users

SQL>SELECT USERNAME, TEMPORARY_TABLESPACE FROM DBA_USERS;

5- Recreate the TEMP table space with another name
SQL>CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE
  'C:\ORACLE\PRODUCT\10.2.0\ORADATA\TORITO\TEMP02.DBF' SIZE 500M AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
TABLESPACE GROUP ''
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
SQL>CREATE TEMPORARY TABLESPACE TEMP TEMPFILE
  'C:\ORACLE\PRODUCT\10.2.0\ORADATA\TORITO\TEMP01.DBF' SIZE 300M AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
TABLESPACE GROUP ''
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
SQL>CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE
  'C:\ORACLE\PRODUCT\10.2.0\ORADATA\TORITO\TEMP02.DBF' SIZE 500M AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
TABLESPACE GROUP ''
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

SQL>ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

SQL>DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
SQL>DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

SQL>SELECT * FROM DBA_TEMP_FILES;
SQL>SELECT * FROM DBA_DATA_FILES;
SQL>CREATE TEMPORARY TABLESPACE TEMP TEMPFILE
  'C:\ORACLE\PRODUCT\10.2.0\ORADATA\TORITO\TEMP01.DBF' SIZE 100M AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
TABLESPACE GROUP ''
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

SQL>ALTER DATABASE ADD TEMPFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\TORITO\TEMP01.DBF' SIZE 500M AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED;

SQL>ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

6- Check Oracle DataBase parameters in the Oracle Data Base

SQL>select * from v$system_parameter;

7- Increase SGA

Stop database

SQL>shutdown immediate;

startup with no mount

SQL>startup nomount;

 Change the size in the sga_max_size parameter

SQL>alter system set sga_max_size=2G scope=spfile;
SQL> shutdown immediate;
SQL>startup;