This content has been marked as final. Show 7 replies
on 10g R2, on AIX 6.1, I have the following in DB control/Policy violation.
I can not understand what it wants to say and do not know what I should do.
Default Permanent Tablespace Set to a System Tablespace
Thanks for help.
It means default tablespace for new created user will this tablespace.If it is system tablespace then change it as
select PROPERTY_VALUE from database_properties where PROPERTY_NAME='DEFAULT_PERMANENT_TABLESPACE'
alter database default tablespace users;
If default permanent tablespace is not specified,then SYSTEM will implicitly become the default permanent tablespace.
Create a new temporary tablespace using the command below
CREATE TEMPORARY TABLESPACE tablespace_name
[EXTENT MANAGEMENT LOCAL]
[UNIFORM [SIZE int K | M] ];
'filespec' [AUTOEXTEND OFF]
'filespec' [AUTOEXTEND ON [NEXT int K | M] [MAXSIZE int K | M]]
Then define this created temp tablespace as your default tablespace
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE <temporary tablespace_name>;
and try to query from V$TEMPFILE or DBA_TEMP_FILES and send the value.
Oracle is a complex system and it loves an order.
Oracle uses the SYSTEM tablespace for its own needs.
It's neither healthy nor good from performance, manageability, usability points of view - to keep your own/applicative objects in the Oracle's SYSTEM tablespace.
To prevent people accidentally having their db objects in the SYSTEM tablespace Oracle introduced a default permanent tablespace.
The DEFAULT TABLESPACE clause in the CREATE DATABASE statement allows to set the default tablespace to be created and named.
If this parameter is not set during creation it can be set "on the fly" using the following command:
ALTER DATABASE DEFAULT TABLESPACE users; or ALTER DATABASE DEFAULT TABLESPACE <any_existing_tablespace_you_want_to_be_default>;