Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

oracle administration > SQL*Plus > Flashcards

SQL*Plus Flashcards

1
Q

What is SQL*Plus?

A

An interface that allows users to communicate with the database.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What can you do within SQL*Plus?

A
  • Run SQL queries and PL/SQL code
  • Issues DBA commands
  • Startup/Shutdown databases
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What MUST you do BEFORE starting a SQL*Plus session?

A

Set the Environment Variables:

  • ORACLE_SID=orcl
  • ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1
  • LD_LIBRARY_PATH

Export the Environment Variables:

  • $ export ORACLE_SID
  • $ export ORACLE_HOME
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What are the ways you can start a SQL*Plus session from the Command Line?

A
  1. Connect w/ the SYSDBA Account:
    • $ sqlplus jbwilli1/password AS SYSDBA
  2. OS Authenticated User Account:
    • $ sqlplus /
  3. Connect through OS Authentication using SYSDBA
    • $ sqlplus / AS SYSDBA
  4. Connect to a non-default databases (i.e. A database not specified by ORACLE_SID):
    • $ sqlplus jbwilli1/password@d0dv
    • Note: The database name you’re trying to connect to MUST be listed in the tnsnames.ora file
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What is the CONNECT command & What does it do?

A

The CONNECT command allows you to connect to the database as a different user OR connect to a different database from within SQL*Plus.

  1. Connect as a different user:
    • SQL> CONNECT username/password
  2. Connect to a different database: (Only databases listed in tnsnames.ora)
    • SQL> CONNECT jbwilli1/password@d0dv1
  3. Connect as SYSDBA:
    • SQL> CONNECT / AS SYSDBA
  4. Connect to a different database as SYSDBA:
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What is a SQL*Plus session with /NOLOG?

A

A SQL*Plus session with /NOLOG is a SQL*Plus session that is not connected to any database; which is used mostly for writing or editing scripts using SQL*Plus or PL/SQL commands.

  • Syntax: $ sqlplus /NOLOG
  • Note: If you decide you want to connect to a database you issue the CONNECT command to do so.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What is the product_user_profile table?

A

A table that limits access to SQL*Plus and PL/SQL commands. When a user logs into a SQL*Plus session, SQL*Plus checks this table to see what restrictions are supposed to be applied to the user.

Note: After creating a database, you should execute the pupbld.sql script, which is used to build the product_user_profile table. The script is located in $ORACLE_HOME/sql/admin

Note: By default, there are no rows in the table. The SYSDBA must insert rows manually if some users need to be restricted. You can restrict a user from executing the following commands:

  • ALTER
  • BEGIN
  • CONNECT
  • DECLARE
  • EXEC
  • EXECUTE
  • GRANT
  • HOST
  • INSERT
  • SELECT
  • UPDATE
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What is the RESTRICT command?

A

The RESTRICT command prevents users from using certain operating system (OS) commands. The RESTRICT command can be used at three levels:

Level 1: Restricts commands EDIT, HOST

Level 2: Restricts Level 1 + SAVE, SPOOL, STORE

Level 3: Restricts Levels 1 and 2 + GET, START

Syntax: $ sqplus RESTRICT -3

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

What is the SET command?

A

The SET command allows you to set the environment variables for your SQL*Plus sessions

Ex: SQL> SET PAGES 2000

Note: To get a complete list of all the variables that can be configured with the SET command type “help set” at the command line.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

What is the SET SERVEROUTPUT command?

A

SET SERVEROUTPUT determines whether the output of a PL/SQL code or stored procedure is displayed on the screen.

Ex: SQL> SET SERVEROUTPUT ON

Note: You can format the output of text displayed on screen by using the FORMAT clause with the SET SERVEROUTPUT command. The FORMAT clause can take the values WRAPPED, WORD_WRAPPED, or TRUNCATED.

  • WRAPPED = The output is wrapped within the length specified by the LINES command and new lines are started when output doesn’t fit the specification.
  • WORD_WRAPPED = Wraps each lines to the length specified by the value of LINES and if a word won’t fit at the end of a line, the line ends before the word.
  • TRUNCATED = Each line of output is cut off exactly at the length of the LINES value.

Ex: SQL> SET SERVEROUTPUT ON FORMAT WORD_WRAPPED

SQL> SET LINES 200

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

How can I customize my environment variables for every SQL*Plus login?

A

You specify your preferences in the login.sql file which is usually located in your home directory, BUT, you may have to create and configure it manually.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

What is the STORE command?

A

The STORE command allows you to save the values of your environment variables in a script so you may use them at a later date.

Ex: SQL> STORE SET my_sqlplus.sql (This will store your current environment variables in the sqlplus.sql file)

