Basically it's as you say: small nations can't do without allies. Even if you don't (or can't) use them offensively, you need the protection against larger nations. For large nations, alliances are optional and often weigh you down. Still, there's a few things to consider:
- You can always refuse a CtA, if you feel the war would be too annoying or not worth it. So unless you need the dip slot, or you want to attack a former ally, there's not much reason to manually cancel an alliance.
- As a big nation it can still make sense to get alliances to secure a border, hinder a rival from expanding or simply allying someone isolated (=unlikely to start their own wars).
- There's actually nothing wrong with thinking of alliances only in the short term. Someone can (and is willing to) help you in your next war? Great! Ally him only for that war. You can stab them in the back as early as the peace negotiations. (i.e. you don't give them anything even though you promised land). Not all alliances need to hold a long time.
- Having an alliance also reduces AE. This alone is a huge reason, why big nations might want to find allies, since you can reduce the risk of getting coalitions against you.
Since you have asked about Ottos and Castille in particular:
For Castille it's actually a good idea to ally Portugal. You got a (often) hostile and (usually) stronger France right on your border, so some help against them can't hurt. Portugal is also super isolated and will rarely start any wars other than against the Berbers or colonial wars somewhere in Africa (but you usually don't get called into the latter).
Another good ally is Austria, since they can attack France from behind if it comes to a war. An alliance with Austria also stops France from advancing into the HRE and keeps them manageable until you decide to deal with them. Additionally Austria hates France (especially after Burgundian crisis has happened), so they will most likely help you in offensive wars against France.
The Ottomans are a different beast, since they start stronger than anyone around them. So they don't really need any allies, but it can still help for preventing AE or blocking rivals. For example you can ally Crimea (if you didn't get it from the event anyway), to keep it out of Russia's hands. Or you can ally Tunis to prevent them from allying the Mamluks. Even if Tunis is only of limited help to you (though it actually can help a lot, if Mamluks split their forces), at least they will stay neutral and not help Mamluks.
In the end it's all about benefit <-> cost. Even if you can win wars without any allies... Why would you refuse "free" help? If your allies start getting too costly (i.e. they call you into too many wars) that's the time you drop them.