Founder of Oracle
Write a procedure to create user account and alter the existing password.
we Use the CREATE USER statement to create and configure a database user, which is an account through which you can log in to the database, and to establish
the means by which Oracle Database permits access by the user.
Create user statement can be written as follows:
SQL> CREATE USER NEETA IDENTIFIED BY SAMMY;
This statement create a new user Neeta with Sammy as the password.
Altering a user:
We use the alter user statement to alter a user in the database. Using the statement we can do the following:
• Change a user password
• Assign tablespace quotas
• Set and alter default temporary tablespaces
• Assign a profile and default roles.
Here is an example showing how a DBA can use the Alter user command to change a user’ s password:
SQL> Show User
User is Neeta.
SQL> Alter user Neeta identified by sara;
SQL> Users can also change their own password with the password command in SQL * PLUS, as shown here:
Changing password for Neeta
Old password: *****
New password: *****
Retype new password : *****
List database objects that can be created in oracle. Explain any two of them.
The different database objects are tables, views, index, sequences, Database triggers, stored functions, stored procedures and clusters etc.
A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger
is mostly used for keeping the integrity of the information on the database. For example, when a new record (representing a new worker) is added to the
employees table, new records should be created also in the tables of the taxes, vacations, and salaries.
In database theory, a view consists of a stored query accessible as a virtual table in a relational database or a set of documents in a document-oriented
database composed of the result set of a query or map and reduce functions. Unlike ordinary tables (base tables) in a relational database, a view does not
form part of the physical schema: it is a dynamic, virtual table computed or collated from data in the database. Changing the data in a table alters the data
shown in subsequent invocations of the view. In some NoSQL databases views are the only way to query data.
Views can provide advantages over tables:
• Views can represent a subset of the data contained in a table
• Views can join and simplify multiple tables into a single virtual table
• Views can act as aggregated tables, where the database engine aggregates data (sum, average etc.) and presents the calculated results as part of the data
• Views can hide the complexity of data; for example a view could appear as Sales2000 or Sales2001, transparently partitioning the actual underlying table
• Views take very little space to store; the database contains only the definition of a view, not a copy of all the data it presents
• Depending on the SQL engine used, views can provide extra security
• Views can limit the degree of exposure of a table or tables to the outer world
Explain features of SQL. What are various sublanguages of SQL?
Features of SQL:-
SQL can be used by a range of users, including those with little or no programming experience.
• It is a non procedural language because more than one record can be accessed rather than one record at a time.
• It reduces the amount of time required for creating and maintaining systems.
• It is English like language.
• It reduces the amount of time required for creating and maintaining systems.
• It is the common language for all relational databases. In other words it is portable and it requires very few modifications so that it can work on other
Sublanguages of SQL are as follows:
Data Definition Language: It includes the command create, alter, drop which is used for objects like table, views, indexes etc. The DDL commands are autocommit
Data manipulation language: It includes commands which are used to query and manipulate existing objects like tables. The DML commands are Insert, delete,
Data Control Language:It is the component of SQL statement that control access to data and the database. Occasionally DCL commands are grouped with DML
statements. The commands are Commit, Rollback, Grant and Revoke
Data Query Language: It is the component of SQL statement that allows getting data from the database and imposing ordering upon it. It includes SELECT
statement. This command is the heart of SQL. It allows getting data out of the database perform operations with it. When a select is fired against a table
or the result is further compiled into a further temporary table, which is displayed or perhaps received by the program i. e. front – end. Select retrieve
data from table.
Explain the role of DBA.
DBA ‘ S Role can be divided into following three catagories:
2) System management
3) Databse design
1)DBA ‘s Security role:
a. Protecting the database: The DBA has several means to ensure the database security and based on the company’s security guidelines, he or she needs to
maintain the database security policy.
b. Monitoring the system: Once the database is actually in production, the DBA is expected to monitor the system to ensure uninterrupted service. Monitoring
the system include the following :
i. Monitoring space in the database to ensure it is sufficient for the system.
ii. Checking to ensure that batch jobs are finished as expected.
c. Managing the users: Every database has users and it is the DBA‘s job to create them based on requests from the appropriate people. A DBA is expected to
guide the users’ use of the database and ensure the database’s security by using proper authorization scheme, roles and privileges.
2)The DBA’s system Management Role:
a. Trouble shooting: One of the DBA’s main job responsibilities is trouble shooting the database to fix the problem.
b. Performance tuning: It is an ongoing task that constantly requires the attention of good Oracle DBA.
c. Loading Data: After the DBA has created the database objects, Schemas and users he or she needs to load the data usually from older legacy systems or
sometimes from a data warehouse.
3)The DBA’s Database design role:
a. Designing the database: Administrators who are particularly skilled in the logical design of database can be crucial members of a team that’s designing
and building brand-new databases.
b. Installing and upgrading software: The DBA is the person who installs the Oracle database severe software in most organization. Prior to actual installation
the DBA is responsible for listing all the memory and disk requirements so that the oracle software and databases as well as the system itself can perform
c. Creating databases: The DBA is responsible for creating databases. He creates a test database and later after satisfactory testing moves a database to the
production version. The DBA plans the logical design of the databases structures, such as table spaces and implements the design by creating the
structures after the database is created.
d. Creating database objects: The DBA needs to create the various objects of the database. Such as tables, indexes and so on. The DBA may also make
suggestions and modifications to the objects to improve their performance.
Write procedure to create user account in Oracle. How are privileges assigned to users?
To create user account refer ans of
Procedure for assigning privileges to user:
After creating user connected and grant the resource by the following command.
SQL> Grant connect, resources to username;
After this command the user can be connected by the following command:
SQL> Connect username identified by password;
A user can be grant privileges using the following command:
SQL> GRANT select,update,delete on tablename to user;
After this command a user can perform the SQL statements on the particular table.
Explain char and varchar data type and write difference between them.
CHAR should be used for storing fix length character strings. String values will be space/blank padded before stored on disk. If this type is used to store
varibale length strings, it will waste a lot of disk space.
Note: ASCII character 32 is a blank space.
Currently VARCHAR behaves exactly the same as VARCHAR2. However, this type should not be used as it is reserved for future usage.
VARCHAR2 is used to store variable length character strings. The string value's length will be stored on disk with the value itself.
Explain the datatype supported by oracle.
The CHAR data type is used for storing fixed length character strings with a maximum size of 2000 bytes. String values will be space-padded before being
stored on disk. When declaring a CHAR, the size must be specified, as shown in this example syntax:
SQL> columnname char( size)
This creates a CHAR column with a maximum data size of 10 characters.
Since data stored in a CHAR is space-padded, it is an inefficient way to store variable length strings. It is much more efficient to use the VARCHAR2 data
type when storing variable-length strings.
The VARCHAR2 data type is used to store variable length strings. The string value's length will be stored on disk along with the value itself.
How the data is represented internally depends on the database character set. The VARCHAR2 datatype takes a required parameter that specifies a maximum size
up to 32767 bytes.
Example VARCHAR2 Syntax:
VARCHAR2(maximum_size [CHAR | BYTE])
You cannot use a symbolic constant or variable to specify the maximum size; you must use an integer literal in the range 1 to 32767.
You can insert any VARCHAR2 value into a LONG column type because the maximum width of a LONG column is 2**31 bytes, but you cannot retrieve a value longer
than 32767 bytes from a LONG column into a VARCHAR2 variable.
The number data type is used to store numbes. Numbers of virtually any magnitude may be stored up to 32 digits of precision. Valid values are 0 and
positive and negative numbers. The precision (P) determines the number of places to the right of the decimal. If scale is omitted then the default is zero.
If precision is omitted, values are stored with their original up to the maximum of 38 digits.
This data type is used to represent date and time. The standard format is dd-mon-yy as in 21-jun-04. To enter dates other than the standard format uses
the appropriate functions. Data-time stores 24- hour format. By default the time in a date field is the first day of the current month.
This data type is used to store variable length character strings containing up to 2GB. Long data type can be used to store arrays of binary data in
ASCII format. Only one LONG value can be defined per table. LONG values cannot be used in sub queries, functions, expressions, where clause or indexes and
the normal character functions such as SUBSTR cannot be applied to LONG values.
RAW / LONG RAW:
This datatype is used to store binary data such as digitized picture or image . Data loaded into columns of these data types are stored
without any further conversions. Raw data type can have a maximum length of 255 bytes. LONG RAW data type can contain up to 2GB.
Explain the various characteristic that make Oracle as a RDBMS.
: The various characteristic that make Oracle as a RDBMS:
• It supports relational data structure.
• It has Data Manipulation Language at least as powerful as the relational algebra.
• Data is stored in a set of tables.
• Tables are joined by relational links.
• IT Reduces Duplication of data in database(Normalization).
• Allows greater flexibility and efficiency.
• In ORACLE Each table must have a unique references for each record called Primary key.
• Replicating these into other tables creates the Foreign Key.
• These foreign keys form the Relationship that link the tables together.
• In ORACLE each table consists of database table rows. Each database table row consists of one or more database table fields.
• In ORACLE data need only be updated once as it would only have been entered once.
• ORACLE eliminates the problems in using Flat file databases
What do you mean by database objects?
A database object is any defined object in a database that is used to store or reference data. Some examples of database objects include tables, views,
clusters, sequences, indexes, and synonyms. The table is this hour's focus because it is the primary and simplest form of data storage in a relational
The table is the primary storage object for data in a relational database. In its simplest form, a table consists of row(s) and column(s), both of which
hold the data. A table takes up physical space in a database and can be permanent or temporary.
Explain how oracle supports multiuser environment.
To take full advantage of a given computer system or network, Oracle allows processing to be split between the database server and the client application
programs. The computer running the database management system handles all of the database server responsibilities while the workstations running the database
application concentrate on the interpretation and display of data.
Oracle supports the largest of databases, which can contain terabytes of data. To make efficient use of expensive hardware devices, Oracle allows full control
of space usage Oracle supports large numbers of concurrent users executing a variety of database applications operating on the same data. It minimizes data
contention and guarantees data concurrency.
Oracle software allows different types of computers and operating systems to share information across networks.
Oracle maintains the preceding features with a high degree of overall system performance. Database users do not suffer from slow processing performance
At some sites, Oracle works 24 hours per day with no down time to limit database throughput. Normal system operations such as database backup and partial
computer system failures do not interrupt database use.
Oracle can selectively control the availability of data, at the database level and sub-database level. For example, an administrator can disallow use of a
specific application so that the application's data can be reloaded, without affecting other applications.
Oracle adheres to industry accepted standards for the data access language, operating systems, user interfaces, and network communication protocols. It is
an "open" system that protects a customer's investment
To protect against unauthorized database access and use, Oracle provides fail-safe security features to limit and monitor data access. These features make
it easy to manage even the most complex design for data access.
Oracle enforces data integrity, "business rules" that dictate the standards for acceptable data. This reduces the costs of coding and managing checks in many
Oracle software works under different operating systems. Applications developed for Oracle can be ported to any operating system with little or no modification.
Oracle software is compatible with industry standards, including most industry standard operating systems. Applications developed for Oracle can be used on
virtually any system with little or no modification.
Oracle also offers the heterogeneous option that allows users to access data on some non-Oracle databases transparently.
What is SQL? State the benefits of SQL.
SQL is a standard interactive and programming language for querying and modifying data and managing databases. Although SQL is both an ANSI and an ISO
standard, many database products support SQL with proprietary extensions to the standard language. The core of SQL is formed by a command language that allows
you to retrieve, insert, update, and delete data, and perform management and administrative functions. SQL also includes a call-level interface (SQL/CLI) for
accessing and managing data and databases remotely.
Explain following databse objects :-
A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects.
Creating or replacing a synonym
The syntax for creating a synonym is:
create [or replace] [public] synonym [schema .] synonym_name
for [schema .] object_name [@ dblink];
The or replace phrase allows you to recreate the synonym (if it already exists) without having to issue a DROP synonym command.
The public phrase means that the synonym is a public synonym and is accessible to all users. Remember though that the user must first have the appropriate
privileges to the object to use the synonym.
The schema phrase is the appropriate schema. If this phrase is omitted, Oracle assumes that you are referring to your own schema.
create public synonym suppliers
This first example demonstrates how to create a synonym called suppliers. Now, users of other schemas can reference the table called suppliers without having
to prefix the table name with the schema named app.
select * from suppliers;
If this synonym already existed and you wanted to redefine it, you could always use the or replace phrase as follows:
create or replace public synonym suppliers
Dropping a synonym
It is also possible to drop a synonym. The syntax for dropping a synonym is:
drop [public] synonym [schema .] synonym_name [force];
The public phrase allows you to drop a public synonym. If you have specified public, then you don't specify a schema.
The force phrase will force Oracle to drop the synonym even if it has dependencies. It is probably not a good idea to use the force phrase as it can cause
invalidation of Oracle objects.
drop public synonym suppliers
This drop statement would drop the synonym called suppliers that we defined earlier.
It is an optional; methods of storing table data . A cluster is a group of table s that share the same data blocks because they shrae common
and are often used together. Clusters are special configuration to use when two or more tables are stored in close physical proximity to improve performamce on SQL join statements using those tables.
Cluster is a method of storing tables that are intimately related and often joined together into the same area on disk. For example instead of emp table
being in one section of the disk and the dept table being somewhere else their rows could be interleaved together in a single area called a cluster.
it is the column or group of columns that the clustered table shaves in common.for example deptno in dept and emp table.
Sysntax: create cluster clustername(columnname datatype,………….)[other option];
Create clusteremp_dept(deptno_key number(2));
Advantages of cluster:
• Disk I/O time is reduced and access time improves for joins of clustered tables.
• In since all rows in clustered tables use the same columns as the common primary key, the columns are stored only once for all tables, yielding some
iii) Sequences: -
The marketing division and the user want to generate vencode automatically without any duplicates. To facilitate such operation oracle
provides a database objects called sequence, which can generate unique, sequential integer values. It can be used to automatically generate primary key
or unique values. A sequence can be either in ascending or descending order. The syntax for creating a sequence is as follows.
Create sequence increamnet by n] [start with n] [maxvalue n] [minvalue n] [cycle/nocycle] [cache/ nocache];
While creating a sequence we can define the following terms;
Increment by n: ‘n’ is an integer which specifies the interval between sequences numbers. The default is 1. If n is positive then the sequence ascends and if
it is negative the sequence descends.
Start with n: Specifies the first sequence numbers to be generated.
Specifies the minimum value of the sequence. By default it is 1 for an ascending sequence and 10e26 – 1 for a descending sequence.
Maxvalue n :
It specifies the max value that the sequence can generate. By default it is -1 and 10e26-1 for a descending and ascending sequences respectively.
specifies that the sequence continues to generate values from the beginning after reaching its max or min value.
SQL>create sequence venseq
Increament by 1
Start with 1
Minvalue 1 cycle
After creating sequcnce we can access its value with the help of pseudo column like currval and nextval.
Nextval returns initial value of the sequence when referred to for the first time . Later refernes to nextval will incream,ent the sequence using the
Increament by clause and return the new value.
Currval returns the current value of the sequence which is the value returned by the last reference to nextval.
An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns.
By default, Oracle creates B-tree indexes.
Create an Index
The syntax for creating a index is:
CREATE [UNIQUE] INDEX index_name
ON table_name (column1, column2, … column_n)
[ COMPUTE STATISTICS ];
UNIQUE indicates that the combination of values in the indexed columns must be unique.
COMPUTE STATISTICS tells Oracle to collect statistics during the creation of the index. The statistics are then used by the optimizer to choose a "plan of
execution" when SQL statements are executed.
CREATE INDEX supplier_idx
ON supplier (supplier_name);
In this example, we've created an index on the supplier table called supplier_idx. It consists of only one field - the supplier_name field.
We could also create an index with more than one field as in the example below:
CREATE INDEX supplier_idx
ON supplier (supplier_name, city);
We could also choose to collect statistics upon creation of the index as follows:
CREATE INDEX supplier_idx
ON supplier (supplier_name, city)
Define keywords and literals. Differentiate between:-
i)char and varchar2
ii)raw and longraw datatyps.
A literal is the same as a constant. There are three types of literals - text literals, integer literals, and number literals
Text literals are always surrounded by single quotes (').
Integer literals can be up to 38 digits. Integer literals can be either positive numbers or negative numbers. If you do not specify a sign, then a positive
number is assumed. Here are some examples of valid integer literals:
Number literals can be up to 38 digits. Number literals can be either positive or negative numbers. If you do not specify a sign, then a positive number is
assumed. Here are some examples of valid number literals:
The following words also have a special meaning to Oracle but are not reserved words and so can be redefined. However, some might eventually become reserved
How are the integer and real data represented in oracle? Explain with an example.
The NUMBER datatype is used to store fixed-point or floating-point numbers. Its magnitude range is 1E-130 .. 10E125. if the value of an expression falls
outside this range, we get a numeric overflow or underflow error. We can specify precision, which is the total number of digits, and scale, which is the
number of digits to the right of the decimal point.
The syntax follows:
To declare fixed-point numbers, for which we must specify scale, use the following form:
To declare floating-point numbers, for which we cannot specify precision or scale because the decimal point can "float" to any position, use the following
To declare integers, which have no decimal point, use this form:
NUMBER(precision) -- same as NUMBER(precision,0)
we cannot use constants or variables to specify precision and scale; you must use integer literals. The maximum precision of a NUMBER value is 38 decimal
digits. If we do not specify precision, it defaults to 38 or the maximum supported by your system, whichever is less.
Scale, which can range from -84 to 127, determines where rounding occurs. For instance, a scale of 2 rounds to the nearest hundredth (3.456 becomes 3.46).
A negative scale rounds to the left of the decimal point. For example, a scale of -3 rounds to the nearest thousand (3456 becomes 3000). A scale of 0 rounds
to the nearest whole number. If we do not specify scale, it defaults to 0.
List various types of user . Enlist the function of DBA.
Destination Index is a 16-bit register. DI is used for indexed, based indexed and register indirect addressing, as well as a destination data address in string manipulation instructions.
Segmentation and Segment Registers:
There are four different types of users in DBMS differentiated by the way they expect to interact with the system. Different types of user interfaces have been designed for the different types of users.
1) Naive Users: -
These are the unsophisticated user who interacts with the system by invoking one of the applications programs that have been written previously. For ex a bank teller who needs to transfer $50 from account A to Account B invokes a program called transfer. This program asks the teller for the amount of money to be transferred and the account to which the money is to be transferred. The typical user interface for naive user is a form interface where use can fill appropriate fields of the form. Naive user simply reads reports generated from the database.
2) Application Programmer:
These are the computer professional who write application programs. Application programmers can choose from many tools to develop user interfaces. Rapid application development tools are tools that enable an application programmer to construct forms and report without writing a program. These are also special types of programming languages that combine imperative control structures for ex for loop, while lop etc. These languages are called as fourth generation languages.
3) Sophisticated users:
These users interact with the system without writing programs. Instead they form their requests in a database query languages. They submit each query to a query processor whose function is to break down DML statement into instructions that the storage manager understands. Analysts who submit queries to explore data in the database fall in this category.
4) Specialized users:
These are sophisticated users who write specialized database applications that do not fit into the traditional data processing framework. Among these applications are computer aided designs systems, knowledge base and expert systems that store data with complex data types for ex. Graphics data and audio data.
Explain internal data type ‘date’ of Oracle. Differentiate between reserved words and key words in oracle.
Differentiate between reserved words and key words in oracle:
Both reserved word and keywords have special meaning in oracle. The difference between reserved word and keyword is that you cannot use reserved words as
identifier but it is not recommended.
Reserved words are :
All, Alter , And , Any, ASC, Begin Between ,By , Case, Check, Create, Connect, Declare, Default, Delete, Distinct, Drop, Else, End ,
xception, Exits, From, For, Fetch, Goto Etc.
Add, avg, Binary, Calling, Cascade, char, character, Date, Define ,Double, Element, elseif, empty, execute, Final , Fixed, Float, Hash, Heap,
Hidden, Length, Loop, Length, While, Varray Etc.