Ex: SQL> @my_sqlplus.sql (OR add the my_sqlplus script to the login.sql file so it will configure your environment every time you log into SQL*Plus)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What is the SHOW command?

A

The SHOW command displays specified variable values

Ex: SQL> SHOW variable_name

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What is the SHOW ALL command?

A

The SHOW ALL command displays all the variables in your current environment

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What is the SHOW RECYCLEBIN command?

A

Displays all the tables in the Recycle Bin that can be recovered using the FLASHBACK TO BEFORE DROP command.

Ex: SQL> SHOW RECYCLEBIN

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What is the SHOW USER command?

A

Displays what user you’re currently logged in as

Ex: SQL> SHO USER

17
Q

What is the SHOW SGA command?

A

Displays the current allocations of the SGA memory

Ex: SQL> SHO SGA

18
Q

What is the SHOW PARAMETERS command?

A

Displays the current default and non-default values of the initialization parameters.

Ex: SQL> SHO PARAMETERS

Ex: SQL> SHO PARAMETERS MEMORY (Displays memory-related parameters)

Ex: SQL> SHO SPPARAMETER (Displays parameters in the SPFILE)

19
Q

How do you set your SQL*Plus prompt to show the database name?

A

SQL> SET SQLPROMPT _USER’@’_CONNECT_IDENTIFIER>

Note: To avoid making costly mistakes you should always set your environment so that the database name AND the username shows up on your prompt. Add this line to your login.sql file.

20
Q

What does ! do in SQL*Plus?

A

Runs operating system commands from within SQL*Plus

Ex: SQL> ! cp /h/jbwilli1/manifest.csv /tmp

Note: The ! allows you to copy manifest.csv to the /tmp directory

21
Q

What is the SPOOL command?

A

Allows you to save the output of a SQL statement(s) to an OS file

Ex: SQL> SPOOL manifest.csv

SQL> SELECT * FROM oracle.manifest;

SQL> SPOOL OFF;

22
Q

What is the ACCEPT & PROMPT commands?

A

PROMPT sends a user-specified message from SQL*Plus to the screen to prompt user input or display comments.

Ex: SQL> PROMPT ‘Please enter your last name’

ACCEPT reads user input from the screen and saves it in a user-defined variable.

Ex: SQL> ACCEPT lastname CHAR FORMAT a20 williams

Note: The ACCEPT and PROMPT commands are usually used in SQL and shell scripts to allow users to interact with the script to retrieve specific info.

23
Q

What is the EXECUTE command?

A

EXECUTE starts a PL/SQL procedure(s) in a package.

Ex: SQL> EXECUTE add_data

24
Q

What is the PAUSE command?

A

PAUSE prevents output from a long SQL statement to zip by on the screen. It allows you to view the output one page at a time by pressing ‘ENTER’. BUT, you must turn PAUSE on.

Ex: SQL> SET PAUSE ON

Note: You can include this in your login.sql file to further tune SQL*Plus to your preference.

25
Q

What is the SAVE command?

A

SAVE allows you to save the contents of the SQL memory buffer (i.e. the statements and commands that you’ve been runnnig during your SQL*Plus session) in a .sql file to be edited/executed later.

Ex: SQL> SELECT username, process, status FROM v$session

.

SQL> SAVE status.sql

Note: The ( . ) is very important because it tells SQL*Plus that you’re done writing this block of SQL. Also, if you already have a file named status.sql on the OS, you must use a REPLACE clause with SAVE. If you already have a status.sql file on the OS and you want to attach more SQL to it, you must use an APPEND clause with SAVE.

Ex: SQL> SAVE REPLACE status.sql

Ex: SQL> SAVE APPEND status.sql

26
Q

What is the ORACLE_PATH environment variable used for?

A

Tells SQL*Plus where to look for a script(s) on the OS. This allows you to put all your scripts in one location so that you won’t have to specify the path at the SQL prompt if you want to execute a script.

Ex: $ ORACLE_PATH=/h/jbwilli1/scripts

$ export ORACLE_PATH

Note: Now at the SQL prompt you can simply execute the script with no specified path ( SQL> @test.sql )

27
Q

What is a Windows Batch Script and how do you create it?

A

A Windows Batch Script is a script that contains a script to be scheduled to run at a specified time using the Windows at (@) utility.

Ex: You have a script ( test.sql ) that you want to schedule to run. You must first create a batch script ( testbatch.bat ) which contains the script and the commands to log into SQL*Plus and execute it:

Ex: sqlplus username/password@connect_identifier @C:\temp\test.sql notepad.exe C:\temp\output.txt

Note: This batch script logs into SQL*Plus, runs test.sql, and outputs the results into the output.txt file using notepad.exe.

