Oracle Tablespace(Drop and Create)

Oracle Tablespace(Drop and Create)

Today one of developer send me to verify, about some PL/SQL statement regarding Drop and Create tablespace in Oracle. well.. i'll show you that PL/SQL statement first and we will have a look in to this after.


SET SERVEROUTPUT ON;
DECLARE
  v_exists         NUMBER:=0;
  v_if_exists_drop NUMBER:=1;
BEGIN
  SELECT COUNT(*)
  INTO v_exists
  FROM dba_tablespaces
  WHERE tablespace_name = 'TBS_DBUG';
  IF v_exists           > 0 THEN
    dbms_output.put_line('TBS_DBUG tablespace exists...!');
    IF v_if_exists_drop=1 THEN
      BEGIN
        EXECUTE IMMEDIATE 'DROP TABLESPACE TBS_DBUG INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS';
      END;
      dbms_output.put_line('TBS_DBUG tablespace Deleted...!');
    END IF;
    BEGIN
      EXECUTE IMMEDIATE 'CREATE TABLESPACE TBS_DBUG DATAFILE ''E:\Oracle\DBUG\tbs_dbug_01.dbf'' SIZE 5000K REUSE AUTOEXTEND ON';
    END;
    dbms_output.put_line('TBS_DBUG tablespace Created...!');
  ELSE
    dbms_output.put_line('TBS_DBUG tablespace does not exist...!');
    BEGIN
      EXECUTE IMMEDIATE 'CREATE TABLESPACE TBS_DBUG DATAFILE ''E:\Oracle\DBUG\tbs_dbug_01.dbf'' SIZE 5000K REUSE AUTOEXTEND ON';
    END;
    dbms_output.put_line('TBS_DBUG tablespace Created...!');
  END IF;
EXCEPTION
WHEN OTHERS THEN
  RAISE;
END; 
/ 


Ok! first I'm going to discuss good things in above SQL statement.

1. First I like the way written this, because mainly validate tablespace is exists.
2. Then good output messages(DBMS_OUTPUT)
3. And used Exception that is good too

Ok! what about bad practices or whats wrong is that?

1. Ok! first Drop tablespace is very tricky thing and you have to do it very carefully,
1. Best way, you have to check if tablespace contains any objects(Tables,Index, ect) And then you can go for delete.can find tablespace contain objects in "dba_segments" system view.
2. But note some newly created tables(without any records) then it will not listed in "dba_segments" system view then, you have to find it in "dba_objects,dba_tables" like wise.
3. If this is going to execute in Production database then you have to make-sure related backups of this database or schema.
4. some times your tablespace will be very huge then, recommended for drop files one by one because this is very costly.And finally delete tablespace with last data file.
• ALTER TABLESPACE TBS_DBUG DROP DATAFILE 'E:\Oracle\DBUG\tbs_dbug_02.dbf';
5. I personally prefer Create tablespace & Drop tablespace with two different PL/SQL scripts. But it was Ok with One script also.       
2. Well! Nothing much to say with Create tablespace, 
1. Best practices way is "AUTOEXTEND OFF" unless if you have any different situation such as development Database.
2. If you have tablespace groups then use it.
3. Tablespace size is very important before create tablespace, if you have decide max size of tablespace. or you have to create tablespace growth plan(daily,weekly,monthly and yearly)      

In general if you can create a common procedure for create or drop tablespaces then is is much more clear. If anyone of you have good suggestion or idea please add some comments. most welcome your comments and ideas.         

     

What's new in SQL server 2012 - Date time

What's new in SQL server 2012 - Date time

                                                Date & Time is main area when we talk about database or database development. I have discuss many issue in SQL Server Data & time data type. if you need to read it again then following blog post will reach you there, 

• ISDATE Function
• Minimum year value

 well, today I'm going to discuss about SQL Server 2012 newly added Date & time related features.Before start this topic hope you will notice I have discuss about ISDATE function in my above blog link. As a solution of SQL Server 2012 development team will introduce following new functions, 

• TRY_CONVERT

                          This is very good move of SQL Server 2012 because T-SQL has very poor data validation,those issue will be a good solution above Try_convert function.   

USE tempdb
GO
SELECT CASE WHEN TRY_CONVERT(int, '1,200.00') IS NULL THEN 'False' ELSE 'True' END AS ResultOut;
GO
SELECT CASE WHEN TRY_CONVERT(datetimeoffset, '2013-11-08 23:59:59.3593750 +05:30') IS NULL THEN 0 ELSE 1 END AS is_date;
GO

• TRY_PARSE 

                      This function also more likely above Try_convert function. 

USE tempdb
GO
SELECT TRY_PARSE('100' AS int) AS ResultOut;
GO
SELECT TRY_PARSE('100.1' AS int) AS ResultOut;
GO
SELECT TRY_PARSE('100.0' AS int) AS ResultOut;
GO

                    only two different i have notice above both functions as follows,  

• Try_parse only allows string as input.
• try_parse just parse values but try_convert convert the values like this,

