Introduction:
The oracle database server is an object-relational database management system. This write-up deals with practical aspects of compatibility of practical functioning of oracle database servers within ecosystems of all operating systems. Oracle database management primarily meant for system administrators that are technically proficient in different functioning and workings of the Oracle database. An oracle database is equally good for application developers of relational databases. Oracle database primarily depends upon the relational database and the operating system environment under which the oracle is accessible. For starters, many manuals of oracle database documentation are filled with different variants of simple schemas which are installed during purchase of software packages. From Oracle database, one can reach to sample schemas and from these in-depth discussion on how to install these schemas and how to test and experiment on oracle database can be easily obtained.
What is a relational database?
Every organization is an information system. It is a formal system of storing and processing information. A human resource organization must maintain and collect human resources records of an organization. It should be immediately available to those who are needed. Every organization has its own data and privacy and it has to be collected and centralized in order to process information at a speedy rate. Few years back most organization have ledgers and manual data ware housing in order to preserve long stretch of records of an organization. Sometimes, work-overload impends organization to remove last ten years data in the cycle so that everything will remain intact.
The concept of a paperless office in which the organization wants to store data in virtual servers and wants to use relational database management system in order to completely automate information systems reaches to reality with the Oracle relational database management system. Database is a principled and organized collection of information. Within database environment, each information is treated as a single unit. Database application is a additional tools which run from units of database to classify different format of data demanded by the user. The main aim of the database is to store, collect, and retrieve all related informations for use by database applications. Database is a vast set of informations, unprocessed and complicated but stored to make use of different database applications.
Database management system (DBMS):
Database management system (DBMS) is software to control basic and advance management of the database. It stores, organizes and retrieves data. DBMS has some elements with those it works within specified database inventories. Kernel code manages memory and storages of DBMS. DBMS is full of metadata. Webmasters are very well acquainted with the concept of metadata for website which they have to manually put into the header.php files. Search engines consider every website as a data and first crawl its metadata to know in which category these websites would ever fit in. Meta data are also known as data dictionary. With every metadata, there could be large number of similar data associated with it. Third element of DBMS is query language which enables database applications to access the database. Primarily with these three elements namely kernel code, meta data or the data dictionary and query languages which enable different database applications to access data from DBMS.
Database application is a software program which can interact with a database and can modify and recover data from it. DBMS is an old concept and various new versions and innovations have improved it considerably but still to date the basic structures and mechanisms in which DBMS works remains exactly the same as before. Throughout all these years, only relational database structures have seen complete prudential growth. First generation database management system has a hierarchical database which organizes databases in parent-child relationships. It is one-to-many relationships. Each parent record has one or more child record. In order to extract data, application of database management system used to search for related parents records in order to access child records. These classes of database work within short organization. With due course of time, with the advent of industrialization and commercialization, more and more multi-national entities began to operate within international spheres transcending all national boundaries.
Network database:
With these cometh the challenges face by an organization with the speed of flow of data and how to manage and retrieve these data at super fast speed at the entrance of a query are some of the really challenged faced by these organizations. In these circumstances, one-to-many database structure based on hierarchical database proves to be really difficult as many a times there is an astronomical increase of parent’s records that creates puzzles and difficulties to organize and find child records. Then comes network data base which is similar to the former one but there is some additional improvement to this model with the introduction of many-to-many records rather that one-to-many relationships.
Both these database management systems are rigid and relationships are predetermined without a single logical definition. There is no data definition language exists and for this it is almost incomprehensible universalize entire database management system. That is why various applications of DBMS have failed to produce a simple query to retrieve data from DBMS. Then cometh the most illustrious relational database model.
Relational database:
In 1970 in a seminar paper, E.F.Codd defined relational database model which was based on mathematical set theory. Even after so many years, relational database model derived from set theory of mathematics is still hugely popular. Relational database works in relational models which have some major elements. It works in structures which consist of formally ruled objects which store or access data in a database. Every object has clearly defined set of actions which are known as operations. These actions enable and empower applications to modify and manipulate data and structures (objects) of a database. There should be no system exceptions. Every data and structures (objects) is guided by a set of rules for different operations. In this way, every data and objects when operated goes in a set of rules which different queries from DBMS applications command to retrieve and manage the database.
Relational database works in minimal relation building. Every datum has a tuple. Tuple represents an unordered set of values. Values are recognized as attribute values. A relation is a set of tuple. Relation creates a rule from an unordered set of attribute values. Relational database is created from these relations of a set of tuples. Each row of the table has the same set of columns. Relational database stores and set data in relation (table). It is a set of tuples having the same rows and columns (attributes). Same employee information can be stored at a salary and department table. Similar information in first instance stores in a tuple in an unorganized attributes values. That data contains the same number of rows and columns and relation or a table is consisted from these set of unorganized data and then many tables or relation can be created within the database. So tables are two-dimensional representation of relation in the form of rows (tuples) and columns (attributes). Tables find common rows and column and then redistribute in different relations or tables across various relational databases.
Relational database management system (RDBMS):
Relational database management system comes out from concept of relational database. RDMS is a complete playground for applications to store, retrieve and manage data. Various database applications play with data to produce meaningful decision matrix to administrators. All these prove to be immense help for them at all these guides to clear cut decision making assemblages for human resources of the organization. RDBMS has two types of distinct functionalities. One is built on logical operations and the other is based on physical operations of RDBMS. In logical operations of RDBMS, an application requests what content is needed for client or employee. In this operation, request comes out from client to server. For example from a client side application can request to add the name of the employee to record it in a table.
These are called logical operations in which client side adds a new request to server side. Second distinguishable functionality of RDBMS is physical operations. In these operations, RDBMS decides how data should be processed and accessed by distant clients and build and specify a certain flexible rules of action upon which all accessible applications obey in order to access RDBMS. General use of RDBMS is for indexing purposes where applications try to find out a specific bit of information. For example, a specific query of a table which requires accessing indexes, search rows and columns and reading data in memory can be permissible from RDBMS. In these bodily operations, database may find an index to search for specific rows and columns in order to provide detailed information and data sharing to applications. In this way, database applications get transparent information system management from RDBMS.
Object oriented features of oracle database:
Oracle database works upon the principles of relational database management system (RDBMS) model which works in principles of object-oriented approach of the database management system. Object oriented approach defines a set of rules and gives access freedoms to users through a vast array of object-oriented features such as user-defined types (logical operations), inheritance, and polymorphic which makes oracle database as object-relational database management system (ORDBMS). In a way, Oracle database makes easy for complex relational models of big industries to go approach for object-relational database models to create user-friendly and easily accessible databases for big industrial houses.
Evolution of Oracle database:
For more over last 30 years, Oracle database has been evolving and a mere database models to transit into relational database models and then to object-relational database models to be widely accepted in vast industries worldwide. In 1979 version-2 of the Oracle database was released to the market as first commercially viable database management software solutions. It is generally accepted due to its relational database models which provide easier access than parent-child database management system. Then, in 1983, Oracle database was submitted in C language thus it creates much required probabilities for computing systems. Owing to C language this software package can work in different computing environments. Oracle database release in 2003 introduced a new feature called grid computing which helps to make low-cost virtual server for small organizations.
It introduces oracle automatic storage management (ASM) which clears complicated manual adjustment to pave the way to create simplified virtual database storage management for the whole organization. With these new introductions, with a little bit of knowledge and organization can now build their own visualized computing server without any additional technological help assignments. Oracle 11g released in 2007 introduces many administrative functions which empower system administrators to innovate and adapt quickly to diverse information systems and provide access to more challenging situations in ever changing world of businesses.
Schema objects:
Major impression which RDBMS is providing to users and applications is sheer distinctiveness in determining well-defined principles of server and users. RDBMS provides greater independence of cooperativeness among physical and logical operations of the database management system. Thus it provides an access to sufficient rules and programs within these two operations to create a viable relational database management models. Schema objects are generally known as a database schema, which is a collection of logical data structures. Database schema or schema objects are owned by the user and it is same as username. For example consider one aspect such as G mail as a single object-relational database model in which every user has a username and password.
But username can be known to many and it is the sole identity of the user from its user name. Users have independence to add mail and send and receive mail and achieve mail. All these are logical operations which add to RDBMS and thus these are examples of schema objects or database objects. So, from above example it can be referred as those schema objects are user- generated data which directly relates to data of database. Examples of the most favored and used schema objects which database allows are tables and indexes. In a way, though these objects are created remotely through logical operations but still these schema are database objects. All database objects are not schema such as profiles and roles. Profile and roles come and restrict from physical operations of RDBMS.
What is a table describes in RDBMS?
A table is a set of rows. Row recognizes instance of an entity. Column provides attribute of an entity. Row identifies real employees. Column identifies employee identification and signature id of a specific employee. Attributes of entity can be optionally allocates to definite rules or constraints. In simpler terms, columns can be specified with a fixed set of rules which are known as integrity constraints. With the same characteristics of rows and columns sets a table. Instances of rows cannot be changed, but attributes of columns can be changed to force a column to contain a certain value in every row.
Suppose, need arises to specify a single characteristic of the column in corresponding rows. Then, each column in a common set of rows can be assigned a single value or attribute through implementation of null integrity constraint. It forces the column to go for a single attribute in every row. This means every instance of the row gets single corresponding attribute with null integrity constraints.
Indexes:
The index is a schema object and an optional data structure which you create on one or more columns of the table. Table is generally created from conventional attribute of columns with similar instances of entity in rows. Index creates one or more common integrates and it can increase the performances of data retrieval. The index is a continuing process. Applications and users can use indexes of columns to query about instances of a specific entity (row). One more benefit of using indexes is that they are free from logical and physical operations of the database management system. When you create indexes all applications continue to function similarly when you drop an index all applications continue to function through columns and rows.
Data access languages: Declarative and procedural
No database management software (DBMS) can work independently from industry standards. There is a precise set of rules for every DBMS to work within certain specified industry regulations. Structured query language (SQL) provides a graphical user interface to relational database management system (RDBMS). Oracle is a case in RDBMS. SQL is a declarative language whereas C is a procedural language. Procedural languages build its logic based on how things to be done and what steps was taken for successful execution of coding. On the other hand, declarative languages such as SQL give idea to users about what should be made. There is a declarative process for this and thought process is well known and even relational database models adopt such algorithms automatically.
SQL statement:
Declarative languages do not submit portability like procedural languages. Users’ impacts a query such as name of the employee about the instances of an entity and from these procedures comes about detailed information about an employee. All operations of oracle database are made with ANSI standard languages of SQL. SQL performs better in relational database models. A SQL statement is a simple computer program. It is a query or programming instruction. The SQL statement is a string of SQL text. SQL statements can perform certain tasks within the oracle database. It generates query data about specific instances of entities, of attributes through indexes and so on.
It can perform logical operations through creation, modification, alternation and dropping of objects. Objects are cases of entities. It can create constraints upon attributes of tables and its objects. It creates a specific set of rules through null integrity constraints for database consistency as well as can make integrity constraints upon columns to restrict different rules and regulations upon which database works. Oracle SQL is distinct from normal SQL. Oracle SQL supports numerous features and enhancements. It can unify diverse undefined past preceding queries into one consistent language. It removes confusion from the mind of users.
PL/SQL:
As discussed earlier in SQL is a declarative language which advises what should be done. PL/SQL is a continuation of Oracle which adds additional procedural variable to declarative language such as SQL. It gives humongous possibilities for the user to create a versatile database interpretation language which gives way to a hybrid language option by combining positive attributes of C and SQL. It empowers you to manage queries and tasks as well as work out specific problems attached to server related tasks such as scripting of additional server side programming with portability options derived from languages such as C to be deployed elsewhere within perimeters of organization. In modern times, most of these industries are going global, thus making stupendous advancement through server-side scripting of application which works equally good with inbuilt application on RDBMS.
In modern times, most of customer centrist-organizations go for different mode of customer satisfactions by providing different portability options such as application for an android. Windows, Apple and so on. All these instruments accumulate data and send to logical operations of RDBMS and then attach those information to tables. PL/SQL stores applications in logical database. Its procedures are a schema object. All these schema objects are specified to carry out a specific set of tasks. PL/SQL is a server-side programming languages with built-in functions that provide room for portability deployment anywhere. Tables and indexes are SQL statements inside logical operations of a database which is used to perform a set of related tasks.
Oracle database is a multitude database:
An oracle database can store Java and Java methods. These can be published to SQL for users to interact with relevant data. Existing PL/SQL programs can be asked from Java and vice-versa. Java is an internet based logical database management script through virtual users which attaches to PL/SQL based logical operations to interact, send and modify database inside RDBMS. This goes on to show oracle database is designed to be used for multimedia interactive database. A useful database must ensure that every user must reach to its own logical schema objects without interfering and corrupting other’s data. Then, the question arises what is the role of an oracle database?
The role of oracle database is the ability to consolidate similar SQL statements so that they go through well defined regulations. A group of SQL queries can either be committed or rolled back. Committed queries are logical queries which are applied to the physical database. Rolled back are query of SQL statements which is rejected by the oracle database. Oracle database examines such transaction and classify them as committed or rolled back. Thus, transactions contain one or more SQL statements from logical operations of RDBMS. Transaction is a set of parallel rules known as units and oracle database regards this while taking vital decisions on transaction management for committed and rolled back value of oracle database.
Here, is a simple illustration to know how a transaction performs. A customer or instances of an entity wants to transfer funds from a savings account to another account. This financial transfer has following consideration as per oracle database operations. Decrease amount from savings account, and then increase account of another account and then record transaction in the transaction journal. For oracle database all three operations are not separate, they are single units and it further guarantees that all these operations committee in a single unit or rolled back in a single unit. In transaction, all three operations succeed or fail at a single mode of operation.
Data concurrency:
Data concurrency is the simultaneous access by multiple users to a single data. Relational database management system (RDBMS) currently works on the principle of data concurrency. There should be proper control over data otherwise many users could change data integrity many times to create entire chaos within RDBMS. There should be proper concurrency control in order to safeguard adequate data integrity. When multiple users access same data the traditional method to control data concurrency is to make users to wait here for some time. The popularity of DBMS lies upon reducing waiting time of users.
DBMS concept is based on to reduce waiting time of users to negligible moments. So, all SQL queries which are valid must proceed with integrity. RDBMS must find out destructive interactions aims to push database to its limit and must undo updated data from destructive interactions. Oracle employs a lock mechanism to control its database from destructive excess. Lock accesses shared resources and prevents destructive interaction in distinct transactions. Lock allows maximum concurrent access to data and prevents destructive interactions by moving to shared resources.
Data consistency:
Users always want to see during and after transactions a consistent use of data in an oracle database. Most of times users deal with perceptible visible transactions made by its account through separate processes. Oracle database enforces statement-level read consistency which works in accordance with a single query committed by the user in a single point of time. This point of time is when the statement of the query is opened or a transaction begins.
Read consistency of database works on the principle of transactions isolation level which imparts internal flashback query to determine explicit time of transactions from within relational database management system. On a reverse point of view, a database can see every data going through each transactions must undergo state of transaction-level read consistency which provides read consistency to each statement delivered through logical operations of the query. It makes consistency in creating data from the same point of time which is the time from which the transaction begins.
Oracle database architecture:
Server accumulates a large amount of data, in a multitude logical environment. It provides data consistency to users concurrently to assess and update data. The database server is vital for information management of the organization. A database stops unwanted access of unwanted users into the database and also provides sufficient mechanisms for stricter database recovery in case of server failure. A server accumulates a large amount of data in a multimedia environment and provides concurrent access to each user the same data at the same time through promulgation of transaction level read consistency.
What consists of Oracle Database server?
An Oracle database server consists of a database and at least one database instance. Generally, database instance is named as instance. Both database and instance are so closely connected with each other that many a times it becomes almost difficult to distinguish between what is database and what is instanced in an oracle database instance server. A database is a collection of files in a disc and these files are independent of database instances. These sets of files store data in database. Database instances are set of memory structures which manage database files. It consists of the system global area (SGA) and a cluster of background processes. SGA and background processes are a subset of the shared memory area of the system. An instance can exist independently of database files.
Each user through an application connects to instance in order to operate into database processes. Application which users use in order to connect to instance is run by a client process. Each client process has its server process. Server processes have its own private session memory known as program global area (PGA). Then, PGA goes into database bugger cache in order to retrieve data immediately and provide lesser server loading times. A database can be considered and managed in the perspective of the physical as well as logical operation perspectives.
Physical and logical storage structures:
A SQL statement can create list and cluster of logical database. An operating system cannot arrange the entire database structure through any such queries. By renaming physical database storage files do not impact database files that are stored inside logical database tables. Oracle is relational database storage and the important function of a relational database is data storage. Physical storage structures are database files that store data. Through SQL client application when you execute the SQL command CREATE DATABASE, data files, online redo log files and control files are created.
Every oracle database has physical data files which contain physical database data. Actually, the contents of logical database structures such as index and tables are all stored inside physical data files. Every Oracle database has a control file. It contents metadata, database name, location of database files. Meta data physical structure of database files is stored. Online redo log files contain all information about all changes made to data. It is made up of redo log entries of log files. There are many other important files are there in the oracle database server such as parameter files, diagnostic files, back up files and archived redo log files. Back up, files and archived redo log files are offline files and they are important for backup and recovery of oracle database server.
Logical storage structure:
Logical storage structures of Oracle Database server consist of data blocks, extents, segments and table spaces. It empowers oracle database structure to have exclusive control over disc space use. Oracle database data are stored in data blocks data on disc of physical structure of oracle database. Every datum has space and mostly relates with the number of bytes and that is significantly called at the finest level of granularity. Specific number of logically continuous data blocks is termed as extent. Extent stays in a single location and all data blocks stay within extent have specific type of information.
User’s interaction with the Oracle database server comes from user’s object such as tables and indexes. Database is divided into logical storage units called as tablespaces. Table-space contains segment and within segment stays specific number of extents and every extent has one or more specific data blocks.
Database memory structures:
The oracle database has a memory structure that uses existing memory structures and processes to streamline data processing and editing. The oracle relational database management system exists at the main memory of a computer and oracle database uses that memory structures and processes to manage existing database structures. Users start application at front end to log into oracle database structure through pre-existing queries. By activating such query, applications connect to oracle database through database instances. Instances to distribute memory areas and structures to apply to access physical operation from logical operation of oracle database. All these memories are allocated to applications with database instances in system global areas (SGA). In SGA all processes and background processes starts from null thus begins accessing of operating system from front-end managed application of consumer servers.
Oracle database instances initiate different forms of processes in order to allocate and distribute specific forms of memory processes and structures for different applications. Oracle database server thread is equivalent to processes. Process has a number of steps. Normally, RDBMS structure like oracle principally depends upon different forms of threads in order to start processes. Many a times functions of threads is similar to processes. Database instances are composed of three principal types of processes. Client processes stay in a different environment at different faraway places on different operating system. Client processes are made to run specific software code on an application program. An application program is an oracle tool to run the application to react to physical storage disc of oracle database server.
Instance memory structures:
Client processes create multiple database application processes which are then filtered by client background process to keep adequate data integrity. Background processes runs with asynchronous scripts to perform input and output functions and constantly monitor for increased parallelism within discrete processes to make room for increased application stability and functional reliability. Background processes work like the moderator to create database stability and speed of access. Server processes communicate with customer processes and recognize the need and then interact with oracle database to fulfill query requests.
System global area (SGA) and program global area (PGA) are two principal memory structures to perform different functions within an Oracle database. SGA is a collection of shared memory structures that contain data and control information for one database instance. PGA controls information regarding background process. It is linked to each server processes and background processes. The oracle server is fully optimized to capitulate maximum performances from server by splitting itself to two server namely database servers and client programs. The computer which has physical operations handles the database server and the computer which run applications handle client server for logical operations.
Application architecture:
Application architecture is an operating system environment in which a database application connects to a server application. There are two types of application architecture: client/server and multiplier. In client/server architecture, a request initiates from a client application to perform a function on the database server. Oracle database initiates action upon this request and creates instance for shared memory access. Client server then receives the request and processes adequate information to customer.
In multiplier application architecture, one or more client server has the facility to provide cached data resources to the client application without accessing the database server. It is added levels of security where the application server has to query processing thus reduce the load on the server. It is like content delivery network (CDN) of self-hosted word press site where pressure on a back end server is quite less than it is anticipated. Generally, database server has physical access means it stays within a physical computer hard disc and in order to access it CPU, RAM and other threaded processes need to be stay upright and in consonant with the requirement of the database server.
If there is simultaneous large number of query attached with physical server then slowly the load on it can render it dysfunctional. Similarly, by accessing all the time to the physical server can become a security threat to data to large organization. For this, there is the need for cached server processes to be stationed at different areas of the organization so that direct access to the physical server from different client server applications will not be possible all the time. It is high time to think more about it in creating different cached server such as content delivery networks used in website such as word press to be replicated in different oracle database server management. With this, there creates an interface between client and multiple databases which provides one additional level of security.
Database and Word press:
Take a look at most of self-hosted word press sites which are mostly attacked by spam messages due to easily accessible client application such as a comment system. Spammers use that to import more and more spam to take back hosting space and make website slow and slowly website down due to exceeding server resources. It is immoral for webmasters who have intended to make their website a revenue generating option. To WordPress website, there are many free content delivery network which cache content at different server location spread all over the world and presents the web page to different localities from there to different cached servers.
It is part of the brilliant ways to make website faster and also secure it from spammers. Word press is a database driven network and it also users’ front end as logical operation and back-end hosting as physical operations and for this it is essential to go for a smoother drive by employing different form of optimization techniques to protect physical database from lurking eyes. Similar functioning of an oracle database and it is important to make cached processes within logical operations in order to magnify and modify route to physical database structures.
In WordPress, there are some beautiful caching add on which create the html form of the database and cached it in the theme folder of the physical database. When any user access these data, it does not get a php converted webpage from database server but it gets an html page straight from theme files which reduce load on the database. In this way, instances of a query to the database are reduced considerably and load on physical database becomes almost zero. Parallel process can be employed with an oracle database and there is an option on logical database to make it more transparent. This process not only makes a security layer for your database but also it provides speedier access to data for client access.
Service-oriented architecture (SOA):
Service-oriented architecture (SOA) is a multiplier architecture in which background functionality is hidden inside different background services. Most of SOA can be accessible through a web browser either through wireless LAN or through unique web addresses. SOA relates with HTTP and XML based standards which mainly function within specified application environments. An example of part of SQL as the application can be seen with wordpress where entire content management system can be turned into a dynamic website where every possible PHP access can be converted into HTML type web access protocol.
Similarly, through SOA, most of the database can have accessibility for authenticated client users through a web browser who is stationed at different locations of the world and by opening a web browser to access these data. It creates an alternative database rule and stores these in caching processes and there are many third party add-ons such different content management systems and cached plug-in to present database to trust users at one go. An oracle database can act as a web service provider by implementing multiplier SOA environment.
Oracle net services:
Modern oracle database has facility for Oracle net-services, which act as an interface between the database and network. Oracle net services to establish a network session that established logical connection between client application and database server. Oracle net becomes data for courier for all client application thus creates additional opportunities for client application to access data without worrying about and waiting for connection to databases. Oracle net services run with the principle of oracle net listener which separates database query from network related action queries. It acts as mediums and when the client application sends a request, it receives it and sends to the database server and then receives information from there and sends again to client server.
In this way, estimated confusion among database queries and instances and network calls segregated completely. Oracle net services are divided into 2 classes of server architecture: Dedicated server architecture and shared server architecture. Example of such net services for oracle database application is self-hosted word press site, where one can have different options for dedicated server hosting or shared server hosting environment. In dedicated server architecture, each client processes or front end of word press connect to a dedicated server processes which have a single computer as a physical operation and repository of word press database.
Distinct structures of Oracle database:
Every extra session is assigned to a dedicated server process. In a dedicated server hosting environment, data access becomes faster and data integrity and stability of the database server become relevant with each process acquiring same dedicated hosting environment. Alternatively, in a shared server architecture, database have to access different shared processes and beginning of each processes have to be a different shared processes as in one physical server location database different processes are installed and this creates a situation for users as there is no such guarantee of how much resources is available for client user at any point of time.
Shared server architecture runs in the principle of dispatcher which is a process that empowers clients to connect to the same database. It runs on the principle of multiple to single connectivity and from time to time it depends upon the user demands where there can be every possibility of high rise of demand and thus it can make the entire operation not sustainable at some points of time. So far we have discussed extensively about logical relations and definitions of distinct structures of oracle database.
Oracle relational data structures:
Relational data structure of oracle deals with data integrity rules and basic structures that stores metadata. Database schema objects are logical container of data structures. Examples of schema objects are tables and indexes. Schema objects are originated and modified within SQL queries. A database user has a username and password and privilege access to numerous databases. Database user logs into database through logical operation of applications. Each user gets a schema object. It contains tables and indexes. Each schema objects have its unique name. There are very different types of schema objects.
The single most common type of schema object in a relational database is tables. Tables store data in rows. Each row of the table is called table cluster. Index is example of schema objects that have an entry in each indexed row of the table. Due to index, during SQL query database users have direct access to the entry of row. Index speeds up database. It does not depend upon physical and logical operation of the database. The modification and running of indexes do not provide deeper impact on basic and continuous operations of the database.
Indexes and sequences:
An index-organized table contains all data that are saved in the index manner. It puts an entry with each row and thus accesses those rows immediately without any such delays. Partitions are an example of schema objects that contain large tables and indexes. Each partition has its own name and optionally they have its own properties are due to more development in the arena of hardware more and newer partition mechanisms are on the way and thus create different profiles for each partitions.
Views are for visual graphical representation of data and it shows data structures of one or more tables in a structural approach and it does not actually store data. One example of view in an operating environment is folders in partitions. It provides systematic information about data-logic users. Sequences are further example of schema objects. They are developed by authenticated users. They are meant for multiplier with primary key values.
Administrator account:
Some schema objects store data in segments. Some schema objects store data in metadata structures only. Schema objects store data logically within table spaces. There is not any significant relationship between table space and schema objects. Any table space can contain any kind of schema objects. There is not any such restriction on this. Administrative works of system administrators relate with starting and stopping of database, managing shared and background memory structures and creating and modifying databases.
The administrative account (SYS) is automatically created once a database is made. It makes SYS schema which stores data dictionary and base tables. Base tables are essential from the point of view of running oracle database. These tables can be created and modified by administrator only; otherwise, entire Oracle database will reach to the point of system diagnostic. There are many default schema are there in an oracle database. They are the set of samples of schema which can enable oracle documentation and oracle instructions.
Table:
Table is under a significant place in the oracle database. A table signifies an entity. An entity signifies something significant which needs to be recorded. An example of an entity could be used. It can create meaningful schema such as job history, employees, countries, departments, locations. This shows that workers can be a table in an oracle database. Mostly, tables are 2 types: relational tables and object tables. Relational tables have simple columns. Most of these columns rise out of the common type of columns. Object tables contain top-level attributes of the object type. Here, objects can be placed in an employee.
These relational tables in the database can be conducted in a particular characteristic ways. Heap organized table does not store rows in a particular order. It is formed through the CREATE TABLE statement. Index organized tables’ orders rows through allotment of primary keys. It enhances disk performances as it puts a simple index to all rows to extract data easily. External table stays outside database, whose metadata is stored inside database but data are stored outside database.
From these classifications, it is concluded that tables can be permanent or temporary in nature. Tables work in sessions. Permanent tables stay in all sessions in the name of table definition and data. Temporary tables stay only during ongoing sessions where the transaction happens and after fully transactions temporary tables stops to act from there. Mostly, during multiple operations results is stored in temporary tables and when the transactions end tables set to expire.
Conclusion:
Oracle provides sophisticated customer experience. It provides different avenues for consumer engagement by providing rewarding client management through multidimensional and robust integration from website to client architecture. Oracle is constantly updated with dynamic services integrated such as mobile-ready application, social infused client interaction and unique customer experience portfolios on the go. This write-up is first in the series of complete understanding of the oracle where a layman will be able to understand how database works and how such generic names have different strategic meanings in oracle database environments. In a world of commercialization and industrialization, marketing in the cloud is the order of the day. Oracle database management helps marketers to accurately predict market analysis, accomplish market transformation and smarter sells through powerful market interpretation, customer foresight and increased of shared revenues to partners.
Oracle integrates all forms of marketing perspectives, be it from web, social or consumer contact point of view. It provides them exclusive customer access to logical database by engaging them through all forms of communication in a unified cross-channel service solutions in the cloud. Knowledge of oracle provides users to acquire openness and flexibility for modern day businesses through maximization of sales engagement by configuring efficient product content management. Oracle provides relational database models which give sufficient accesses and privileges to all form of clients at a particular session without any delay and re-authorizations. It optimizes customer experience to adapt to unique form of experience by catapulting and leveraging the reach of the organization in a single unified platform. In short, Oracle RDBMS is simple, super-fast; easily contactable to separate technologies which can be expanded globally, by providing better graphical user experience through various third party responsive and innovating cloud management tools.
Hello and Welcome back to SQL beginners tutorial part 2. In the previous article, as a first part in the series we learnt till how to insert the data in the tables. Today we dig further and we will try to learn more SQL functions and working more with table.
Ok, before moving any further lets talk about naming conventions first. Few important thins in this regard as described below:
- Decide the names in such a way that it clears the purpose of the table like table name 'personal' better than writing 'abc123', although which is correct but it doesn't clear about the kind of data it will store.
- Be consistent in the type of names you create for ex. if your one table name says 'data_puneet' then the other tables in your databasse should also follow the process of having an underscore in the table name. You won't realize it now, but when you revisit your database after some time or if someone looks at it, it will be very easy for him to understand that what kind of objects are represented by which kind of names.
- Try to use command names in capitals which will help differentiating in commands and table or row name. Ex. the command CREATE TABLE 'personal'. Now doesn't it makes it clear ?
- Use Semicolon at the end of your command to clear that the particular command has ended
Please not that the above naming conventions are just to add ease to the work, they are nothing like mandatory to have to work with SQL because SQL is a case insensitive language, so you need not to worry about writing it in any manner. Just make sure you can understand it.
Data Types
As you can remember, while creating the table, we defined the column names with some data type which that column can use. Lets try to see which are the different data types available in SQL that we can use. One imprtant thing to note is that it is not necessary that one data type is consistent in differnt database systems. Our purpose is to understand the data types. So just try to go through the help section of the particular database you are using and which data type are supported by it. Take a look at below screenshot.
Above table is having the columns which are using different data types:
- VARCHAR:This data type hold the text data upto 255 characters and adapt to the length of the data.
- CHAR: Its a rigid data type and prefers data to be of the same length.
- INT: This data type takes only numbers and can also handle negative numbers.
- DATE: This data type handles the dates and it will only allow that format only ex: 11-17-2012.
- BLOB: This data type just needs text in large amount and can be used to add comments for understanding.
The above are the basic and the most used data types, but this is not the end of the list. There are other data types used also and speficily to improve the performance.
Note: We will use VARCHAR in most of the cases because lets say we define a VARCHAR(50) for the name , but the name may or may not be completely upto 50 characters. So VARCHAR will use only that much space which is being defined by the value in that column, where as CHAR data type doesn't support this behaviour.
Checking the Data Type
In case you come accross a table which is already created and you want to see the data types being defined in the column, you need to use the command
DESC <TABLE NAME>
And it will show the output as shown in the below screenshot. Here DESC means describe the table.
Alter Your Table
There are many times when we create a table and after that there is a need to change either the data type of the column or even adding a new column to the table and in that case alter table comes to our rescue. Lets see how to use that. We have our table 'personal' which we created in the last tutorial and in that table lets say you want to add the `email` column. We can do that using below command:
ALTER TABLE `personal` ADD email varchar(50);
The above command in SQL will add the email column in your table and your table will look like this:
Isn't that easy? There is one another hard way, which is, deleting your table and creating it once again. Will it make sense to you? No, i don't think so. So lets stick to alter table only.
Dropping Your Table
Lets say after creating your table, you had a feeling that this is not the table you wanted or all the column names have are wrong and you want to start from scratch. Or you just want to delete the table without any reason, so we have an option in SQL to delete the table in that case using the command:
DROP <Table Name>
In table name , write the name of the table you want to delete and you are done. The table is removed from the database. Please take care while running this particular command as it will also delete any kind of data present in the table and there is no option to take it back. There are cases when system doesn't allow you to drop the table because some fields of that table might be used by another table, which is a concept of refrencing, and in that case we need to remove the refernece first and then drop the table. We will cover that in the coming tutorials.
Defining Primary Key
Every table in the database needs to have a primary key. It means there should be atleast one column in the table which uniquely defines a particular row from the other rows and that value can never be same in two different columns. For example our mobile number is unique. No two people can have the same mobile number and so we can define the number column as the primary key in our table which defines the primary key.
The main purpose of primary key is also that it will never allow you to have two entries with the same primary key, so you will always get the error when you try to insert a row in the table with same primary key. In case there is no field in the table which can be defines as a primary key , then we can use multiple columns together to define a primary key.
You can use below command to define the primary key in a table:
As shown, we have added 'phone no' as primary key and hence it will always be unique in each row , which helps us to identify each row seperately.
Two Columns as Primary Key
In other case, if lets say be any reason we don't want to have phone number as a unique constraint because there might be a case when two people are using the same phone number, so in that case we to still define a primary key , we can use two columns as shown in the below screenshot.
The above combination can be a very good combination to define the primary key and is very comonly used to define the unique constraint whenever we dont have specific column do be defined as primary key. You can add more columns also in case two columns still doesn't make a unique constraint.
Do practice them and feel free to ask me about any issues.
To be Continued..!!!
This Whole world is running because of data and its increasing at an exponential rate. You have data around you everywhere and all the time. Even your personal stuff is your data to use your stuff in better way it needs to be organized well so that they can be found out easily. And this is where a DMBS ( Data Base Management System) Comes in. They do the job of organizing your data.
Now I hope everyone understand that computers understand the language of 0 and 1 which is called a binary language. They don't understand our simple English or Hindi. Even what i am writing here is being interpreted through 0 and 1 only, which some software is doing it for the computer. In the same way database understand through their own language and that language is called as SQL ( Structured Query Language).
What Kind of Data?
A Database can store anything. You just have to define in a proper way that what exactly is needed to be stored so that the DBMS can organize it for you, to be retrieved later. We define types of data to be stored and that data type will accept only that type of data. For example , a phone number will always be an object of type number. You cannot have a phone number as : 984550ADDD.. correct ? So we need to defined the proper type for the data to be stored for better management.
How data is Stored?
For a layman's understanding, a database stores data in the form of a table , which is a combination of rows and Columns. It will organize data in such a way that an information related to each other with different objects is stored in one row and different in other. And different objects are stored in columns. Lets see the example to clearly understand this. ( Note: i'll explain about the tool being used and how to use it, in different tutorials). Lets focus on understanding the concept first.
Lets start with you only. You are a person and you have the following data related to you like : First Name, Start Name, Age , Phone No. There are many other things related to you but lets start with these only. So, the data for for different people will be stored like this as shown in the table.And this is very basic for of data. The table like that contains millions of rows and hundreds of column because they need to store huge amount of data.
Working with SQL
As stated earlier, SQL is a languages which a database management system understands and to make them understand that, we write the SQL statements with which they understand our requirements and return us the relvant answers. So lets start with it rightaway. First we will learn the command to create the table. As defined earlier, we have personal data to store and so lets name the table as Personal. So to create a table with name personal we need to write the below command in sql Developer. You need to either install oracle db and run the below commands in sql developer or you can use the open source wamp server which has mysql database to use. I would prefer wamp. Its free, small , open source and easy to install.
CREATE TABLE `personal` (
`First Name` varchar(50) NOT NULL,
`Last Name` varchar(50) NOT NULL,
`Age` int(10) NOT NULL,
`Phone no` int(10) NOT NULL
)
The above query will create the table personal with different types of data.
Understanding Data Type
As you saw, we create the table with two different types of data which are varchar and INT. Varchar means variable character which means it can store string of different length. In our case we have defined the length of that string to be 50 which means the first name and the last name field cannot be more than 50. Also we have written not null in front of them which means that there should be some value for the Names and the numbers and they cannot be left as empty. Its totally on our requirement where we want to have some field as null or not. For eg. you don't want to mention last name and phone number, so you can create the table by removing not null from those fields. But currently we are having all fields as mandatory.
Lets Insert the data
Now since our table is ready, but it doesn't have any data yet, which is useless, so its time to insert the data in that. And to insert the data we have another simple query as shown in screenshot below.
And after running the above command, you can see the data as shown in the first screenshot earlier.
Congratulations, you have just created your first table using SQL and inserted data in that. Now try creating different types of tables and insert some data accordingly. This is just the beginning and there is lot to come. But before that i would like to know your inputs to start working on rest of the tutorials. Because it really won't make sense if it is not helping you in any way. Also you can pm me in case you are facing issues in creating or inserting data.
There are lot more intersting things to come while learning SQL. And also it is needed badly in Software Industry. So Happy Learning.
The types of users and their roles and responsibilities at a site can vary. A small site can have one database administrator who administers the database for application developers and users. A very large site can find it necessary to divide the duties of a database administrator among several people.
There are following types of users:
1. Database Administrators
2. Security Officers
3. Network Administrators
4. Application Developers
5. Application Administrators
6. Database Users
Database Administrators:
Each database requires at least one database administrator (DBA) to administer it. Because an Oracle database system can be large and can have many users, often this is not a one person job. In such cases, there is a group of DBAs who share responsibility.A database administrator’s responsibilities can include the following tasks:
- Installing and upgrading the Oracle server and application tools
- Allocating system storage and planning future storage requirements for the database system
- Creating primary database storage structures (table spaces) after application developers have designed an application
- Creating primary objects (tables, views, indexes) once application developers have designed an application
- Modifying the database structure, as necessary, from information given by application developers
- Enrolling users and maintaining system security
- Controlling and monitoring user access to the database
- Monitoring and optimizing the performance of the database
- Planning for backup and recovery of database information
- Maintaining archived data on tape
- Backing up and restoring the database
Security Officers:
A security officer enrolls users, controls and monitors user access to the database, and maintains system security.Network Administrators:
A network administrator can administer Oracle networking products, such as Oracle Net.Application Developers:
Application developers design and implement database applications. Their responsibilities include the following tasks:- Designing and developing the database application
- Designing the database structure for an application
- Estimating storage requirements for an application
- Specifying modifications of the database structure for an application
- Relaying the above information to a database administrator
- Tuning the application during development
- Establishing an application’s security measures during development
Application Administrators:
Application administrators administrate a particular application. Each application can have its own administrator.Database Users:
Database users interact with the database through applications or utilities. A typical user’s responsibilities include the following tasks:- Entering, modifying, and deleting data, where permitted
- Generating reports from the data