It refers to "spooning," i.e. snuggling/sleeping lovers lay on their sides with her back against his belly and his arms around her. The name "spooning" comes from the fact that the couple are like spoons nestled in a drawer. It's one of the most sublime pleasures that God has bestowed upon our poor wretched species.
I've never heard the participant in this activity described as "big spoon" and "little spoon," although I can understand the labels.
So the man being the "little spoon" means that the couple lies with the man's back against the woman's belly and her arms around him.
In his case, I think it means he is "presenting" his posterior to her, ready to receive whatever she decides to insert. "When he is feeling down or vulnerable, he needs an emotional and physical shield." He needs a protector, and a mommy... and so his solution, when confronted with physical and emotional difficulties, is to be bent into a fetal position with his protector behind him, holding him in place. It isn't difficult to see where his actual preferences lie.
Better than oral?
So the man being the "little spoon" means that the couple lies with the man's back against the woman's belly and her arms around him.
I must be a modern man as that's always how we sleep although it could be because I go to sleep first while she still does her assigned chores.
So The Modern (pussyfied metro-sexual) Man Lets the female cover him, protect him, and keep him safe at night.
Unless he's a really, really, Modern Man and his "wife" is actually another Modern Man.