That is not true. Type piracy means that a package adds a method to a function it doesn’t own for types it doesn’t own. If the package owns the function or any of the types of the arguments, that’s not type piracy.
DataFrames owns DataFrame, so defining Plots.plot (which it doesn’t own) is not type piracy. As you point out, multiple dispatch wouldn’t make much sense if you couldn’t define new methods for Base (or other package) functions for new types.
With extension modules, there is no rule for which package should define extensions, i.e., whether a method for Plots.plot for DataFrame types should be an extension in the Plots or in the DataFrame package. This is something package maintainers have to agree on. There might be a heuristic that smaller packages should add extension modules for bigger / more general (“base”) packages, not the other way around. But that’s not a hard rule.
LinearAlgebra probably does have instances of type piracy w.r.t Base, so some parts of LinearAlgebra will have to be moved to Base to fix that.