Assume I've connected an inductor to a DC or AC source. The voltage across the inductor due to the source current would be L di/dt. But we know that the inductor induces/creates a voltage to oppose the source voltage. My question is, what is the value of this opposing voltage. Surely it can't be L di/dt because then the opposing voltage and source voltage would be equal thus making current to be zero, right?
The current is zero but only instantaneously. Inductors give graphs of voltage that upward slope as DC voltage surges and then gradually reaches its set level (which it technically never reaches but gets continually closer). The graph starts at 0V and then ascends. It doesn't immediately leap up to max voltage like a square wave
If you connect an ideal inductor across an ideal DC source, the current is initially zero, and rises linearly with time. The voltage, of course, is the applied DC voltage, so the current rises as di/dt = V/L amperes/second.
With an inductor that has some DC resistance, the current does not increase indefinitely but exponentially approaches the applied voltage divided by the resistance with time constant \$\tau = L/R \$. If it's a superconducting coil that has zero DC resistance, it increases linearly until it hits the critical current and then resistance appears.
If sinusoidal AC is applied, the steady-state current is the voltage divided by the reactance, which is \$X_L = \omega L\$.