| memory consumption - Insert script [message #569941] |
Sat, 03 November 2012 09:56  |
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
|
Senior Member |
|
|
Dear All,
I ran below script -
SQL> insert into ash_emp select * from ash_emp; -- initially, tables has only 14 rows
SQL> /
114688 rows created.
SQL> /
229376 rows created.
SQL> /
458752 rows created.
SQL> //
917504 rows created.
SQL> /
1835008 rows created.
SQL> /
3670016 rows created.
SQL> /
7340032 rows created.
SQL> /
14680064 rows created.
SQL> /
29360128 rows created.
while seing simultaneously at the dba_free_space table, bytes were fluctuating rapidly. What could be the reason?
SQL> select tablespace_name, sum(bytes/1024) "KB" from dba_free_space
group by tablespace_name;
TABLESPACE_NAME KB
------------------------------ ----------
UNDOTBS1 [b]2048[/b]
SYSAUX 6528
USERS [b]2112[/b]
SYSTEM 1024
SQL> /
TABLESPACE_NAME KB
------------------------------ ----------
UNDOTBS1 2048
SYSAUX 6528
USERS 2112
SYSTEM 1024
SQL> /
TABLESPACE_NAME KB
------------------------------ ----------
UNDOTBS1 1024
SYSAUX 6528
USERS 2880
SYSTEM 1024
SQL> /
TABLESPACE_NAME KB
------------------------------ ----------
UNDOTBS1 1024
SYSAUX 6528
USERS 2880
SYSTEM 1024
SQL> /
TABLESPACE_NAME KB
------------------------------ ----------
UNDOTBS1 1024
SYSAUX 6528
USERS 3648
SYSTEM 1024
SQL> /
TABLESPACE_NAME KB
------------------------------ ----------
UNDOTBS1 1024
SYSAUX 6528
USERS 3648
SYSTEM 1024
SQL> /
TABLESPACE_NAME KB
------------------------------ ----------
UNDOTBS1 896
SYSAUX 6528
USERS 4416
SYSTEM 1024
SQL> /
TABLESPACE_NAME KB
------------------------------ ----------
UNDOTBS1 448
SYSAUX 6528
USERS 5184
SYSTEM 1024
SQL> /
TABLESPACE_NAME KB
------------------------------ ----------
UNDOTBS1 [b]64[/b]
SYSAUX 6528
USERS 5184
SYSTEM 1024
SQL> /
TABLESPACE_NAME KB
------------------------------ ----------
UNDOTBS1 4096
SYSAUX 6528
USERS 5952
SYSTEM 1024
SQL> /
TABLESPACE_NAME KB
------------------------------ ----------
UNDOTBS1 4096
SYSAUX 6528
USERS 5952
SYSTEM 1024
SQL> /
TABLESPACE_NAME KB
------------------------------ ----------
UNDOTBS1 4096
SYSAUX 6528
USERS 6720
SYSTEM 1024
SQL> /
TABLESPACE_NAME KB
------------------------------ ----------
UNDOTBS1 4096
SYSAUX 6528
USERS 6720
SYSTEM 1024
SQL> /
TABLESPACE_NAME KB
------------------------------ ----------
UNDOTBS1 3072
SYSAUX 6528
USERS [b]7488[/b]
SYSTEM 1024
SQL> /
TABLESPACE_NAME KB
------------------------------ ----------
UNDOTBS1 3072
SYSAUX 6528
USERS 7488
SYSTEM 1024
SQL> /
TABLESPACE_NAME KB
------------------------------ ----------
UNDOTBS1 3072
SYSAUX 6528
USERS 8256
SYSTEM 1024
SQL> /
TABLESPACE_NAME KB
------------------------------ ----------
UNDOTBS1 3072
SYSAUX 6528
USERS 8256
SYSTEM 1024
SQL> /
TABLESPACE_NAME KB
------------------------------ ----------
UNDOTBS1 3072
SYSAUX 6528
USERS 1088
SYSTEM 1024
SQL> /
TABLESPACE_NAME KB
------------------------------ ----------
UNDOTBS1 2048
SYSAUX 6528
USERS 1856
SYSTEM 1024
SQL> /
TABLESPACE_NAME KB
------------------------------ ----------
UNDOTBS1 2048
SYSAUX 6528
USERS 1856
SYSTEM 1024
SQL> /
TABLESPACE_NAME KB
------------------------------ ----------
UNDOTBS1 2048
SYSAUX 6528
USERS 2624
SYSTEM 1024
SQL> ////
TABLESPACE_NAME KB
------------------------------ ----------
UNDOTBS1 1024
SYSAUX 6528
USERS 2624
SYSTEM 1024
SQL> /
TABLESPACE_NAME KB
------------------------------ ----------
UNDOTBS1 1024
SYSAUX 6528
USERS 3392
SYSTEM 1024
SQL> /
TABLESPACE_NAME KB
------------------------------ ----------
UNDOTBS1 1024
SYSAUX 6528
USERS 4160
SYSTEM 1024
SQL> /
TABLESPACE_NAME KB
------------------------------ ----------
UNDOTBS1 832
SYSAUX 6528
USERS 4160
SYSTEM 1024
SQL> /
TABLESPACE_NAME KB
------------------------------ ----------
UNDOTBS1 576
SYSAUX 6528
USERS 4928
SYSTEM 1024
SQL> /
TABLESPACE_NAME KB
------------------------------ ----------
UNDOTBS1 256
SYSAUX 6528
USERS 5696
SYSTEM 1024
SQL> /
TABLESPACE_NAME KB
------------------------------ ----------
UNDOTBS1 64
SYSAUX 6528
USERS 5696
SYSTEM 1024
SQL> /
TABLESPACE_NAME KB
------------------------------ ----------
UNDOTBS1 4096
SYSAUX 6528
USERS 6464
SYSTEM 1024
SQL> /
TABLESPACE_NAME KB
------------------------------ ----------
UNDOTBS1 4096
SYSAUX 6528
USERS 6464
SYSTEM 1024
SQL> /
TABLESPACE_NAME KB
------------------------------ ----------
UNDOTBS1 4096
SYSAUX 6528
USERS 7232
SYSTEM 1024
SQL> /
TABLESPACE_NAME KB
------------------------------ ----------
UNDOTBS1 3072
SYSAUX 6528
USERS 7232
SYSTEM 1024
SQL> /
TABLESPACE_NAME KB
------------------------------ ----------
UNDOTBS1 3072
SYSAUX 6528
USERS 8000
SYSTEM 1024
SQL> /
TABLESPACE_NAME KB
------------------------------ ----------
UNDOTBS1 3072
SYSAUX 6528
USERS 8000
SYSTEM 1024
SQL> /
TABLESPACE_NAME KB
------------------------------ ----------
UNDOTBS1 3072
SYSAUX 6528
USERS 8768
SYSTEM 1024
SQL> /
TABLESPACE_NAME KB
------------------------------ ----------
UNDOTBS1 3072
SYSAUX 6528
USERS 8768
SYSTEM 1024
SQL> /
TABLESPACE_NAME KB
------------------------------ ----------
UNDOTBS1 2048
SYSAUX 6528
USERS [b]1600[/b]
SYSTEM 1024
SQL> /
TABLESPACE_NAME KB
------------------------------ ----------
UNDOTBS1 2048
SYSAUX 6528
USERS 2368
SYSTEM 1024
User is scott and default permanent tablespace is USERS. Why there is up-down in bytes?
Please make me understand about it?
Regards,
Ishika
|
|
|
|
|
|
| Re: memory consumption - Insert script [message #569948 is a reply to message #569945] |
Sat, 03 November 2012 10:53  |
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
|
Senior Member |
|
|
Dear Michel,
While inserting rows in a table, bytes of USERS tablespace should decrease. But, instead of decreasing, its fluctating. My main concern is about USER tablespace (which is default tablespace).
Regards,
Ishika
|
|
|
|
|
|
|
|
|
|
| Re: memory consumption - Insert script [message #569953 is a reply to message #569951] |
Sat, 03 November 2012 12:49 |
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
|
Senior Member |
|
|
You are correct Mr. BlackSwan.
Actually auto extensible applies to data files; not tablespace.
Thanks for clarifying the concept of free space. If you know the link about the concept of free spaces, please share.
Regards,
Ishika
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
