Question on Oracle Public DB link inside Procedures

The theory behind using stored procedures for increased security is that you don't give DML access directly to users. The idea is that users never get anything other than execute permissions on the procedures. There's also a slightly less paranoid version of this approach where all writes are by stored procs, but table/view reads can be done by users.

The reasoning goes that if users only have execute permissions on stored procs and no direct DML on tables or views, then they can't manipulate data in any unpredicable ways.

To take an example, let's say you've got a bank account system and the only way to make a funds transfer is through a stored proc, you can have this proc audit log details about when it happened, where the money went and so forth. If you allowed direct DML access to the base tables then a compromised account could be used to take money without leaving an audit trail.

You are right that a compromised account is still a big security hole - big enough to drive a truck through. If all the compromised account's access is restricted to stored proc execution however then at least the hole will only fit a smaller truck.

impdp will create the user if it's not present yet, so you don't have to worry about it unless that's not what you want.

Do not run impdb or expdp as sysdba, only do that if Oracle support requests it in specific circumstances. Use an ordinary user for that - one that has been granted the dba role for instance. (There are the [IMPORT|EXPORT]_FULL_DATABASE privileges specifically for this type of thing, you'll need to grant access to the Oracle directory object(s) too.)

A full schema export (metadata and contents) would indeed look like:

expdp user/pass schemas=<schemaname> directory=dumpdir \
      dumpfile=<schemaname>.dmp \
      logfile=expdp_<schemaname>.log

If you want to import to a different user/schema (the target database can be the same as the source), you can use:

impdp user/pass schemas=schema1 directory=dumpdir \
      remap_schema=schema1:schema2 \
      dumpfile=schema1.dmp \
      logfile=impdp_schema2.log

If you don't want a complete import, you can set some filters both on data and metadata. See Filtering During Import Operations.

The Utilities Guide has all the details, I strongly recommend reading at least the overview part.