Oracle 12c Tutorial step by step.

How to use Row Limiting Clause in Oracle 12c SQL.

Row limiting clause is introduced in oracle 12c R1.

• Using the row_limiting_clause to limit the number of rows that are returned by a query.

• using this clause to implement Top-N reporting.

• You can specify the percentage of rows or number of rows to return with the FETCH FIRST keyword.

• You can use the OFFSET keyword to specify that the returned rows begin with a row after the first row of the full result set.
• The WITH TIES keyword includes additional rows with the same ordering keys as the last row of the row-limited result set. (You must specify ORDER BY in the query.

You specify the row_limiting_clause in the SQL SELECT statement by placing it after the ORDER BY clause.

Use of FETCH FIRST keyword

The FETCH FIRST keyword show the first five record according order by clause, in following query i used order by clause in ascending order so that’s why it’s showing first top five rows and if you use DESC then it’ll showing also top 5 rows in descending order.

SQL> select empno,ename from emp
     order by empno
     fetch first 5 rows only;

EMPNO      ENAME
---------- ----------
7369       SMITH
7499       ALLEN
7521       WARD
7566       JONES
7654       MARTIN

Use of OFFSET KEYWORD

Offset keyword is show the next top five rows after first top five rows.

SQL> select empno,ename from emp
     order by empno
     offset 5 rows fetch next 5 rows only;

EMPNO      ENAME
---------- ----------
 7698      BLAKE
 7782      CLARK
 7788      SCOTT
 7839      KING
 7844      TURNER

Use of WITH TIES keyword.

Using the WITH TIES clause return first 5 rows from the ordered set and if any other rows is additional ties with the output, it shows in the last.

SQL> select deptno from emp
     order by deptno
     fetch first 5 rows with ties;

 DEPTNO
----------
 10
 10
 10
 20
 20
 20
 20
 20

8 rows selected.

How to create PARFILE and How to use it.

How to create PARFILE and How to use it.

PARFILE is a simple text file also called it parameter file, which is used by the expdp utility. It means PARFILE can be created by client (database user) on his own system, and in that case no need to involve the DBA whereas files like DUMPFILE and LOGFILE are the files which are created by the expdp utility on the server.

And also with the creation of PARFILE, client (Database User) can also modify and update the PARFILE as per the requirements.

Location for the PARFILE:-

The Database user can create a PARFILE anywhere on its own system.

How to set name of PARFILE?

In the ORACLE DATABASE there is no restrictions for the name of PARFILE. You can give any name what you want to give to parfile, and about extension, same like logfile and dumpfile you can give whatever extension what you want for the PARFILE.

But we recommend to give (.par) extension, it will be helping us to identified the PARFILE from other files in your system.

Contents for the PARFILE?

The PARFILE contains the parameters which is used by the expdp utility like,

1. Directory name.
2. Dumpfile name.
3. Logfile name.
4. Schemas name (if you want to perform the schemas export).
5. Tablespace name ( if you want to perform exporting tablespace).
6. Full (if you want to perform full database export).

How to create PARFILE

STEP-1

Firstly we need to create a folder on any location in your system, so before doing anything you just make a folder and give a logical name to this directory then make a directory, following steps.

As in this tutorial I’m going to use a folder which is name is ‘table_exp’ and the folder location is:

$mkdir /u01/table_exp

Step 2: Now create Directory object.

So for the creating directory object we need to login as SYS user with SYSDBA privileges.

$sqlplus / as sysdba
SQL> create directory tables_exp as ‘/u01/table_exp’;

Above command will create the directory objects.

Now after create the directory we need to grant some mandatory privileges to those users who wants to perform the export.

Like SCOTT and SYSTEM users wants to perform export.

SQL> grant read, write on directory tables_exp to scott,system;

STEP 3. CREATE A PARFILE.

Just create a plain text file (notepad file) in any location in your system, for demonstration I have created a file in “/u01/exp_scott.par” directory.

After making the text file we have need to write all the necessary parameters in this file.

Directory= tables_exp
Dumpfile=table_scott.dmp
Logfile=table_scott.log
Tables=emp,dept,jobhistory

After making this file we need to run expdp utility, using following command.

$expdp scott/tiger@db11g parfile=’/u01/ exp_scott.par’

This command is automatically take an export backup of those tables which we write in the parfile.

NOTE: – The PARFILE cannot be specified within a parameter file and also you cannot write login information of the database in parameter file.

How to Create an External user using Create user statement
The external user and his authentication are managed by external services like as network service or operating system services. The user who is authenticate by the OS (operating system) can access the Database without any password.

The external users are classic default and regular database users (non-dba) who are assigned standard database roles (such as CONNECT and RESOURCE), but no SYSOPER (database operator) or SYSDBA (database administrator) privilege.

Before creating an external user you must check the Oracle OS_AUTHENT_PREFIX initialization parameter, using following command.

SQL> SHOW PARAMETER os_authent_prefix

NAME                         TYPE        VALUE
-----------------------    ----------- ------------------------------
os_authent_prefix           string      ops$

You can see the value of os_authent_prefix parameter default value is “ops$”, if it’s not same like that you can change it using ALTER COMMAND.

Now create an external user using CREATE command, using following.

SQL>Create user “ops$oracle” Identified by externally;

Your external user has been created successfully, Now we have need to grant mandatory system privilege which is “Create Session” so that it can be log on to database.

SQL> GRANT create session TO “ops$oracle”;

Now external user can be login in database without using username or password, it’s just use the forward slash (/).

$sqlplus /

SQL>show user

You can watch my YouTube video to see the execution of each step.

How to use REMAP_TABLE parameter in impdp utility
Like you want to import a table from your existing logical backup (export table), but when you checked your database you saw that table is already exist in your database which is created by another user.

So in this situation, how to import that table because it’s import for you, so you can make a duplicate copy of the table using REMAP_TABLE parameter.

So command of importing table will remain the same as previous, we just use REMAP_TABLE parameter in the command.

$impdp scott/tiger directory=dir dumpfile=emp.dmp logfile=emp.log tables=emp remap_table=scott.emp:emp_copy

NOTE:- The REMAP_TABLE parameter use two values, the old name of the table along with user name (schema name like scott.emp) and second is the new name of the table which’s specify by you, both the names are separated by colons.

So after execution of this command you will get a new table in your database which is the duplicate table.