SELECT TRY_PARSE('100.0' AS int) AS ResultOut;  --True
GO
SELECT TRY_CONVERT(int,'100.0') AS ResultOut; --Error
GO

• PARSE

                This also more likely TRY_PARSE function. only different is if values not match then it will returns error,

USE tempdb
GO
SELECT PARSE('100' AS int) AS ResultOut; --True
GO
SELECT PARSE('100.1' AS int) AS ResultOut; --False
GO
SELECT PARSE('€345,98' AS money USING 'de-DE') AS ResultOut;
GO
SELECT PARSE('345,98' AS money USING 'ja-JP') AS ResultOut;
GO

• DATEFROMPARTS 

                               This is very simple function, it will concatenate year , month and day. And validate it too,

USE tempdb
GO
SELECT DATEFROMPARTS ( 2013, 11, 30 ) AS ResultOut;
GO
SELECT DATEFROMPARTS ( 2013, 11, 31 ) AS ResultOut;--error
GO
SELECT DATEFROMPARTS ( 2012, 02, 29 ) AS ResultOut;
GO
SELECT DATEFROMPARTS ( 2013, 02, 29 ) AS ResultOut;--error
GO

• DATETIME2FROMPARTS , DATETIMEFROMPARTS & DATETIMEOFFSETFROMPARTS 

                                          These functions mostly same as above function, after seen of Syntax you will realize it,

Syntax         
   

DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )

DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )

           

DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )

 
Following example will help you more clearly,

USE tempdb
GO
SELECT DATETIMEFROMPARTS ( 2010, 12, 31, 23, 59, 59, 0 ) AS ResultOut;
GO
SELECT DATETIME2FROMPARTS ( 2010, 12, 31, 23, 59, 59, 0, 0 ) AS ResultOut;
GO
SELECT DATETIMEOFFSETFROMPARTS ( 2010, 12, 31, 14, 23, 23, 0, 12, 0, 7 ) AS ResultOut;
GO

• EOMONTH 

                     This is one of my favourite function.Earlier SQL Server versions if you need to get end of the moth I have used following SQL statement,

USE tempdb
GO
DECLARE @date datetime= '11/20/2013';
SELECT DATEADD (mm,1,@date-(DAY(@date)-1))-1;
GO

  But now you have no need to write above painful SQL statements you can use as follows,

USE tempdb
GO
DECLARE @date datetime= '11/20/2013';
SELECT EOMONTH ( @date ) AS ResultOut;
GO 

SQL Server MERGE + IGNORE_DUP_KEY = ON

SQL Server MERGE + "IGNORE_DUP_KEY = ON"

                                                                         I'm huge fan of SQL Server Merge command and I'm no hesitation to recommended SQL Server Merge DML statement, if you have INSERT,UPDATE and DELETE at once and with validate not exists records.But recently one of our developer reported Insert records will fail with Duplicate key error but they already have "IGNORE_DUP_KEY = ON" their target table.Initially I was hope we can insert duplicate records without any error if you have  "IGNORE_DUP_KEY = ON" but I was wrong that time. Just I have read SQL Server MERGE  Syntax and remarks on MSDN. And I seen Microsoft mention clearly "If IGNORE_DUP_KEY is set to ON for any unique indexes on the target table, MERGE ignores this setting." Well... so no argument on that :) . Long time back (around 5 years ) I have publish article on SQLServerCentral.com(one of my favourice web site) about "Understanding the MERGE DML Statement in SQL Server 2008" and I have not include this on my article. Any way I'm recommended you to read this article if you also still new with SQL Server MERGE command.

Well let's try to reproduce "duplicate key" Error     

USE [tempdb]
GO
IF  EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[tblOffsetAndFetch]') AND [type] in (N'U')) BEGIN
    DROP TABLE [dbo].[tblOffsetAndFetch];
