Home » RDBMS Server » Server Utilities » after executing select queries file sys becomes full
after executing select queries file sys becomes full [message #73845] Thu, 22 July 2004 23:50 Go to next message
Suchita
Messages: 13
Registered: May 2002
Junior Member
hello,

i am working on

platform linux ver 9
oracle  9i
oracle is installed in one of the file system.
it has  one database.

when we execute simple queries like (select) the database size increases which results in total increase of
/home file system & due to that file system becomes full.
we tried to find out which file of database increases after executing select query
but in database files (.ctl,.log,.dbf) we could not found any changes in size of ( .ctl,.log,.dbf) files.
but there is increase in total size of database.

so why select queries causes the file system & database size to grow ?

follwing is the extract of command df -k for file size and du -k for database size.

BEFORE EXECUTING SELECT QUERY

[[oracle1@home oradata]]$ df -k
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/sda3             33056324   2296404  29080728   8% /
/dev/sda1               202220     15084    176696   8% /boot
/dev/sdb5             11590492   9975064   1026648  91% /home
/dev/sda2              2016044     32828   1880804   2% /lmm
/dev/sdb3             11590524    952160  10049584   9% /misc
none                    514888         0    514888   0% /dev/shm
/dev/sdb1             10080488   1886812   7681608  20% /usr

[[oracle1@home oradata]]$
[[oracle1@home oradata]]$ du -k

7774740 ./mobile1

[[oracle1@home oradata]]$ cd mobile1
[[oracle1@home mobile1]]$ ls -lt

total 7774736
-rwxrwxrwx    1 oracle1  oracle1   2023424 Jul 22 14:58 control01.ctl
-rwxrwxrwx    1 oracle1  oracle1   2023424 Jul 22 14:58 control02.ctl
-rwxrwxrwx    1 oracle1  oracle1   2023424 Jul 22 14:58 control03.ctl
-rwxrwxrwx    1 oracle1  oracle1  104858112 Jul 22 14:57 redo03.log
-rwxrwxrwx    1 oracle1  oracle1  20979712 Jul 22 14:37 cwmlite01.dbf
-rwxrwxrwx    1 oracle1  oracle1  20979712 Jul 22 14:37 drsys01.dbf
-rwxrwxrwx    1 oracle1  oracle1  144842752 Jul 22 14:37 example01.dbf
-rwxrwxrwx    1 oracle1  oracle1  26222592 Jul 22 14:37 indx01.dbf
-rwxrwxrwx    1 oracle1  oracle1  20979712 Jul 22 14:37 odm01.dbf
-rwxrwxrwx    1 oracle1  oracle1  104858112 Jul 22 14:37 redo01.log
-rwxrwxrwx    1 oracle1  oracle1  104858112 Jul 22 14:37 redo02.log
-rwxrwxrwx    1 oracle1  oracle1  3282051072 Jul 22 14:37 system01.dbf
-rwxrwxrwx    1 oracle1  oracle1  10493952 Jul 22 14:37 tools01.dbf
-rwxrwxrwx    1 oracle1  oracle1  382738432 Jul 22 14:37 undotbs01.dbf
-rwxrwxrwx    1 oracle1  oracle1  26222592 Jul 22 14:37 users01.dbf
-rwxrwxrwx    1 oracle1  oracle1  39985152 Jul 22 14:37 xdb01.dbf
-rwxrwxrwx    1 oracle1  oracle1  3659538432 Jul 20 16:37 temp01.dbf

AFTER EXECUTING SELECT QUERY

[[oracle1@home oradata]]$ df -k
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/sda3             33056324   2296428  29080704   8% /
/dev/sda1               202220     15084    176696   8% /boot
/dev/sdb5             11590492   9976268   1025444  91% /home
/dev/sda2              2016044     32828   1880804   2% /lmm
/dev/sdb3             11590524    952160  10049584   9% /misc
none                    514888         0    514888   0% /dev/shm
/dev/sdb1             10080488   1886812   7681608  20% /usr

[[oracle1@home oradata]]$ du -k

7775940 ./mobile1

[[oracle1@home oradata]]$ cd mobile1
[[oracle1@home mobile1]]$ ls -lt
total 7775936
-rwxrwxrwx    1 oracle1  oracle1   2023424 Jul 22 16:44 control01.ctl
-rwxrwxrwx    1 oracle1  oracle1   2023424 Jul 22 16:44 control02.ctl
-rwxrwxrwx    1 oracle1  oracle1   2023424 Jul 22 16:44 control03.ctl
-rwxrwxrwx    1 oracle1  oracle1  382738432 Jul 22 16:44 undotbs01.dbf
-rwxrwxrwx    1 oracle1  oracle1  104858112 Jul 22 16:44 redo03.log
-rwxrwxrwx    1 oracle1  oracle1  3282051072 Jul 22 16:43 system01.dbf
-rwxrwxrwx    1 oracle1  oracle1  3659538432 Jul 22 16:17 temp01.dbf
-rwxrwxrwx    1 oracle1  oracle1  20979712 Jul 22 14:37 cwmlite01.dbf
-rwxrwxrwx    1 oracle1  oracle1  20979712 Jul 22 14:37 drsys01.dbf
-rwxrwxrwx    1 oracle1  oracle1  144842752 Jul 22 14:37 example01.dbf
-rwxrwxrwx    1 oracle1  oracle1  26222592 Jul 22 14:37 indx01.dbf
-rwxrwxrwx    1 oracle1  oracle1  20979712 Jul 22 14:37 odm01.dbf
-rwxrwxrwx    1 oracle1  oracle1  104858112 Jul 22 14:37 redo01.log
-rwxrwxrwx    1 oracle1  oracle1  104858112 Jul 22 14:37 redo02.log
-rwxrwxrwx    1 oracle1  oracle1  10493952 Jul 22 14:37 tools01.dbf
-rwxrwxrwx    1 oracle1  oracle1  26222592 Jul 22 14:37 users01.dbf
-rwxrwxrwx    1 oracle1  oracle1  39985152 Jul 22 14:37 xdb01.dbf

 

Report message to a moderator

Re: after executing select queries file sys becomes full [message #73847 is a reply to message #73845] Fri, 23 July 2004 03:11 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
login to sqlplus as admin user and issue

sql> show parameter _dump_dest

check any of those destinations in your /home

Report message to a moderator

Re: after executing select queries file sys becomes full [message #73849 is a reply to message #73847] Fri, 23 July 2004 23:12 Go to previous message
Suchita
Messages: 13
Registered: May 2002
Junior Member
sql>show parameter_dump_dest

after giving this command one of its listed parameter is as follows

user_dump_dest -/home/oracle1/OraHome1/admin/mobile1/udump

udump contains trace files(.trc)

but i have not understood the reason for
why file sys becomes full after executing select query.

Report message to a moderator

Previous Topic: Oracle Installation
Next Topic: Terminating export using ctrl+c
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Dec 25 07:57:54 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.