Oracle space management

List all tablespaces and the files that belong to them

COLUM BYTES/1024*1024 FORMAT 999,999,999,999
FROM dba_data_files

Archive Log Mode Status
/* log mode of databases */
SELECT name, log_mode FROM v$database;

Find overall size of the database

COLUMN sum(bytes)/1024/1024 FORMAT 999,999,999,999
select sum(bytes)/1024/1024 from dba_data_files;

Size of all TEMP files

select nvl(sum(bytes),0)/1024/1024 from dba_temp_files;

To shrink your current tablespaces to the HWM

select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)

column cmd format a75 word_wrapped
select 'alter database datafile '''file_name''' resize '

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents group by file_id ) b
where a.file_id = b.file_id(+) and
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0

Moving datafiles

  1. Shutdown database
  2. Take a full cold backup (optional)
  3. Copy the datafiles from the existing filesystem to the new one (you can always delete the file from original filesystem later)
  4. Startup mount instance
  5. For each datafile, ALTER DATABASE RENAME TO
  7. ALTER DATABASE BACKUP CONTROLFILE TO TRACE; -- since we changed the database structure and must preserve it.
  8. Delete the file(s) from the original filesystem.

Some datafiles can be removed while the database is running, but only if they:
  1. Are not part of the SYSTEM tablespace
  2. Do not contain temporary segments
  3. Do not contain rollback segments
To move the datafiles of a tablespace from one filesystem to another, without shutting down the database:
  2. Copy the required files to the new location
  3. For each datafile, ALTER TABLESPACE ABC ONLINE;
  5. Delete the file(s) from the original filesystem.


Solaris run levels

Solaris init states refer to the services provided by the system. The exact services and processes run at each init level are determined by the scripts in the /etc/rc#.d directories. The default service levels for each init state are:

  • 0: The system is at the security monitor (>) prompt. It is safe to shut down the system when it is at this init state.
  • 1, s or S: This state is known as "single-user" or "system administrator" mode. Root is the only user on the system, and only basic kernel functions are enabled. A limited number of filesystems (usually only root and /usr) are mounted. This init state is often used for sensitive functions (such as kernel libc patches) or while troubleshooting a problem that is keeping the system from booting into multiuser mode.
  • 2: Multiple users can log in. Most system services (except for NFS server and printer resource sharing) are enabled.
  • 3: Normal operating state. NFS and printer sharing is enabled, where appropriate.
  • 4: Usually undefined.
  • 5: Associated with the boot -a command. The system is taken to init 0 and an interactive boot is started.
  • 6: Reboot. This state takes the system to init state 0 and then to the default init state (usually 3, but can be redefined in the /etc/inittab file).

To get to a desired run level n, each of the rc (run control) scripts from 1 to n is run. To get to run level 0, the K scripts are run in each rc#.d directory between the current run level and 0 in reverse numerical order.


Ipswich and Adastral BT

Ipswich is in the heart of East Anglia and the heart of Europe. It is the regional centre for business, shopping, sport and entertainment. Ipswich is home to IP-City and is one pole of the Cambridge/Ipswich Hi-Tech Corridor. It is proud of its 800-year Royal Charter and its long maritime tradition and also possesses a magnificent heritage and fine parks.

Adastral Park is a large campus in the middle of nowhere! It’s about 10 miles outside Ipswich. Onsite, there are ATMs, a Post Office, restaurant, coffee bar and gym.

How to get there?

  • By car, take the A12 north-east from London / M25. Look out for a roundabout signposted ‘Adastral Park’ to the right.
  • By train, catch a train from London Liverpool Street to Ipswich, and then a cab from Ipswich station to Adastral Park.
You need a photo pass to get through the main security gate, and a proximity tag to get around the building.

On arrival, visit the Reception Block to get your photo pass, and call one of the Quallaby onsite team who will have to come and collect you.


It can get difficult to find a taxi once you are at BT premises. However, there are several cab companies that thrive on the traffic between Ipswich and Martlesham. Below are some numbers:

Ipswich cab company: (01473) 25 22 22 and 25 44 44
Avenue Taxis: (01473) 40 70 70


Week II in France

The Indian from England with quasi-American accent was back in Paris. Yay! Guess the French weren’t so happy as the grumpy official at passport control was brusque enough to wipe my helpful (slight nervous?) smile. The taxi driver could speak some English though. Another yay! Not that it was of much use – the splitting headache and his hands-free mobile made quick work of whatever appetite I had for a conversation.

The room was in the main hotel this time, which was much better than I expected. I think I actually got some sleep that night.


New trainer, new course material. And surprise! It’s a change for the good. Goes out to prove how much a difference depth of content, competence and a non-McDonald’s IQ can make! On the flipside, the long days leave no time for exploring Paris.

Met Michal at the training centre. He works for the Polish office of CH2M HILL, and is quite the opposite of the Polish guys at the training last week (more friendly). The one drink before dinner resulted in…. well, few beers now that I have lost count, and back at the bar in the hotel, the part-time boxer-bartender, served us the ghastliest cocktail ever. I think it supplied a year’s worth of sugar that one needs. Maybe he was punched in the wrong place.


Dreading a tough morning-after, it was pleasant yet difficult to wake up next morning. No headache, yay! There was just enough time to shower (though I still looked wrecked), but made it in time. No surprises during the day.
Went for lunch again to this cosy little family-run restaurant. While the publican had offered me this "Indian" pudding the day before (at the behest of their "Sri Lanki" cook I suspect), I was surprised she made the "Jus de Orange" for me. I found out later I was the first customer they made if for. Apparently everyone else either goes for the fizzy orange drink, or the omnipresent Coca-Cola. Ordered the wrong dish though, so had a tough time pushing those slices of ham down my throat. "Coup de Fruits" for the dessert was every bit as delicious as it looked. The warm homely setting of the pub made me long for home. Not that India has such pubs. Or restaurants. Most public places are solely in the male dominion, unless it’s a hip night-club. But I digress. The publican beamed broadly when I thanked her for the orange juice (I think she did understand what I said though she doesn’t understand English – one of those things that’s so human).

And the "Indian" pudding? Oh, I’m quite sure there is no such Indian pudding.


Pizza again. Same place. Another bad choice. Some very salty fish on the pizza, so I guess I could live without salt and sugar for a year now.

A week in France

A week in France

So finally the visa problems were sorted out and I was off to France. The flight was uneventful, however, upon arriving in France I found that my mobile wasn’t working. After some interesting conversation with the taxi driver at the airport (and abortive attempts to pronounce the name of the place I had to go), I somehow managed to reach Porte de Saint Cloud.

The hotel wasn’t all that bad, however, I was booked into the apartments which can be quite unattractive and uncomfortable, especially since they are not serviced if you stay more than 2 days. Also due to the failed 3 mobile, I had to resort to making calls from the hotel to home every morning and evening. There was on-demand Internet access available at the hotel, but of course, one had to pay for it.


The big day! As usual, there had been a lot of talk in the office about the dress code, so I went all decked up in a formal suit. That I was overdressed, is an understatement (the American trainer was in a crumpled shirt and a lopsided tie, supposedly imitating the latest in style back home). We had an interesting mix of people in the class, and that most attendees could barely speak English didn’t help.

The training started off well, making one believe the slow pace was designed to break it all in gently. Little did I know that it masked the lack depth, both in the course and trainer’s technical skills.

Lunch was interesting; though I was disappointed when we chose an Italian pizza place to eat. Nonetheless the pizza tasted better, and different than what it has ever tasted anywhere.
Had my first glimpse of the Eiffel Tower in the evening. The thrill of being able to see what had been force-fed since childhood (usual general knowledge question – "Where’s the Eiffel Tower?") was uncomparable. It does look beautiful, though in retrospection, it is but a phallic symbol. Not much different that Indian’s worshipping the Shiva-lingam
Only when I reached the tower did I find out that one could climb up the tower. Elevators for the tourist, as well as stairs for the more adventurous. Being lazy and subdued by the weather (-6 C), I took the elevator, and was totally unprepared for the stunning views of Paris. Took dozens of photographs, however, most are fuzzy since I couldn’t battle both the wind and cold at the same time.


If I was able to concentrate somewhat in the training the first day, the second was a nightmare. The course content dragged on, and I wandered off to more interesting things. Like doodling. The highlight of the day was the couple of beers in the hotel room in the evening. Sad, eh?


The ice was broken somewhat among the attendees, so John and I decided to go out and explore Paris a bit. Barely awake by 4:30 PM when the "training" finished, we took the metro. Destination: Arc De Triomphe, and the walk on Champs de Elysees to the Louvre.

If I thought Indian heritage had a penchant for opulence, France taught me that they have that, and the ability to preserve it. While Arc de Triomphe is a poor man’s Gateway of India, Champs de Elysees is stunning. While brightly-lit neon signs are ubiquitous in the high street of any big city, the sheer force of architecture and beauty is overwhelming. Not to mention the French passion for sales (called soldes). And there are always plenty of couples adding romance in the air.

By the time we crossed Champs de Elysees, we were almost frozen. The wind was brutal, and probing, But nothing that a few beers couldn’t cure. Louvre will have to wait for another day.


Another dead day. Tried to economise by buying a couple of cheeseburgers (I now have the most perfect set of side handles now), but of course, I had to get something to wash them down and something for a snack, so I probably spent more on this economical meal. I guess I learnt something that day, if only I could remember what?


What training? Everything there was, was completed the day before (I had by now these wonderful sketches – the cap of pen here, outline of a computer mouse there, plus an extra large number of graffiti across the training manual).

Anyway, it was the best day of the whole week. Christophe and I had to meet John at the Notre Dame Cathedral, and we took a detour to see Louvre first. Biggest courtyard I ever saw. Various extracts of the Da Vinci Code ran before my eyes, and I stopped for a while to wonder about the signifiance of the four glass pyramids. Ah well, I got some photographs at least. Though I couldn’t go in, so my date with Monalisa will happen some other time.

Notre Dame is imposing. The fine sculptures on the outside give way to this serenity and calmness inside. There were people on the pews lost in their thoughts, while some were visibly shaken and probably in pain. That of course didn’t stop the tourists from flouting the "No-pictures" law. Never saw any place of belief as violated as that.

The pall of gloom lifted quickly enough when Christophe took us across to these alleyways that had some Greek and "Irish" pubs. A pint of Guinness extra cold was made available, but whether it was cold was debatable. Two pints and then off to hotel, and then in the taxi to the hotel. There was a bit of panic on the motorway when the beer was kicking in and I could see the traffic slowing down. It must have all cleared up though, since when I woke up (after some heavy snoring as alleged by the taxi driver), we were almost at the airport. Snooty airline waitresses made it amply clear I was entering England.

Whatever european warmth was left, was taken away of course, by the taxi driver in England.
Ps: I had to go back to France again (I found out on Friday), but that then, is another blog!