How to Check Oracle Database Tablespace for individual tablespace.

Check Individual tablespace allocated Quota, Free and used  details.


You can run the below query with DBA privileges.

Below query will give you the result in GB's

SELECT df.tablespace_name "Tablespace",

  totalusedspace "Used GB",

  (df.totalspace - tu.totalusedspace) "Free GB",

  df.totalspace "Total GB",

  ROUND(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "% Free"

FROM

  (SELECT tablespace_name,

    ROUND(SUM(bytes) / (1024*1024*1024)) TotalSpace

  FROM dba_data_files

  GROUP BY tablespace_name

  ) df,

  (SELECT ROUND(SUM(bytes)/(1024*1024*1024)) totalusedspace,

    tablespace_name

  FROM dba_segments

  GROUP BY tablespace_name

  ) tu

WHERE df.tablespace_name = tu.tablespace_name

and df.tablespace_name='USERS'

You will get the Tablespace size in MB's by running the following :

SELECT df.tablespace_name "Tablespace",
  totalusedspace "Used GB",
  (df.totalspace - tu.totalusedspace) "Free GB",
  df.totalspace "Total GB",
  ROUND(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "% Free"
FROM
  (SELECT tablespace_name,
    ROUND(SUM(bytes) / (1024*1024)) TotalSpace
  FROM dba_data_files
  GROUP BY tablespace_name
  ) df,
  (SELECT ROUND(SUM(bytes)/(1024*1024)) totalusedspace,
    tablespace_name
  FROM dba_segments
  GROUP BY tablespace_name
  ) tu
WHERE df.tablespace_name = tu.tablespace_name
and df.tablespace_name='USERS'

Original date created for an Oracle table

Created date from the Oracle table :

To know the original created date of a particular table there is a view called "dba_objects".

here is the view description

SYSTEM@orcl:SQL>desc dba_objects
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 NAMESPACE                                          NUMBER
 EDITION_NAME                                       VARCHAR2(30)

In this view there  is a column named "created " exists in the data dictionary dba_objects view.

using this created we can get the details when was table created.

you can use the following query to get the date.

SELECT CREATED FROM DBA_OBJECTS
WHERE  OBJECT_TYPE = 'TABLE'
AND OBJECT_NAME = 'USERDETAILS';

sample out put will be :

CREATED
---------
13-FEB-18

షిర్డిలో విశాఖ బృందం సాయి భజన! ఓం ఓం సాయిరాం ఓం షిర్డి సాయిరాం .. om om ...

My uncle performance in Shirdi with devotes

Latest 10 rows from oracle.

we can get the recent or latest 10 rows from oracle database based on the sysdate i.e current date.

below query will give you the today's recent 10 transactions.

SELECT *
   FROM (SELECT ID,CREATED FROM EMP
            WHERE TRUNC(CREATED)>=TRUNC(SYSDATE) ORDER BY CREATED DESC )
WHERE ROWNUM <= 10

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

Error::

SQL> startup
ORACLE instance started.

Total System Global Area  778387456 bytes
Fixed Size                  1374808 bytes
Variable Size             494929320 bytes
Database Buffers          276824064 bytes
Redo Buffers                5259264 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

Cause: Either incomplete or backup control file recovery has been performed. when you see these types of recovery you must specify either the RESETLOGS option or the NORESETLOGS option in-order to open your database.


Solution ::

* When you run the startup command you will get the following error.

SQL> startup
ORACLE instance started.

Total System Global Area  778387456 bytes
Fixed Size                  1374808 bytes
Variable Size             494929320 bytes
Database Buffers          276824064 bytes
Redo Buffers                5259264 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01588: must use RESETLOGS option for database open

* Use the following command to recovery the database.

C:\Users\She>rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Sep 14 20:04:01 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1398505858, not open)

RMAN>   recover database;

Starting recover at 14-SEP-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=156 device type=DISK

starting media recovery

archived log for thread 1 with sequence 357 is already on disk as file D:\APP\SH
ESHA\ORADATA\ORCL\REDO03.LOG
archived log for thread 1 with sequence 358 is already on disk as file D:\APP\SH
ESHA\ORADATA\ORCL\REDO01.LOG
archived log file name=D:\APP\She\ORADATA\ORCL\REDO03.LOG thread=1 sequence=3
57
media recovery complete, elapsed time: 00:00:14
Finished recover at 14-SEP-17

RMAN> exit


Recovery Manager complete.

* Once the recovery manager complete then do the following process.

C:\Users\She>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 14 20:05:03 2017

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shut immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  778387456 bytes
Fixed Size                  1374808 bytes
Variable Size             494929320 bytes
Database Buffers          276824064 bytes
Redo Buffers                5259264 bytes
Database mounted.

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Pr
oduction
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\She>sqlplus hr/hr

SQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 14 20:07:02 2017

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>


** In the above we have used the below commands after successfully recovery.

shut immediate;
startup mount:
alter databse open resetlogs;

HOW TO CHANGE NO ARCHIVE LOG MODE TO ARCHIVE LOG IN RAC ENVIRONMENT

HOW TO CHANGE  NO ARCHIVE LOG MODE TO ARCHIVE LOG IN RAC

Enable Archivelog ::

1) verify the archive log mode in any one of the node

To check the archive log list you need to run the commnd " Archive log list"

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     86
Current log sequence           88

SQL> select name, log_mode from v$database;

NAME LOG_MODE
--------- ------------
ocrac NOARCHIVELOG

2) You need to disable the cluster instance parameter by setting cluster_database to FALSE from the current instance.
Pleae follow the below steps.

sqlplus "/ as sysdba"
SQL> alter system set cluster_database=false scope=spfile sid='ocrac';

