How do I use SQLPLUS?

Start SQLPLUS:

admin@dbserver1:~$ /net/oracle/bin/sqlplus 'dbserver1/ted@DBSP'

To add a user to Oracle Database:

SQL> CREATE USER bob IDENTIFIED BY  DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp QUOTA 2000m ON users QUOTA 0m ON system;

User created.

SQL> GRANT connect,resource TO bob;

SQL> CONNECT bob;

If you want bob to have DBA privileges as well, then:

SQL> GRANT dba TO bob;

If you change your mind:

SQL> REVOKE dba FROM bob;

How do i get a list of all tables?

SQL> SELECT table_name FROM user_tables;

How can I see what privileges I have in the database?

select * from user_tab_privs;
select * from user_resource_limits;
select * from user_sys_privs;
select * from user_role_privs;

How can I see how big the database is?

SQL> select * from user_ts_quotas;

TABLESPACE_NAME                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS
------------------------------ ---------- ---------- ---------- ----------
SYSTEM                                  0          0          0          0
USERS                           372375552 2097152000      45456     256000

How can I see which files are storing the database?

SQL> SELECT name from v$datafile;

How can I do a full backup

$ exp system/manager file=expdat.dmp compress=Y full=Y

How can I terminate a user’s database session?

SELECT sid, username from v$session;
ALTER system kill session ;

How can I write a PL/SQL procedure?

SQL> var blah number;
SQL> begin
  2  :blah := 2;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> print blah
         2

SQL> 

How can I assign the results from a SQL query to a variable?

SELECT mycol INTO :x FROM mytable;

…or, do multiple at once:

SELECT first_name, last_name INTO :x,:y;

Other useful commands:

SELECT table_name FROM tabs;
SELECT object_name, object_type FROM obj;
SELECT table_name, table_type FROM cat;
SELECT table_name, table_type FROM all_catalog;
SELECT table_name FROM all_catalog WHERE table_type='VIEW';
Share: These icons link to social bookmarking sites where readers can share and discover new web pages.
  • Twitter
  • Facebook
  • Google Bookmarks
  • LinkedIn
  • Reddit
  • StumbleUpon

Leave a Reply

Your email address will not be published. Required fields are marked *