END  
GO
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[tblOffsetAndFetch]') AND [type] in (N'U'))
BEGIN
    CREATE TABLE [dbo].[tblOffsetAndFetch](
        [id] [int] NOT NULL,
        [name] [varchar](50) NULL,
        [cDate] [datetime] NULL,
        [intData] [int] NULL,
        [decimalData] [decimal](21, 5) NULL,
        [floatData] [float] NULL,
    PRIMARY KEY CLUSTERED
    (
        [id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY];
END
GO
SELECT * FROM [dbo].[tblOffsetAndFetch];
GO
MERGE [dbo].[tblOffsetAndFetch] AS A
USING (SELECT '01' [id],'Records 01' [name],Getdate() [cDate],01 [intData],1.1 [decimalData],1.11 [floatData]
UNION
SELECT '01' [id],'Records 02' [name],Getdate() [cDate],02 [intData],2.2 [decimalData],2.22 [floatData]) AS I
ON (A.id = I.id)
WHEN NOT MATCHED
THEN INSERT VALUES(I.id, I.[name], I.[cDate], I.[intData], I.[decimalData], I.[floatData]);
GO
SELECT * FROM [dbo].[tblOffsetAndFetch];
GO

Results,

 (0 row(s) affected)
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK__tblOffse__3213E83FABB9779A'. Cannot insert duplicate key in object 'dbo.tblOffsetAndFetch'. The duplicate key value is (1).
The statement has been terminated.

(0 row(s) affected)

Well! hope now you will understand the above error occur situation.And I have found some helpfull artical on SQL Server MERGE DML statement and I hope it will help you all, 

•  Indexed view is not updated on data changes in base table
• If you are using indexed views and MERGE, please read this!

Cheers...
and Most welcome your comments and ideas...

What's new in SQL server 2012

What's New In SQL Server 2012 - OFFSET and FETCH

                                                    Earlier days(SQL Server 2000) hope you all remember when we need to display reports with page break, For a example if you have 100 records but you need to display 5 rows per page then how much difficult to write T/SQL script for that?, hope you all remember following SQL statements,

SQL Server 2000(T-SQL)    

USE tempdb
GO
IF  EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[tblOffsetAndFetch]') AND [type] in (N'U')) BEGIN
    DROP TABLE [dbo].[tblOffsetAndFetch];
END   
GO
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[tblOffsetAndFetch]') AND [type] in (N'U'))
BEGIN
    CREATE TABLE [dbo].[tblOffsetAndFetch](
        [id] [int] PRIMARY KEY,
        [name] [varchar](50),
        [cDate] [datetime] NULL,
        [comments] [varchar](200) NULL
    ) ON [PRIMARY];
END
GO
INSERT INTO [dbo].[tblOffsetAndFetch]([id],[name],[cDate],[comments])
SELECT id,name,crdate,name+'| Type - '+xtype FROM sys.sysobjects;
GO
 

--For SQL server 2000
DECLARE @noofrows        smallint;
DECLARE @pages            smallint;
DECLARE @pageNumber        smallint;
DECLARE @ssql            varchar(8000);
SET @noofrows=5;
SET @pageNumber=0;
SET @pages=((SELECT COUNT(*) FROM [dbo].[tblOffsetAndFetch])/@noofrows)+CASE WHEN ((SELECT COUNT(*) FROM [dbo].[tblOffsetAndFetch])%@noofrows)>1 THEN 1 ELSE 0 END;

SELECT * FROM [dbo].[tblOffsetAndFetch] ORDER BY id;

WHILE @pages>@pageNumber BEGIN
    SET @ssql='SELECT TOP '+CAST(@noofrows AS varchar(3))+' * FROM [dbo].[tblOffsetAndFetch] M WHERE M.id NOT IN(SELECT TOP '+CAST(@noofrows*@pageNumber AS varchar(3))+' D.id FROM [dbo].[tblOffsetAndFetch] D ORDER BY id);';
    EXEC(@ssql);
    SET @pageNumber=@pageNumber+1
END;   
GO

After SQL Server 2005 released, it contains many new features like

ROW_NUMBER() according to row_number function you can convert above task 

very simple way as follows,

--For SQL server 2005 and 2008R2

;WITH T (RowNumber,id,name,cDate,comments)
AS
 (
  SELECT ROW_NUMBER() OVER(ORDER BY A.id) AS RowNumber,A.id, A.name, A.cDate, A.comments 
  FROM [dbo].[tblOffsetAndFetch] A
 ) 
SELECT * FROM T WHERE RowNumber BETWEEN 0 AND 5;

  
;WITH T (RowNumber,id,name,cDate,comments)
AS
 (
  SELECT ROW_NUMBER() OVER(ORDER BY A.id) AS RowNumber,A.id, A.name, A.cDate, A.comments 
  FROM [dbo].[tblOffsetAndFetch] A
 ) 
SELECT * FROM T WHERE RowNumber BETWEEN 6 AND 10; 

Well After SQL Server 2012 released, SQL Server Development team has done many new addition to 

SQL Server like OFFSET and FETCH.With Using above commands you can fulfill above task with following way,

--1st 5 rows
SELECT * 
FROM [dbo].[tblOffsetAndFetch]
ORDER BY id
OFFSET 0 ROWS
FETCH NEXT 5 ROWS ONLY;

--2nd 5 rows
SELECT * 
FROM [dbo].[tblOffsetAndFetch]
ORDER BY id
OFFSET 5 ROWS
FETCH NEXT 5 ROWS ONLY;

--3rd 5 rows
SELECT * 
FROM [dbo].[tblOffsetAndFetch]
ORDER BY id
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY;

Now you will notice how much easy with new "OFFSET and FETCH" commands in SQL server. 

mySQL you have limit function and PL/SQL rownumber column you can help to do above task in same output results. 

But this time I hope SQL server more smarter than Oracle(Im not sure about new PL/SQL commands in Oracle 12c) 

Let me explain about execution plan for this,

Well! now you can realize how much performance impact on SQL server new commands.

Cheers... 
and Most welcome your comments and ideas...