They're good for isolating your subject with a narrow depth of field as Dylansq has already pointed out. A 250mm lens at f/8 and focused to 10 feet away has a depth of field of about an inch and a half! So if you have a smallish subject that's only a couple feet away from its background you can use a long telephoto lens, fill the frame, and really blur that background.
This flower was pretty much at the minimum focus distance for my 55-250, zoomed all the way out to 250mm, and that blurred background was only a foot or two behind it; even this small flower's stalk (more like a blade of grass, really) is barely recognizable:

And I know you said you don't care about birds, but I think this illustrates the DoF well for subjects a little farther away. IIRC there are 3 trees here, not that far apart from each other, and the bird is in the middle one. The far tree is barely in focus and the gazebo/patio behind that is really only visible because of the man-made straight lines:

Also, long lenses make far things look closer. You can read signs and see details that you won't be able to see with the naked eye. Your long lens is like a good pair of binoculars with recording and enhancing (just keep increasing the exposure until you can read that dark sign: something you can't do with binoculars) capabilities. It's really nice when there's stuff you are curious about but can't get to, like abandoned structures that are off the trail at a national park, or anything interesting on the other side of a river or up a tall tree. Once, we used a long lens to identify a lump on the other side of a lake on a cloudy day as a beaver dam: zoomed all the way in with a superzoom (560mm in 35mm-equivalent, plus 4x digital zoom) and pumped the exposure way up (ISO all the way up) and of course the Image Stabilization kept the camera shake to a minimum so we could still get a clear (at least, non-motion-blurred) shot.
Here are two 100% crops from pictures taken of the same steeple at a country church, both with a 5.6x crop camera (Canon SX10IS). The inset was taken at 12mm ("like"* a 42mm lens on a 1.6x crop camera) from the church's parking lot: you can just barely see that there is something attached to the cross. The main picture was taken at 50mm ("like" 175mm) and from much farther away, across the highway. Now you can see more detail, see that it's coiled around the base, and even see some of the staples holding it in place and the insects flying around the cross!

And of course: pictures of the moon! The one subject where you really can't "zoom with your feet."
- Yes, "like" means Field of View, not IQ. But the comparison is still important: same camera, longer focal length gives you a lot more detail.