3) Now you have to shutdown all instances accessing the clustered database

  srvctl stop database -d ocrac

4) Using the local instance, MOUNT the database:

sqlplus "/ as sysdba"
SQL> startup mount

5) Enable archiving

SQL> alter database archivelog;

Now you have successfully enable the archivelog mode.

6) You have to re-enable the instance parameter cluster_database to TRUE from the current instance by using the following command.

SQL> alter system set cluster_database=true scope=spfile sid='ocrac';

7) You have to bounce the local instance.

SQL> shutdown immediate

8) Bring all instance back up using srvctl.

srvctl start database -d ocrac

9) Login to the local instance and verify Archive Log Mode is enabled

sqlplus "/ as sysdba"
SQL> archive log list
Database log mode            Archive Mode
Automatic archival             Enabled
Archive destination           /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence     86
Next log sequence to archive  88
Current log sequence               89

10) After enabling Archive Log Mode, each instance in the RAC configuration can automatically archive redologs.

Disable Archivelog ::

1) set oracle_sid=ocrac
SQL> alter system set cluster_database=false scope=spfile sid='ocrac';
System altered.

2) /u01/app/grid/bin> srvctl stop database -d ocrac -o immediate

3) SQL> startup mount
Total System Global Area 2471931904 bytes
Fixed Size            2255752 bytes
Variable Size          671089784 bytes
Database Buffers     1778384896 bytes
Redo Buffers           20201472 bytes
Database mounted.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 8
Next log sequence to archive 9
Current log sequence 9

SQL> alter database noarchivelog;
Database altered.
SQL> alter system set cluster_database=true scope=spfile sid='ocrac';
System altered.

SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

4) /u01/app/grid/bin>>srvctl start database -d ocrac

5) export ORACLE_SID=ocrac

oracle@ocluster01:~| /home/oracle >sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 21 19:55:19 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL>

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 18
Current log sequence 19
SQL>
 

DDL, DML, DCL and TCL Statements in SQL with Examples

DDL, DML, DCL and TCL Statements in SQL

SQL language is divided into four types of primary language statements.
They are DML, DDL, DCL and TCL.

1. DML (Data Manipulation Language)
2. DDL (Data Definition Language)
3. DCL (Data Control Language)
4. TCL (Transaction Control Language)

Using these statements, we can define the structure of a database by creating and altering database objects.
These are basic operations we perform on data such as selecting a few records from a table, inserting new records, deleting unnecessary records, and updating/modifying existing records.
We also can control which user can read/write data or manage transactions to create a single unit of work.

DML statements include the following:

SELECT – select records from a table
INSERT – insert new records
UPDATE – update/Modify existing records
DELETE – delete existing records

DDL (Data Definition Language)

DDL statements are used to alter/modify a database or table structure and schema.

statements are used to define the database structure or schema.

CREATE – create a new Table, database, schema
ALTER – alter existing table, column description
DROP – delete existing objects from database
TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
COMMENT - add comments to the data dictionary
RENAME - rename an object

DCL (Data Control Language)

DCL statements control the level of access that users have on database objects.

GRANT – allows users to read/write on certain database objects
REVOKE – keeps users from read/write permission on database objects
TCL (Transaction Control Language)

TCL statements allow you to control and manage transactions to maintain the integrity of data within SQL statements.

BEGIN Transaction – opens a transaction
COMMIT Transaction – commits a transaction
ROLLBACK Transaction – ROLLBACK a transaction in case of any error

Reverse A given number using PL/SQL Procedure

Using PL/SQL its very simple and easy to reverse a given number.



CREATE OR REPLACE PROCEDURE REVERSE_NUM(NUM NUMBER)IS
RNO NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('GIVEN NUMBER IS :' || NUM);
FOR I IN REVERSE 1..LENGTH(NUM)
LOOP
RNO := RNO || SUBSTR(NUM,I,1);
END LOOP;
DBMS_OUTPUT.PUT_LINE('REVERSE NUMBER IS : ' || RNO);
END;
/

To Execute this procedure use the below command:

EXECUTE REVERSE_NUM(1234);

Differences Between Procedures & Functions

Differences Between Procedures & Function

Stored Procedures are pre- compiled objects which are compiled for first time and its compiled format is saved which executes (compiled code) whenever it is called. But Function is compiled and executed every time when it is called.

• Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values).
• Both functions and stored procedures can accept parameters. Functions can accept input parameters but can return only a single return value.     Stored procedures can also accept OUTPUT parameters.
• Functions can be called from Procedure whereas Procedures cannot be called from Function.
• Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement, whereas Function allows only SELECT statement in it.
• Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.
• Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.
• We can go for Transaction Management in Procedure whereas we can't go in Function.
• Functions are normally used for computations where as procedures are normally used for executing business logic.
• Function returns 1 value only. Procedure can return multiple values (max 1024).
•  Functions cannot alter data or objects in a server. Stored procedures can alter data and objects in database and server.

Summary

Basic Difference. Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values). Functions can have only input parameters for it whereas Procedures can have input/output parameters . Functions can be called from Procedure whereas Procedures cannot be called from Function

Syntax to create a procedure is ::

CREATE [OR REPLACE] PROCEDURE proc_name [list of parameters]
IS   
   Declaration section
BEGIN   
   Execution section
 
EXCEPTION   
  Exception section
END;

Syntax to create a function is ::

CREATE [OR REPLACE] FUNCTION function_name [parameters]
RETURN return_datatype; 
IS 
Declaration_section 
BEGIN 
Execution_section
Return return_variable; 
 
EXCEPTION 
exception section 
Return return_variable; 
END;