28
Q

What is the DEFINE and UNDEFINE commands?

A

The DEFINE command allows you to create your own variables that last the duration of your SQL*Plus session or until you specify the UNDEFINE command and discard the variable.

Ex: SQL> DEFINE scripts=/h/jbwilli1/scripts

Ex: SQL> UNDEFINE scripts

Note: In SQL*Plus you’ll often use the DEFINE variable to substitute values for variables by adding the ( & ) to the user variable.

Ex: SQL> DEFINE owner = ‘&1’

29
Q

What are the pre-defined SQL*Plus variables that can be used with the DEFINE command?

A
  • _DATE = Contains the current date
  • _CONNECT_IDENTIFIER = Contains the name of the database you’re connected to.
  • _USER = Contains the username of the user
  • _PRIVILEGE = Contains the privilege level of the current user (i.e. SYSDBA)
30
Q

What does LIST command do in SQL*Plus?

A

Lists the previous SQL statement executed from the SQL buffer (i.e. the SQL buffer only holds the last SQL statement that was run).

Ex: SQL> l

SQL> SELECT * FROM manifest

Note: Typing a lowercase L is the same as using the LIST command

31
Q

What does the GET command do?

A

GET shows you what’s in your SQL script before you execute it in SQL*Plus by loading it from the OS into the SQL buffer.

Ex: SQL> GET test.sql

SELECT * FROM test;

32
Q

What does / do in SQL*Plus?

A

( / ) executes the last SQL statement that was ran

Note: It’s always a good idea to use the LIST command BEFORE executing your last statement using ( / ).

33
Q

How can you use GET to run multiple scripts concurrently?

A

You create a SQL script that contains all the other .sql scripts you want to run and you use GET with the script that contains all the others.

Ex: SQL> GET one_script.sql

@test.sql

@user.sql

@run.sql

SQL> @one_script.sql

Note: one_script.sql contained @test.sql, @user.sql, and @run.sql

34
Q

What does the C/OLD/NEW command do?

A

C/OLD/NEW allows you to make minor changes to your SQL code at the SQL*Plus command line.

Ex: SQL> SELECT user, status FROM user_profiles;

SQL> c/user/username

SQL> l

SQL> SELECT username, status FROM user_profiles;

SQL> /

Note: The example above shows that the first column (user) wasn’t correct. The c/user/username statement changes (c) user (/old) to username (/new). The ( l ) is the shortened version of LIST, which displays the last SQL statement WITH the correction and the ( / ) runs the statement.

35
Q

What does the INSERT command do?

A

INSERT ( i ) allows you to make changes to lines in SQL code from the SQL*Plus command line. Using ( i ) by itself allows you to add another line of code to the last statement.

Ex: SQL> SELECT username, status, process

FROM v$session

WHERE status = ‘ACTIVE’

SQL> i

SQL> and username = ‘HR’;

Note: This example allows the user to add the “ and username = ‘HR’; “ line to the SELECT statement showed above.

If you want to INSERT a line at a different part of the SQL code you must list the statement ( l ) and then add the new line using ( i ) at a specific line of code in the statement.

Ex: SQL> SELECT username, status, process

FROM v$session

WHERE status = ‘ACTIVE’

SQL> 1

SELECT username, status, process

SQL> i

2i ,login_time, terminal

3i .

SQL> l

SELECT username, status, process, login_time, terminal

FROM v$session

WHERE status = ‘ACTIVE’

Note: This example specifies the first line of SQL code by typing ( 1 ) and INSERTS a line of code behind it by typing ( i ) and specifies that this will be the 2nd portion of the line ( 2i ) and ends the SQL block with ( . ), Then lists the corrected SQL statement ( l ).

36
Q

How do you DELETE a line of code in SQL*Plus?

A

Display the SQL code using LIST ( l ) and then specfiy the line you want to delete.

Ex: SQL> del4

Note: This deletes the 4th line of code from the previous SQL statement

37
Q

What does the APPEND command do?

A

APPEND allows you to attach a word to a previously ran SQL statement.

Ex:

SQL> SELECT username, status

FROM dba_users

SQL> 1

SQL> APPEND , created_date

SQL> l

SELECT username, status, created_date

FROM dba_users;

38
Q

What does the COPY command do?

A

COPY allows you to copy tables and avoids the need to use the CREATE TABLE AS SELECT (CTAS) statement.

Ex: SQL> COPY FROM jbwilli1/password@d0dv1

CREATE test01 USING SELECT * FROM employees;

Note: This statement logs into d0dv1 as user jbwilli1 and creates table “test01” by selecting and copying the table data from “employees.”

oracle administration (2 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions