Remote database from command line

SQL*Plus connection to remote Oracle database

SQL*Plus is a very handy command line tool when you want to quickly access an Oracle database or execute SQL statements from scripts, e.g. BASH scripts. However, getting it to connect to a remote Oracle database server in a script proved to be quite a challenge. This blog documents how to write the connection string in various cases.

Local database

Connecting to a local database is trivial, just use:

$ sqlplus dbUser/dbPassword@dbSid

Remote database from command line

Here's the nasty syntax for connecting to a remote database using its SID:

$ sqlplus dbUser/dbPassword@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=remoteServer)(PORT=1521)))(CONNECT_DATA=(SID=dbSid)))'

You can also connect using a SERVICE_NAME instead of SID.
Note that the single-quotes are needed to preserve the brackets.

Remote database from a BASH script

I needed to write a script that called SQL*Plus several times. In order to make it maintainable, the username, password and the part after the @ were specified as environment variables. Unfortunately, there did not seem to be any obvious way of setting the value of the environment variable so that the single-quotes were properly sent to the sqlplus command at run time. I had to explicitly escape each bracket with a backslash, like so:

DB_CONNECTION=\(DESCRIPTION=\(ADDRESS_LIST=\(ADDRESS=\(PROTOCOL=TCP\)\(HOST=remoteServer\)\(PORT=1521\)\)\)\(CONNECT_DATA=\(SID=dbSid\)\)\)

Because this script was part of a Java project, I subsequently moved this value to a Java properties file. This means every backslash had to be escaped with another backslash to prevent Java from interpreting it. The entry in the Java properties file:

The PuTTY pscp.exe version of the program enables Windows computers to transfer files to or from a remote

Run the PuTTY SSH (Secure Shell) program when you need to establish a secure, encrypted network connection to a remote computer storing files you require. The PuTTY pscp.exe version of the program enables Windows computers to transfer files to or from a remote computer rather than having to access the command line directly. Run the pscp.exe executable on the Windows command line to quickly copy files from a remote computer to the local computer hard drive.

Ads by Google

Download HIPAA Checklist

Free HIPPA Security Guide by ESET® 10 Steps For HIPAA Compliance

eset.com​/​HIPAA-Security-Checklist

Step 1

Click the "Start" button on the Windows computer and click the "Run" or "Search" box. Type "cmd" in the box and press the "Enter" key and the command line window will appear.

Step 2

Type "cd /path_to_pscp/" (without quotes) at the command prompt except replace "/path_to_pscp/" with the exact path to the "pscp.exe" file. Press the "Enter" key.

Related Reading: How to Open PDF Files on the iPad Using iBooks

Step 3

Enter "pscp.exe username@x.x.x.x:/file_path/filename c:\directory\filename" on the command line except replace "username" with the name of an account that has permissions to access the remote computer through SSH, replace "x.x.x.x" with the IP address or hostname of the remote SSH computer, replace "file_path" with the directory path to the file you wish to retrieve, replace "filename" with the actual name of the file you want to transfer, and replace "directory" with the path and name of the directory where you wish to save the file on the local computer.

Examplepscp.exe gbi@167.15.70.130/home/oracle/ucr_db.txt c:\ucr.db.txtStep 4

Press the "Enter" key. Type the password for the account authorized to connect to the remote SSH computer if prompted and press "Enter" again. The file will transfer and save to the local computer.

Ads by Google

CIST-1200- Database Management:THE RELATIONAL MODEL 2: INDEXES

CIST-1200- Database Management

Chapter 4: THE RELATIONAL MODEL 2: INDEXES
Lecture 4-B

DUE: SEP 30

NAME:------------------------------------------------DATE:-------

1. What are views?
Views are application program’s or individual user’s picture of the database
Less involved than full database
Simplification
Security

Example:
CREATE VIEW Housewares AS
SELECT PartNum, Description, OnHand, Price
FROM Part
WHERE Class='HW';


2. How indexes improve database performance?

Indexes improve performance by using Record numbers automatically assigned and used by DBMS to Increase data retrieval efficiency


Example:
CREATE INDEX CustomerName ON Customer (CustomerName);


3. Explain the security features of a DBMS
Prevention of unauthorized access to database
GRANT SELECT ON Customer TO Jones;

Example to cancel
REVOKE SELECT ON Customer FROM Jones;


 4. What is entity?
Name of place, things, persons to track in database called a table

5. What is  referential integrity?

if table-A contains a foreign key that matches the primary key of table-B, the values of this foreign key must match the value of the
primary key for some row in table B or be null

Example to create non existence foreign Key

FOREIGN KEY (RepNum) REFERENCES Rep


6. What is  legal-values integrity?

A set of values allowable in a field

Example:
CHECK (CreditLimit IN (5000, 7500, 10000, 15000))

Lecture 6 - DBryant

Since I submitted this through Angel, I cannot go back and retrieve what I typed.  Here is the assignment I copied from your post:

1.DBA creates users and administers User Security.

Example:

CREATE USER james
  IDENTIFIED BY mia0101
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON users
  PROFILE default
  PASSWORD EXPIRE
  ACCOUNT UNLOCK;

2.DBA grants and revoke privileges.

Example:
GRANT SELECT,INSERT,UPDATE,DELETE ON customers TO sales_manager;
GRANT SELECT ON customers TO public;
GRANT SELECT ON sales.customers TO sales_admin WITH GRANT OPTION;


3.DBA creates and manages roles.

