| USERS tablespace reached 96% after manual db creation w/ 1 new schema only [message #499202] |
Mon, 14 March 2011 01:41  |
reym21
Messages: 241
Registered: February 2010
Location: Philippines
|
Senior Member |
|
|
Sirs,
Please help me solved this problem.
I created manually a database in 10g, after succesfully creating the dB, I created a single user re: LAMS. Now, I noticed that my USERS tablespace is currently at a 99.96% usage:
SQL> @check_space_used.sql
Monday, March 14, 2011 2:46:22 PM SGT
TABLESPACE_NAME TOTALSPACE TOTALBYTES PERCENTUSED
------------------------------ -------------------- -------------------- --------------------
SYSTEM 1073741824 239599616 23
UNDO 268435456 16449536 7
USERS 2546991104 2546008064 100
SYSAUX 1073741824 84803584 8
check_space_used.sql
---------------------
!date
set numwidth 20
set linesize 120
select a.tablespace_name,a.dbytes "TOTALSPACE", b.sbytes "TOTALBYTES",
ceil((b.sbytes / a.dbytes) * 100) "PERCENTUSED"
from (select tablespace_name,sum(bytes) as dbytes from dba_data_files group by
tablespace_name) a,
(select tablespace_name,sum(bytes) as sbytes from dba_segments group by tablespace_name) b
where a.tablespace_name=b.tablespace_name;
spool off
Below is the syntax I used when I created it:
---------------------------------------------
CREATE TABLESPACE users
DATAFILE '/VOL03/oradata/elams/data/users01.dbf' SIZE 100M
AUTOEXTEND ON NEXT 512K
MINIMUM EXTENT 64K
DEFAULT STORAGE(INITIAL 256K NEXT 128K MINEXTENTS 2 MAXEXTENTS 2048);
The schema in the current dB:
-----------------------------
SQL> select username from dba_users;
USERNAME
------------------------------
OUTLN
SYS
SYSTEM
LAMS
DBSNMP
TSMSYS
DIP
ORACLE_OCM
8 rows selected.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
