Matanong lang, does AMA Computer college offer Oracle 11g learning modules?
For AMA BSIT grads, do they include Oracle modules in their subjects?
@prodigy, the best I know is sa Oracle university mismo.. but if you want there is another good solution mas mura :) just let me know then I'll tell you how
hi guys question po,
we are planning to do housekeeping of tables to free-up some space on logical drive. any tips/suggestions? btw, db is mssql 2008. TIA
im interested. pm sent sir.
yes we have an Oracle Database Management System using 11g from AMA Computer College
up ko lang yung tanong ko. sana po meron makapagbigay ng recommendation or nagawa na nila before. im not a dba pero handling apps. tia
make a backup of the whole database. then delete the relevant data from the tables you want to housekeep. in mssql, you can shrink back the data files when the space is freed up after deleting the data. store the database backup as archive in a safe place. if you ever need to go back to this data you can create a temporary database and restore the backup file. some other specifics will depend on your company and requirements
also make sure that your database recovery model is 'full', and that transaction log backups are scheduled. this will prevent the transaction log from growing uncontrolled and eating up lots of space
@wtsd: salamat sa info. I believe we do have backup on db and tlog kasi nakikita ko siya sa job history na may nag-rrun and I believe our dba is doing logshipping. hindi ko lang alam kung pano setup ng backup nila, kung backup to drive or tape. ang naisip ko kasi na plano ay:
move data rows to a .bak table (same db padin) ie 1 mo old
delete .bak data older than x months
ok ba ito?
btw re: shrink datafiles, meron kasi ako nabasa na as much as possible daw wag mag-sshrink? kaya isinantabi ko yung shrink pero kung wala naman epekto then might as well consider shrink datafiles.
-- edited by taz12 on Sep 08 2014, 03:54 PM
ang assumption ko kasi was meron kayo disk space issues, and kelangan nyo ma reclaim yung space, kaya suggest ko yung shrink database. kung yung sa plano mo na i move lang yung 'old' data to another table, di mo talaga ma reclaim yung disk space since andun pa rin naman yung data, nilipat mo lang. of course, in terms of performance, pwede bumilis yung queries na gumagamit nung table na yun, since magiging mas konti rows na yung iaaccess ng mga queries. yung sinasabi mo 'defrag', sino gagawa nun? depende sa laki ng table, reorg/rebuild and update stats pwede tumagal ng ilang oras, and preferable tatakbo ito during non production hours. i guess alam din naman ng DBA nyo yun..
@wtsd, salamat sa info.
actually disk space issues kaya plano namin mag-housekeeping and non-production hours namin siya gagawin.
re: sa defrag kami na din gagawa nun after ma-delete yung old data. kailangan din ba mag index rebuild/reorg after ma-delete yung old data?
ok, one at a time, medyo magulo. kung yung old data i move mo to another table within the same database, hindi liliit yung database mo, so you will still have the same disk space issues.
on the performance side, you need to run reorg/rebuild at the least on the source table, para ma 'defrag': ma compact yung mga data pages, and yung indexes ma refresh
One surefire way is to archive your old data from your database either by using Transact SQL or Import/Export Wizard sa MSSQL nyo. Make sure din na may definite na data retention kayo na naka set. Let's say data older than 5 years, i-archive na.
Masakit sa ulo pa if your data is located in multiple tables (Relational Database, ugh!). Syempre coordinate with Devs kung paano nyo mate-trace yan. Usually sa views or stored proc. meron nyan.
Pagkatapos nyan, do a data/transaction log shrinking para ma-recover nyo yung unused "page" sa database files and transaction logs sa db nyo.
-- edited by cocoy0717 on Sep 09 2014, 10:08 AM
-- edited by cocoy0717 on Sep 09 2014, 10:09 AM
mejo lalo gumulo isip ko. pero salamat sa inputs niyo cocoy at wtsd. hehe!
@wtsd: yup, immove muna to another table yung data (ie 1 month old). tapos pagka-move iddelete naman yung data ni .bak (ie 3 years old) para ma-freeup yung space.
@cocoy: teka hindi ko naisip yang relational database na yan. pano po ba setup niyan? yung pagsshrink ba ay ang last step? meaning after ma-delete yung old data -> rebuild -> shrink
any other info kung ano pa dapat iconsider? tia
relational database, means naka structure yung database mo in a way na yung data mo is broken down into different tables that is related to each other.
example, you might want to store customer data in several tables that will store personal info on table A, contact info on Table B, list of transactions in Table C. Those 3 tables are "related" to each other para i-store mo yung customer data.
Yung rationale dyan is may customer ka na meron multiple contact info, or meron syang different transactions on different locations, etc. parang ganun. Storing them on a single table will give you data redundancy and data integrity problems.
hindi ka pwede mag purge/delete ng data sa Table A alone. Tatamaan yung data mo sa tables B and C. That's why you need to coordinate with your Developers kung paano mare-retrieve yung data na yan IF it is spread out to several tables.
-- edited by cocoy0717 on Sep 09 2014, 10:33 AM
oks noted. thanks. :D