The woodpeckers will peel the bark back in search of bugs. If the habitat is correct, some woodpeckers will nest in dead trees as well, so yes, it can be habitat for them.
I'd say it would depend on your environment. I am in the deep woods and when we had our timber select cut, we left the tops, trash and dead trees to rot down. Over time we found it provided cover for much wildlife and improved the soil as it rotted. On the other hand, if I didn't have a lot of acreage, I'd probably clean it up so I wouldn't have to look at it.
Rereading your original post makes me think I'd remove the dead timber from my property as you mentioned
the oaks and hickories beyond my property are dying off
so there is ample habitat for the wildlife near by.
I would opt for hugelkultur beds.