Example:
CREATE ROLE appl_dba;
SET ROLE appl_secure IDENTIFIED BY seekwrit;

4.DBA assigns resource limits to a profile, use the CREATE PROFILE or ALTER PROFILE statement.

Example:
CREATE PROFILE "TEST1" LIMIT
CPU_PER_SESSION DEFAULT
CPU_PER_CALL DEFAULT
CONNECT_TIME DEFAULT
IDLE_TIME 10
SESSIONS_PER_USER DEFAULT
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL 250000
PRIVATE_SGA 25000
COMPOSITE_LIMIT DEFAULT;

5.DBA is responsible for implementing Password Security

For example:
CREATE PROFILE "TEST2" LIMIT
PASSWORD_LIFE_TIME 60  PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_MAX 2   PASSWORD_REUSE_TIME 4
PASSWORD_LOCK_TIME DEFAULT FAILED_LOGIN_ATTEMPTS 5
        PASSWORD_VERIFY_FUNCTION DEFAULT;


6.DBA enables auditing of specific SQL statements with an AUDIT statement.

Example:
AUDIT table;
AUDIT table BY juanita;
AUDIT table BY juanita WHENEVER NOT SUCCESSFUL;


7.DBA disable auditing of a specific SQL statement, use a NOAUDIT statement.

Example:
NOAUDIT session;
NOAUDIT not exists;
NOAUDIT table BY juanita;
AUDIT INSERT TABLE BY juanita BY ACCESS;

8.DBA purges audit records older than 90 days, execute the following as user SYS:

Example:
DELETE FROM sys.aud$ WHERE timestamp# < SYSDATE -90;

9.DBA creates a new Fine-grained auditing (FGA) policy, use the packaged procedure DBMS_FGA.ADD_POLICY.

Example:
DBMS_FGA.ADD_POLICY(object_schema=>’HR’
   ,object_name=>’EMPLOYEES’
   ,policy_name=>’COMPENSATION_AUD’
   ,audit_column=>’SALARY, COMMISSION_PCT’
   ,enable=>FALSE
   ,statement_types=>’SELECT’);

10.DBA enables Unified Auditing, shut down the database and listener, and then relink the oracle executable using the following options:

Example:
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME

Dr. A,

This is a test to see if I can communicate with you.

EXAM VOUCHERS 25%; 50% Practice test

 certification exam vouchers

Oracle Academy Institutional members can request certification exam vouchers by following these instructions:

1. Sign in to the Oracle Academy website
2. Click the "Oracle Certification Exams" link in the left menu.
3. Follow the instructions provided.


Member ID #83013
Atlanta Technical College
If this customer needs assistance, please have him contact us at academy_ww@oracle.com.

Lecture6: TEN ways DBA administers User Secuity

1.DBA creates users and administers User Security.

Example:

CREATE USER james
  IDENTIFIED BY mia0101
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON users
  PROFILE default
  PASSWORD EXPIRE
  ACCOUNT UNLOCK;

2.DBA grants and revoke privileges.

Example:
GRANT SELECT,INSERT,UPDATE,DELETE ON customers TO sales_manager;
GRANT SELECT ON customers TO public;
GRANT SELECT ON sales.customers TO sales_admin WITH GRANT OPTION;


3.DBA creates and manages roles.

Example:
CREATE ROLE appl_dba;
SET ROLE appl_secure IDENTIFIED BY seekwrit;

4.DBA assigns resource limits to a profile, use the CREATE PROFILE or ALTER PROFILE statement.

Example:
CREATE PROFILE "TEST1" LIMIT
CPU_PER_SESSION DEFAULT
CPU_PER_CALL DEFAULT
CONNECT_TIME DEFAULT
IDLE_TIME 10
SESSIONS_PER_USER DEFAULT
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL 250000
PRIVATE_SGA 25000
COMPOSITE_LIMIT DEFAULT;

5.DBA is responsible for implementing Password Security

For example:
CREATE PROFILE "TEST2" LIMIT
PASSWORD_LIFE_TIME 60  PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_MAX 2   PASSWORD_REUSE_TIME 4
PASSWORD_LOCK_TIME DEFAULT FAILED_LOGIN_ATTEMPTS 5
        PASSWORD_VERIFY_FUNCTION DEFAULT;


6.DBA enables auditing of specific SQL statements with an AUDIT statement.

Example:
AUDIT table;
AUDIT table BY juanita;
AUDIT table BY juanita WHENEVER NOT SUCCESSFUL;


7.DBA disable auditing of a specific SQL statement, use a NOAUDIT statement.

Example:
NOAUDIT session;
NOAUDIT not exists;
NOAUDIT table BY juanita;
AUDIT INSERT TABLE BY juanita BY ACCESS;

8.DBA purges audit records older than 90 days, execute the following as user SYS:

Example:
DELETE FROM sys.aud$ WHERE timestamp# < SYSDATE -90;

9.DBA creates a new Fine-grained auditing (FGA) policy, use the packaged procedure DBMS_FGA.ADD_POLICY.

Example:
DBMS_FGA.ADD_POLICY(object_schema=>’HR’
   ,object_name=>’EMPLOYEES’
   ,policy_name=>’COMPENSATION_AUD’
   ,audit_column=>’SALARY, COMMISSION_PCT’
   ,enable=>FALSE
   ,statement_types=>’SELECT’);

10.DBA enables Unified Auditing, shut down the database and listener, and then relink the oracle executable using the following options:

Example:
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME