Learn DB Concepts with me...

Subscribe to Learn DB Concepts with me... feed
“​Perfection is not attainable, but if we chase perfection we can catch excellence” - Arvind Toorpuarvind toorpunoreply@blogger.comBlogger200125
Updated: 11 hours 51 min ago

passing variables in sqlplus scripts

Wed, 2016-06-29 13:14
SET VERIFY OFF

ACCEPT par1 prompt "ENTER PARAMETER #1: "

ACCEPT par2 prompt "ENTER PARAMETER #2: "

execute pkg_TEST_VARIABLES.TEST_PASS_VARIABLES ( &&par1, &&par2);
Categories: DBA Blogs

select grants on tables to users or roles

Wed, 2016-06-29 11:28

select grants on tables to users or roles

SELECT
  OWNER,
  GRANTEE,
  GRANTOR,
  TABLE_NAME,
  PRIVILEGE
FROM
  DBA_TAB_PRIVS
WHERE
  table_name   = 'TABLENAME'
AND PRIVILEGE IN ('DELETE','INSERT','SELECT','UPDATE')
AND GRANTEE   IN ('ROLE1','ROLE2')
ORDER BY
  1,2;



select grants on table to a user thru role or direct priv

select Grantee,'Granted Through Role' as Grant_Type, role, table_name
from role_tab_privs rtp, dba_role_privs drp
where rtp.role = drp.granted_role
and table_name = 'TABLENAME'
union
select Grantee,'Direct Grant' as Grant_type, null as role, table_name
from dba_tab_privs
where table_name = 'TABLENAME' ;
Categories: DBA Blogs

Online redefination of table

Tue, 2016-06-28 09:55

Online table redefinition of a table
 Lets create a table with some dependent objects for this setup.

DROP PROCEDURE get_description;
DROP VIEW redef_tab_v;
DROP SEQUENCE redef_tab_seq;
DROP TABLE redef_tab PURGE;

CREATE TABLE redef_tab (
  id           NUMBER,
  description  VARCHAR2(50),
  CONSTRAINT redef_tab_pk PRIMARY KEY (id)
);

CREATE VIEW redef_tab_v AS
SELECT * FROM redef_tab;

CREATE SEQUENCE redef_tab_seq;

CREATE OR REPLACE PROCEDURE get_description (
  p_id          IN  redef_tab.id%TYPE,
  p_description OUT redef_tab.description%TYPE) AS
BEGIN
  SELECT description
  INTO   p_description
  FROM   redef_tab
  WHERE  id = p_id;
END;
/

CREATE OR REPLACE TRIGGER redef_tab_bir
BEFORE INSERT ON redef_tab
FOR EACH ROW
WHEN (new.id IS NULL)
BEGIN
  :new.id := redef_tab_seq.NEXTVAL;
END;
/


Lets confirm that objects  are created 


COLUMN object_name FORMAT A20
SELECT object_name, object_type, status FROM user_objects ORDER BY object_name;

OBJECT_NAME          OBJECT_TYPE         STATUS
-------------------- ------------------- -------
GET_DESCRIPTION      PROCEDURE           VALID
REDEF_TAB            TABLE               VALID
REDEF_TAB_BIR        TRIGGER             VALID
REDEF_TAB_PK         INDEX               VALID
REDEF_TAB_SEQ        SEQUENCE            VALID
REDEF_TAB_V          VIEW                VALID

6 rows selected.


Basic Online Table Redefinition

Now we perform an online table redefinition.

-- Check table can be redefined

EXEC DBMS_REDEFINITION.can_redef_table('ATOORPU', 'REDEF_TAB'); 
--- SCHEMA = ATOORPU, TABLE_NAME=REDEF_TAB


CREATE TABLE redef_tab2 AS
SELECT *
FROM   redef_tab WHERE 1=2;

-- Start Redefinition
EXEC DBMS_REDEFINITION.start_redef_table('ATOORPU', 'REDEF_TAB', 'REDEF_TAB2');


-- Optionally synchronize new table with interim data before index creation
EXEC DBMS_REDEFINITION.sync_interim_table('ATOORPU', 'REDEF_TAB', 'REDEF_TAB2');

-- Add new PK.
ALTER TABLE redef_tab2 ADD (CONSTRAINT redef_tab2_pk PRIMARY KEY (id));

-- Complete redefinition
EXEC DBMS_REDEFINITION.finish_redef_table('ATOORPU', 'REDEF_TAB', 'REDEF_TAB2');

-- Remove original table which now has the name of the new table
DROP TABLE redef_tab2;



Finally, we verify the status of the schema objects.


COLUMN object_name FORMAT A20
SELECT object_name, object_type, status FROM user_objects ORDER BY object_name;

OBJECT_NAME          OBJECT_TYPE         STATUS
-------------------- ------------------- -------
GET_DESCRIPTION      PROCEDURE           VALID
REDEF_TAB            TABLE               VALID
REDEF_TAB2_PK        INDEX               VALID
REDEF_TAB_SEQ        SEQUENCE            VALID
REDEF_TAB_V          VIEW                VALID


Note : The GET_DESCRIPTION procedure and REDEF_TAB_V view are still valid, but the REDEF_TAB_BIR trigger is gone. The trigger was still associated with the original table, renamed to REDEF_TAB2, so when the original table was dropped, the trigger was dropped with it.
Categories: DBA Blogs

ORA-00020: maximum number of processes exceeded

Thu, 2016-06-16 09:45


ORA-00020: maximum number of processes



This error occurs when your total numbers of sessions connecting to oracle database has exceeded the max limit set in parameter file. Simplest way to overcome this error is to reset the max sessions value to a greater value than existing.Here is how to do it
 



oracle@LINUX201:[~] $ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 13 10:20:26 2016

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

ERROR:
ORA-00020: maximum number of processes (500) exceeded




oracle@LINUX201:[~] $ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 13 10:23:42 2016

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

ERROR:
ORA-00020: maximum number of processes (500) exceeded


Enter user-name:

Disconnected from ORACLE

I wasn't able get into the oracle database to kill some database session. So I tried to kill few sessions on OS to make my way into DB.

oracle@LINUX201:[~] $ ps -ef|grep oracle
.
.
.
.
.
.
.

oracle   64373     1  0 Jun08 ?        00:00:03 oracleQPDEV (LOCAL=NO)
oracle   64540     1  0 Jun08 ?        00:00:00 oracleQPDEV (LOCAL=NO)
oracle   64554     1  0 Jun08 ?        00:00:01 oracleQPDEV (LOCAL=NO)
oracle   64633     1  0 Jun08 ?        00:00:03 oracleQPDEV (LOCAL=NO)
oracle   64637     1  0 Jun08 ?        00:00:00 oracleQPDEV (LOCAL=NO)
.
.
.
oracle   65186     1  0 Jun08 ?        00:00:04 oracleQPDEV (LOCAL=NO)
oracle   65190     1  0 Jun08 ?        00:00:00 oracleQPDEV (LOCAL=NO)
oracle   65192     1  0 Jun08 ?        00:00:01 oracleQPDEV (LOCAL=NO)
oracle   65202     1  0 Jun08 ?        00:00:00 oracleQPDEV (LOCAL=NO)
oracle   65206     1  0 Jun08 ?        00:00:02 oracleQPDEV (LOCAL=NO)
root     65407 65381  0 May16 pts/2    00:00:00 sudo -u oracle -i
oracle   65408 65407  0 May16 pts/2    00:00:00 -bash
oracle   65458 65408  0 May16 pts/2    00:00:00 sqlplus
oracle   65459 65458  0 May16 ?        00:00:00 oracleQPDEV (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   65518     1  0 Jun08 ?        00:00:00 oracleQPDEV (LOCAL=NO)
oracle   65520     1  0 Jun08 ?        00:00:02 oracleQPDEV (LOCAL=NO)
oracle   65534     1  0 Jun08 ?        00:00:00 oracleQPDEV (LOCAL=NO)

oracle@LINUX201:[~] $ kill -9 64785
oracle@LINUX201:[~] $ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 13 10:26:25 2016

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

ERROR:
ORA-00020: maximum number of processes (500) exceeded


Enter user-name: ^C

Killing few processes on Linux :

oracle@LINUX201:[~] $ kill -9 65192 65085 64785 64777 64655 64653 64637


oracle@LINUX201:[~] $ ps -ef|grep 65192 65085 64785 64777 64655 64653 64637

.
.
.
.
oracle   50258     1  0 Jun07 ?        00:00:04 oracleQPDEV (LOCAL=NO)
oracle   50264     1  0 Jun07 ?        00:00:03 oracleQPDEV (LOCAL=NO)
oracle   50268     1  0 Jun07 ?        00:00:02 oracleQPDEV (LOCAL=NO)
.
.
.
oracle   64554     1  0 Jun08 ?        00:00:01 oracleQPDEV (LOCAL=NO)
oracle   64633     1  0 Jun08 ?        00:00:03 oracleQPDEV (LOCAL=NO)
oracle   65186     1  0 Jun08 ?        00:00:04 oracleQPDEV (LOCAL=NO)
oracle   65190     1  0 Jun08 ?        00:00:00 oracleQPDEV (LOCAL=NO)
oracle   65202     1  0 Jun08 ?        00:00:00 oracleQPDEV (LOCAL=NO)
oracle   65206     1  0 Jun08 ?        00:00:02 oracleQPDEV (LOCAL=NO)
.
.
.

oracle@LINUX201:[~] $ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 13 10:30:07 2016

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, OLAP, Data Mining and Real Application Testing options

SQL> show parameter process;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     1
cell_offload_processing              boolean     TRUE
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
global_txn_processes                 integer     1
job_queue_processes                  integer     1000
log_archive_max_processes            integer     4
processes                            integer     500
processor_group_name                 string


Now reset the max processes to a greater value:

SQL> alter system set processes=1200 scope=spfile;

System altered.

SQL>  show parameter process;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     1
cell_offload_processing              boolean     TRUE
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
global_txn_processes                 integer     1
job_queue_processes                  integer     1000
log_archive_max_processes            integer     4
processes                            integer     500
processor_group_name                 string
SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
QPDEV     READ WRITE

This will need a restart to take affect


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup;
ORACLE instance started.

Total System Global Area 3206836224 bytes
Fixed Size                  2257520 bytes
Variable Size            1275071888 bytes
Database Buffers         1912602624 bytes
Redo Buffers               16904192 bytes
Database mounted.
Database opened.

SQL> show parameter process

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     1
cell_offload_processing              boolean     TRUE
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
global_txn_processes                 integer     1
job_queue_processes                  integer     1000
log_archive_max_processes            integer     4
processes                            integer     1200
processor_group_name                 string

SQL>
Categories: DBA Blogs

Create Temporary Tables in Oracle

Fri, 2016-06-10 13:33

Global Temporary Tables in Oracle

Temporary tables are useful in applications where a result set is to be buffered, perhaps because it is constructed by running multiple DML operations. For example, consider the following:

A Web-based airlines reservations application allows a customer to create several optional itineraries. Each itinerary is represented by a row in a temporary table. The application updates the rows to reflect changes in the itineraries. When the customer decides which itinerary she wants to use, the application moves the row for that itinerary to a persistent table.

During the session, the itinerary data is private. At the end of the session, the optional itineraries are dropped.

This statement creates a temporary table that is transaction specific:

NOTE : Indexes can be created on temporary tables. They are also temporary and the data in the index has the same session or transaction scope as the data in the underlying table.

*********************************************************************************
HERE is an example to create a global temporary table with on commit DELETE ROWS :
*********************************************************************************

sql>  CREATE GLOBAL TEMPORARY TABLE admin_work_area
        (startdate DATE,
         enddate DATE,
         class CHAR(20))
      ON COMMIT DELETE ROWS;
      
      
    
sql>  insert into ADMIN_WORK_AREA values (sysdate,sysdate+ 1,'A');

1 row inserted.


sql> select * from ADMIN_WORK_AREA;

commit;

Commit complete.

sql> select * from ADMIN_WORK_AREA;


NOTE: records in this temp table will be deleted upon commit. This is equivalent to truncating table on commit.

*********************************************************************************
HERE is an example to create a global temporary table with on commit PRESERVE ROWS :
*********************************************************************************


sql>  CREATE GLOBAL TEMPORARY TABLE admin_work_area
        (startdate DATE,
         enddate DATE,
         class CHAR(20))
      ON COMMIT PRESERVE ROWS;
      
            
sql>  insert into ADMIN_WORK_AREA values (sysdate,sysdate+ 1,'A');

1 row inserted.

1 row inserted.

sql>  select * from ADMIN_WORK_AREA;

commit;

Commit complete.

sql>  select * from ADMIN_WORK_AREA;


NOW exit the session and login back and select the table.

sql>  select * from ADMIN_WORK_AREA;

table is empty

NOTE: records (rows) in this temp table will be deleted upon session exit only, as long as you are using same session you can see these rows. 
This is equivalent to truncating table on session exit.

Categories: DBA Blogs

Inserting Data into table with DML Error Logging (catching errors whiles inserting data into table)

Fri, 2016-06-10 10:29


Inserting Data with DML Error Logging:
When you load a table using an INSERT statement with subquery, if an error occurs, the statement is terminated and rolled back in its entirety. This can be wasteful of time and system resources. For such INSERT statements, you can avoid this situation by using the DML error logging feature.

To use DML error logging, you add a statement clause that specifies the name of an error logging table into which the database records errors encountered during DML operations. When you add this error logging clause to the INSERT statement, certain types of errors no longer terminate and roll back the statement. Instead, each error is logged and the statement continues. You then take corrective action on the erroneous rows at a later time.

DML error logging works with INSERT, UPDATE, MERGE, and DELETE statements. This section focuses on INSERT statements.


--------------------------------------------------------
--  DDL for Table ATEST1
--------------------------------------------------------


  CREATE TABLE "ATOORPU"."ATEST1"
   (    "ID" NUMBER constraint ATEST1_PK PRIMARY KEY,
    "TDATE" DATE,
    "AMOUNT" VARCHAR2(20 BYTE),
    "ORD_NO" NUMBER
   ) ;

--------------------------------------------------------
INSERT SOME VALUES
--------------------------------------------------------


Insert into ATEST1 (ID,TDATE,AMOUNT,ORD_NO) values (1,to_date('04-APR-16','DD-MON-RR'),null,300);
Insert into ATEST1 (ID,TDATE,AMOUNT,ORD_NO) values (2,to_date('04-APR-16','DD-MON-RR'),null,300);
Insert into ATEST1 (ID,TDATE,AMOUNT,ORD_NO) values (3,to_date('01-MAR-16','DD-MON-RR'),null,100);
Insert into ATEST1 (ID,TDATE,AMOUNT,ORD_NO) values (4,to_date('01-MAR-16','DD-MON-RR'),'100',200);

--------------------------------------------------------
CREATE ERROR LOG TABLE USING THE DBMS PACKAGE :
--------------------------------------------------------


EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('ATEST1', 'ERR_ATEST1');   -- ATEST1 source table and ERR_ATEST1 error log table


Error Logging Restrictions and Caveats
  • Oracle Database logs the following errors during DML operations:
  • Column values that are too large
  • Constraint violations (NOT NULL, unique, referential, and check constraints)
  • Errors raised during trigger execution
  • Errors resulting from type conversion between a column in a subquery and the corresponding column of the table
  • Partition mapping errors

Certain MERGE operation errors (ORA-30926: Unable to get a stable set of rows for MERGE operation.)

--------------------------------------------------------
-- This will generate some insert errors
--------------------------------------------------------


INSERT INTO ATEST1
  SELECT ID+3,TDATE,AMOUNT,ORD_NO
  FROM ATEST1
  WHERE id > 1
  LOG ERRORS INTO ERR_ATEST1 ('daily_load') REJECT LIMIT 9;

--- daily_load is TAG, REJECT LIMT will set the max errs before terminating insert statement.


Note:

If the statement exceeds the reject limit and rolls back, the error logging table retains the log entries recorded so far.

--------------------------------------------------------
-- This will generate some update errors
--------------------------------------------------------


update ATEST1 set ID=3 where ID>5 LOG ERRORS INTO ERR_ATEST1 ('daily_load') REJECT LIMIT 9;

--- daily_load is TAG, REJECT LIMT will set the max errs before terminating insert statement.


--------------------------------------------------------
LETS CHECK THE ERROR MESSAGES RECORDED:
--------------------------------------------------------


select * from ERR_ATEST1;
Categories: DBA Blogs

Using Index Hints in oracle

Wed, 2016-06-08 09:59

Hints : Hints are used to give specific information that we know about our data and application, to Oracle. This further improves the performance of our system. There can be instances where the default optimizer may not be efficient for certain SQL statements. We can specify HINTS with the SQL statements, to improve the efficiency of those SQL statements. Hints should only be used as a last-resort if statistics were gathered and the query is still following a sub-optimal execution plan.

Example of the correct syntax for an index hint:

select /*+ index(TEST_IDX IDX_OS_USR) */ * from TEST_IDX;







If we alias the table (A in below case), you must use the alias in the index hint:

select /*+ index(A IDX_OS_USR) */ * from TEST_IDX A;

Note :

Oracle decides to use weather to use this hint or not, of oracle finds that it has faster execution plan without using hint it ignores it. You might think that an index may be helpfull and provide it as hint but oracle may still ignore it. In below case you can see hint being ignored.






Categories: DBA Blogs

CREATE AN INVISIBLE INDEX ON A TABLE

Tue, 2016-06-07 12:50
INVISIBLE INDEX:

Oracle 11g gives us ability to create indexes that can be marked as invisible. Invisible indexes are maintained like any other index, but they are ignored by the optimizer unless the OPTIMIZER_USE_INVISIBLE_INDEXES parameter is set to TRUE at the instance or session level

CREATE AN INVISIBLE INDEX:

CREATE INDEX INV_IDX_OS_USR ON TEST_IDX (ID) INVISIBLE;

lets check the newly created index :

SQL> select OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,VISIBILITY from all_indexes where index_name='INV_IDX_OS_USR';

OWNER         INDEX_NAME            TABLE_OWNER         TABLE_NAME      VISIBILITY       
--------      -----------                                    ----------               -----------                  --------
ATOORPU       INV_IDX_OS_USR       ATOORPU            TEST_IDX               INVISIBLE 


USER CAN'T MAKE USE OF INVISIBLE INDEX UNTIL HE MAKES IT VISIBLE IN THAT SESSION LETS SEE IF WE CAN USE INVISIBLE INDEX WITH OUT ENABLING IT IN OPTIMIZER:

select /*index (TEST_IDX INV_IDX_OS_USR)*/ * from TEST_IDX where ID=284;




MAKING AN INDEX VISIBLE IN CURRENT SESSION:

ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;
select /*index (TEST_IDX INV_IDX_OS_USR)*/ * from TEST_IDX where ID=284;







MAKING AN INDEX INVISIBLE IN CURRENT SESSION:

ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=FALSE;

select /*index (TEST_IDX INV_IDX_OS_USR)*/ * from TEST_IDX where ID=284;

-->> you will not have to provide any hints to use index. I have provided hint just to make sure  oracle uses it.

select  * from TEST_IDX where ID=284;        -->> Same as above



MAKING AN INDEX INVISIBLE OR VISIBLE:


Indexes can be created as invisible by using the INVISIBLE keyword at the end, and their visibility can be managed using the ALTER INDEX command

TO MAKE AN EXISTING INDEX INVISIBLE USE BELOW SYNTAX: 

ALTER INDEX index_name INVISIBLE;

TO MAKE AN EXISTING INDEX VISIBLE USE BELOW SYNTAX: 


ALTER INDEX index_name VISIBLE;


Categories: DBA Blogs

CREATE AN INVISIBLE INDEX ON A TABLE

Tue, 2016-06-07 12:50
INVISIBLE INDEX:

Oracle 11g gives us ability to create indexes that can be marked as invisible. Invisible indexes are maintained like any other index, but they are ignored by the optimizer unless the OPTIMIZER_USE_INVISIBLE_INDEXES parameter is set to TRUE at the instance or session level

CREATE AN INVISIBLE INDEX:
 
CREATE INDEX INV_IDX_OS_USR ON TEST_IDX (ID) INVISIBLE;

lets check the newly created index :

SQL> select OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,VISIBILITY from all_indexes where index_name='INV_IDX_OS_USR';

OWNER         INDEX_NAME            TABLE_OWNER         TABLE_NAME      VISIBILITY       
--------      -----------                                    ----------               -----------                  --------
ATOORPU       INV_IDX_OS_USR       ATOORPU            TEST_IDX               INVISIBLE   


USER CAN'T MAKE USE OF INVISIBLE INDEX UNTIL HE MAKES IT VISIBLE IN THAT SESSION LETS SEE IF WE CAN USE INVISIBLE INDEX WITH OUT ENABLING IT IN OPTIMIZER:

select /*index (TEST_IDX INV_IDX_OS_USR)*/ * from TEST_IDX where ID=284;




MAKING AN INDEX VISIBLE IN CURRENT SESSION:

ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;
select /*index (TEST_IDX INV_IDX_OS_USR)*/ * from TEST_IDX where ID=284;






 
MAKING AN INDEX INVISIBLE IN CURRENT SESSION:
 
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=FALSE;

select /*index (TEST_IDX INV_IDX_OS_USR)*/ * from TEST_IDX where ID=284; 

-->> you will not have to provide any hints to use index. I have provided hint just to make sure  oracle uses it.

select  * from TEST_IDX where ID=284;        -->> Same as above

 

MAKING AN INDEX INVISIBLE OR VISIBLE:


Indexes can be created as invisible by using the INVISIBLE keyword at the end, and their visibility can be managed using the ALTER INDEX command

TO MAKE AN EXISTING INDEX INVISIBLE USE BELOW SYNTAX: 

ALTER INDEX index_name INVISIBLE;

TO MAKE AN EXISTING INDEX VISIBLE USE BELOW SYNTAX: 


ALTER INDEX index_name VISIBLE;


Categories: DBA Blogs

Steps to quickly rebuild of existing standby database

Tue, 2016-05-24 13:05
 Steps to quickly rebuild of existing standby database:There are situations where you will have to rebuild your existing standby database as a result of  various situations like primary db was restored from backup with open reset logs.
1. Disable log shipping to standby database (that you want to rebuild "alter system set log_archive_dest_state_2=defer").

2. Take full bakup from PRIMARY DB.

3. Take standby controlfile backup.

4. Copy backup and standby control file to standby server.

5. Drop datalafiles and controlfiles on standby Database.

6. Copy new standby control files to all controlfile locations.

7. Mount standby Database

8. Restore standby database.

8.  Enable log shipping to standby database(alter system set log_archive_dest_state_2=enable).

9. Recover managed standby database (on standby).
Categories: DBA Blogs

Restore database schema from full expdp backup

Thu, 2016-05-19 16:01

Import schema from full db expdp backup:

In some situations you might want to restore a single schema from entire EXPDP backup. In this example I want to explain how to import a single schema from full DB expdp backup.





Lets backup the full database using the EXPDP:

F:\>expdp atoorpu directory=dpump dumpfile=fulldb_%U.dmp logfile=fulldb.log full=y compression=all parallel=8

Export: Release 11.2.0.4.0 - Production on Tue May 17 14:55:21 2016

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

UDI-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 7 days

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ATOORPU"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
Starting "ATOORPU"."SYS_EXPORT_SCHEMA_01":  atoorpu/******** directory=dpump dumpfile=fulldb_%U.dmp logfile=fulldb.log full=y compression=all parallel=8

.
.
.
.
.
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
. . exported "SCOTT"."DEPT"                             4.976 KB       4 rows
. . exported "SCOTT"."EMP"                              5.625 KB      14 rows
. . exported "SCOTT"."SALGRADE"                         4.890 KB       5 rows
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
.
.
.
.
.
Master table "ATOORPU"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ATOORPU.SYS_EXPORT_FULL_01 is:
  C:\DPUMP\FULLDB_01.DMP
  C:\DPUMP\FULLDB_02.DMP
  C:\DPUMP\FULLDB_03.DMP
  C:\DPUMP\FULLDB_04.DMP
  C:\DPUMP\FULLDB_05.DMP
  C:\DPUMP\FULLDB_06.DMP
  C:\DPUMP\FULLDB_07.DMP
  C:\DPUMP\FULLDB_08.DMP
Job "ATOORPU"."SYS_EXPORT_FULL_01" successfully completed at Tue May 17 14:27:13 2016 elapsed 0 00:05:36

Restore schema into a temp SCOTT1 schema:


Now that I have completed my full DB backup. I want to restore just SCOTT schema from full backup into a temp schema called SCOTT1. For this I will need to pass two parameters
1. schemas = Schema name you want to import
2. remap_schema= This is to remap that schema to a temp SOCTT1 schema


F:\>impdp atoorpu directory=dpump dumpfile=fulldb_%U.dmp logfile=fulldb_imp.log schemas=SCOTT remap_schema=SCOTT:SCOTT1

Import: Release 11.2.0.4.0 - Production on Tue May 17 14:57:21 2016

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

UDI-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 7 days

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ATOORPU"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "ATOORPU"."SYS_IMPORT_SCHEMA_01":  atoorpu/******** directory=dpump dumpfile=fulldb_%U.dmp logfile=fulldb_imp.log schemas=SCOTT remap_schema=SCOTT:SCOTT1
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "SCOTT1"."DEPT"                             4.976 KB       4 rows
. . imported "SCOTT1"."EMP"                              5.625 KB      14 rows
. . imported "SCOTT1"."SALGRADE"                         4.890 KB       5 rows
. . imported "SCOTT1"."BONUS"                                0 KB       0 rows
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Job "ATOORPU"."SYS_IMPORT_SCHEMA_01" successfully completed at Tue May 17 14:57:34 2016 elapsed 0 00:00:08


Restore SCOTT into SCOTT schema and replace exiting tables :

If you want to restore it with same schema SCOTT and replace existing tables use this


impdp atoorpu directory=dpump dumpfile=fulldb_%U.dmp logfile=fulldb_imp.log schemas=SCOTT ABLE_EXISTS_ACTION=REPLACE


If you want to restore it with same schema SCOTT but append (add) rows to existing tables use this
impdp atoorpu directory=dpump dumpfile=fulldb_%U.dmp logfile=fulldb_imp.log schemas=SCOTT ABLE_EXISTS_ACTION=APPPEND
Categories: DBA Blogs

restore single table from expdp full backup

Thu, 2016-05-19 13:24
Lets take a full Backup:


F:\>expdp atoorpu directory=dpump dumpfile=fulldb_%U.dmp logfile=fulldb.log full=y compression=all parallel=8


Export: Release 11.2.0.4.0 - Production on Tue May 17 14:55:21 2016

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

Password:

UDI-28002: operation generated ORACLE error 28002

ORA-28002: the password will expire within 7 days

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "ATOORPU"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

Starting "ATOORPU"."SYS_EXPORT_SCHEMA_01":  atoorpu/******** directory=dpump dumpfile=fulldb_%U.dmp logfile=fulldb.log full=y compression=all parallel=8


.

.

.

.

.

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX

. . exported "SCOTT1"."DEPT"                             4.976 KB       4 rows

. . exported "SCOTT1"."EMP"                              5.625 KB      14 rows

. . exported "SCOTT1"."SALGRADE"                         4.890 KB       5 rows

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX

.

.

.

.

.

Master table "ATOORPU"."SYS_EXPORT_FULL_01" successfully loaded/unloaded

******************************************************************************

Dump file set for ATOORPU.SYS_EXPORT_FULL_01 is:

  C:\DPUMP\FULLDB_01.DMP

  C:\DPUMP\FULLDB_02.DMP

  C:\DPUMP\FULLDB_03.DMP

  C:\DPUMP\FULLDB_04.DMP

  C:\DPUMP\FULLDB_05.DMP

  C:\DPUMP\FULLDB_06.DMP

  C:\DPUMP\FULLDB_07.DMP

  C:\DPUMP\FULLDB_08.DMP

Job "ATOORPU"."SYS_EXPORT_FULL_01" successfully completed at Tue May 17 14:27:13 2016 elapsed 0 00:05:36


****************************************************************************************************************

FROM THIS FULL BACKUP I WOULD LIKE TO RESTORE A TABLE FROM SCHEMA: SCOTT & TABLE: BONUS FULL BACKUP INTO SCHEMA.

****************************************************************************************************************


F:\>impdp atoorpu directory=dpump dumpfile=fulldb_%U.dmp logfile=fulldb_imp.log tables=SCOTT.BONUS


Import: Release 11.2.0.4.0 - Production on Tue May 17 16:04:51 2016


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

Password:


UDI-28002: operation generated ORACLE error 28002

ORA-28002: the password will expire within 7 days


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "ATOORPU"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "ATOORPU"."SYS_IMPORT_TABLE_01":  atoorpu/******** directory=dpump dumpfile=fulldb_%U.dmp logfile=fulldb_imp.log tables=SCOTT.BONUS

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

. . imported "SCOTT"."BONUS"                                 0 KB       0 rows

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS

Job "ATOORPU"."SYS_IMPORT_TABLE_01" successfully completed at Tue May 17 16:05:05 2016 elapsed 0 00:00:08


****************************************************************************************************************

FROM THIS FULL BACKUP I WOULD LIKE TO RESTORE A TABLE FROM FULL BACKUP AND RESTORE IT FROM SCHEMA : SCOTT INTO ANOTHER SCHEMA : SCOTT1.

****************************************************************************************************************


F:\>impdp atoorpu directory=dpump dumpfile=fulldb_%U.dmp logfile=fulldb_imp.log tables=SCOTT.BONUS REMAP_SCHEMA=scott:scott1


Import: Release 11.2.0.4.0 - Production on Tue May 17 16:20:31 2016


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

Password:


UDI-28002: operation generated ORACLE error 28002

ORA-28002: the password will expire within 7 days


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "ATOORPU"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "ATOORPU"."SYS_IMPORT_TABLE_01":  atoorpu/******** directory=dpump dumpfile=fulldb_%U.dmp logfile=fulldb_imp.log tables=SCOTT.BONUS REMAP_SCHEMA=scott:scot

1

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

. . imported "SCOTT1"."BONUS"                                0 KB       0 rows

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS

Job "ATOORPU"."SYS_IMPORT_TABLE_01" successfully completed at Tue May 17 16:20:45 2016 elapsed 0 00:00:08
Categories: DBA Blogs

select from table with no direct relation or foriegn keys

Wed, 2016-05-11 12:50
SELECT
  E.EMPNO,
  E.ENAME,
  E.JOB,
  D.DEPTNO,
  D.LOC,
  E.SAL
FROM
  scott.emp E
LEFT JOIN SCOTT.DEPT D
ON
  E.DEPTNO=D.DEPTNO;





SELECT
  E.EMPNO,
  E.ENAME,
  E.JOB,
  D.DEPTNO,
  D.LOC,
  E.SAL,
  (
    SELECT      grade
    FROM
      SCOTT.SALGRADE S
    WHERE
      E.SAL BETWEEN S.LOSAL AND S.HISAL
  ) AS SALGRADE
FROM
  scott.emp E
LEFT JOIN SCOTT.DEPT D
ON
  E.DEPTNO=D.DEPTNO;










Categories: DBA Blogs

run sql from windows cmd prompt CLI

Wed, 2016-05-11 09:36

Method 1:

set ORACLE_HOME=D:\app\oracle\product\11.2.0\db_1
set ORACLE_SID=ORCL

sqlplus -s "USERNAME/PASSWORD" @C:\Shell\Drop_objs.sql

sqlplus -s "USERNAME/PASSWORD" @C:\Shell\Change_pass.sql



Method 2:

set ORACLE_HOME=D:\app\oracle\product\11.2.0\db_1
set ORACLE_SID=ORCL
(ECHO select username from dba_users where username in ('SCOTT');
ECHO exit;) | sqlplus -s "USER/PASS" > C:\Shell\test_out.txt



Categories: DBA Blogs

USING SELECT 'X' in query/subqueries.

Tue, 2016-04-19 14:26
 
USING SELECT 'X' in query/sub-queries.



--------------------------------------------------------
--  DDL for Table TAB1
--------------------------------------------------------

  CREATE TABLE "ATEST"."TAB1"
   (    "ID" NUMBER,
    "NAME" VARCHAR2(20 BYTE)
   ) ;

Insert into ATEST.TAB1 (ID,NAME) values (1,'AAA');
Insert into ATEST.TAB1 (ID,NAME) values (2,'BBB');
Insert into ATEST.TAB1 (ID,NAME) values (3,'EEE');
Insert into ATEST.TAB1 (ID,NAME) values (4,'FFF');


--------------------------------------------------------
--  DDL for Table TAB2
--------------------------------------------------------

  CREATE TABLE "ATEST"."TAB2"
   (    "ID" NUMBER,
    "NAME" VARCHAR2(20 BYTE)
   ) ;

Insert into ATEST.TAB2 (ID,NAME) values (1,'CCC');
Insert into ATEST.TAB2 (ID,NAME) values (2,'DDD');


Get records that exits in TAB1 and not in TAB2 using select 'X' :


select * from TAB1 f where not exists (select 'X' from TAB2 where id=f.id);
ID    NAME
--    ---- 
4    FFF
3    EEE

IN the above query we get output of all the records from TAB1 that doesnt match with TAB2 ID's.
Hence we do not get the records with ID's 1 & 2 as they only exits in TAB1.
This is just like using "select * from TAB1 f where not exists (select ID from TAB2 where id=f.id);"


Get records that exits in TAB1 and in TAB2 using select 'X' :


select * from TAB1 f where exists (select 'X' from TAB2 where id=f.id);

ID    NAME
--    ---- 
1    AAA
2    BBB

IN the above query we get output of all the records from TAB1 that exist with same ID in TAB2 .
Hence we get only records with ID 1 & 2 as they exists in both TABLES.
This is just like using "select * from TAB1 f where exists (select ID from TAB2 where id=f.id);"
Categories: DBA Blogs

sql for first day of month and last day of month

Fri, 2016-04-01 20:00

select SYSDATE ,
last_day(sysdate) as LAST_DATE_CURR_MNTH,
ADD_MONTHS(last_day(sysdate),-1) as PREVIOUS_MON_LAST_DATE,
last_day(sysdate)+1 as NEXT_MON_FIRST_DATE,
ADD_MONTHS(last_day(sysdate),+1) as NEXT_MON_LAST_DATE,
ADD_MONTHS(last_day(sysdate),+5) as LAST_DATE_OF_5TH_MON,
ADD_MONTHS(last_day(sysdate),+5) +1 as FIRST_DATE_IN_6TH_MON_AFTR_NOW  
from dual;

"SYSDATE"    "LAST_DATE_CURR_MNTH"    "PREVIOUS_MON_LAST_DATE"    "NEXT_MON_FIRST_DATE"    "NEXT_MON_LAST_DATE"    "LAST_DATE_OF_5TH_MON"   
-----------  ---------------------   ------------------------    ---------------------   --------------------    -----------------------
"FIRST_DATE_IN_6TH_MON_AFTR_NOW"
-----------------------

01-APR-16        30-APR-16                31-MAR-16                    01-MAY-16                31-MAY-16            30-SEP-16   
-----------------------
01-OCT-16
Categories: DBA Blogs

CHANGE STANDBY DATABASE PROTECTION MODE

Fri, 2016-04-01 09:00
SQL> select protection_mode from v$database;

PROTECTION_MODE
--------------------
MAXIMUM PERFORMANCE

SQL> show parameter log_archive_dest_2

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2             string     SERVICE=ORCLSTB1 NOAFFIRM ASYN
                         C VALID_FOR=(ONLINE_LOGFILES,P
                         RIMARY_ROLE) DB_UNIQUE_NAME=OR
                         CLSTB1
log_archive_dest_20             string
log_archive_dest_21             string
log_archive_dest_22             string
log_archive_dest_23             string
log_archive_dest_24             string
log_archive_dest_25             string
log_archive_dest_26             string

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_27             string
log_archive_dest_28             string
log_archive_dest_29             string
SQL> show parameter db_unique_name

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                 string     ORCL
SQL> show parameter log_archive_config

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
log_archive_config             string     dg_config=(ORCL,ORCLSTB1,ORCLS
                         TB2)
SQL> alter system set log_archive_dest_2='SERVICE=ORCLSTB1 NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLSTB1';

System altered.

SQL> show parameter log_archive_dest_2

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2             string     SERVICE=ORCLSTB1 NOAFFIRM ASYN
                         C VALID_FOR=(ONLINE_LOGFILES,P
                         RIMARY_ROLE) DB_UNIQUE_NAME=OR
                         CLSTB1
log_archive_dest_20             string
log_archive_dest_21             string
log_archive_dest_22             string
log_archive_dest_23             string
log_archive_dest_24             string
log_archive_dest_25             string
log_archive_dest_26             string

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_27             string
log_archive_dest_28             string
log_archive_dest_29             string

SQL> alter system set log_archive_dest_2='SERVICE=ORCLSTB1 NOAFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLSTB1';

System altered.


SQL> alter database set standby database to maximize availability;

Database altered.

NOTE: You don’t need to shutdown your instance, when you are changing  protection mode from MAXIMUM PERFORMANCE TO MAXIMUM AVAILABILITY.But you need to if you are going to MAXIMUM PROTECTION.

SQL> alter system switch logfile;

System altered.

SQL> select protection_mode from v$database;

PROTECTION_MODE
--------------------
MAXIMUM AVAILABILITY

SQL> archive log list;
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           /u01/app/oracle/oraarch/
Oldest online log sequence     239
Next log sequence to archive   241
Current log sequence           241
SQL> select group#,bytes/1024/1024 from v$standby_log;

    GROUP# BYTES/1024/1024
---------- ---------------
     4        52
     5        52
     6        52
     7        52


SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
     1          240

SQL> select protection_mode from v$database;

PROTECTION_MODE
--------------------
MAXIMUM AVAILABILITY



SQL> alter system switch logfile;

System altered.

Categories: DBA Blogs

Create a PrimaryKey on existing table without unique values

Mon, 2016-03-28 00:25
lets assume that there was a table ABC that was already existing in the database and you want to add an additional column with unique primary key values.


sql to create table ABC :

  CREATE TABLE "ABC"
   (    "USERNAME" VARCHAR2(30 BYTE) NOT NULL ENABLE,
    "USER_ID" NUMBER NOT NULL ENABLE,
    "CREATED" DATE NOT NULL ENABLE )
   TABLESPACE "USERS" ;

Now we  can add an additional column ID which will be populated with all unique values for PrimaryKey.

alter table abc add(ID NUMBER);

We will now create a sequence and get the values from the seq and insert them into table ABC new ID column:

CREATE SEQUENCE SEQ_ID
START WITH 1
INCREMENT BY 1
MAXVALUE 999999
MINVALUE 1
NOCYCLE;

Now insert the unique values into the database with below sql:
UPDATE abc SET ID = SEQ_ID.NEXTVAL;


now you can add unique constraint (or) add primary key constraint to table,so that it wont take any more duplicate value into the table.

alter table abc add primarykey (ID);
Categories: DBA Blogs

ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET

Fri, 2016-03-25 00:08
In this scenario I am trying to increase the value of parameter memory_max_target. My initial memory_max_target = 804 I want to increase it to 900

SQL> show parameter sga

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga     boolean FALSE
pre_page_sga     boolean FALSE
sga_max_size     big integer 804M
sga_target     big integer 0

SQL> show parameter max_target

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_max_target     big integer 804M

SQL> show parameter memory

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address     integer 0
memory_max_target     big integer 804M
memory_target     big integer 804M
shared_memory_address     integer 0

SQL> alter system set memory_max_target=900 scope=spfile;

System altered.

SQL> show parameter memory;

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address     integer 0
memory_max_target     big integer 804M
memory_target     big integer 804M
shared_memory_address     integer 0

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup;
ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET
SQL> startup mount;
ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET
SQL> startup nomount;
ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET

Since we can't login into DB to check the value  that was set. Lets create pfile and check the actual value.

SQL> create pfile from spfile;

File created.

[oracle@Linux01 ~]$ cd $ORACLE_HOME/dbs

[oracle@Linux01 dbs]$ ls -ll

[oracle@Linux01 dbs]$ vi initDB11G.ora


Haha .. here is the problem in my case.




*********************************************************************************
In my case the problem is that, I didn't mention the MEMORY_MAX_TARGET in MB
Changing the value to MB did the trick
*********************************************************************************




[oracle@Linux01 dbs]$ sqlplus  /"AS sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 24 23:46:18 2016

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

Connected to an idle instance.

Below reboot not needed but since I want to use spfile. I did it

SQL> startup pfile='$ORACLE_HOME/dbs/initDB11G.ora';
ORACLE instance started.

Total System Global Area  939495424 bytes
Fixed Size    2218952 bytes
Variable Size  675284024 bytes
Database Buffers  255852544 bytes
Redo Buffers    6139904 bytes
Database mounted.
Database opened.
SQL> create spfile from pfile;

File created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area  939495424 bytes
Fixed Size    2218952 bytes
Variable Size  675284024 bytes
Database Buffers  255852544 bytes
Redo Buffers    6139904 bytes
Database mounted.
Database opened.
SQL>


SQL> show parameter memory;

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address     integer 0
memory_max_target     big integer 900M
memory_target     big integer 800M
shared_memory_address     integer 0
Categories: DBA Blogs

SQLSERVER QUERIES - SQLSERVER2015

Wed, 2016-03-23 16:02
SELECT TOP 1000 [FNAME]
      ,[LNAME]
      ,[ID]
  FROM [TESTDB].[dbo].[USERS]

FNAME    LNAME    ID
Arvind    Reddy    1
Ravi    Reddy    2
Tom        Shawn    3


SELECT TOP 1000 [ORDER_ID]
      ,[USER_ID]
      ,[ORDER_INFO]
      ,[ORDER_AMT]
  FROM [TESTDB2].[dbo].[USER_ORDERS]
 
ORDER_ID    USER_ID    ORDER_INFO    ORDER_AMT
9001            1        BOOKS        10
9002            2        SHOES        20
 

SELECT A.[ORDER_ID]
      ,A.[USER_ID]
      ,A.[ORDER_INFO]
      ,A.[ORDER_AMT],B.ID,A.USER_ID
  FROM [TESTDB2].[dbo].[USER_ORDERS] A ,[TESTDB].[dbo].[USERS] B where A.USER_ID=B.ID ;
 
ORDER_ID    USER_ID    ORDER_INFO    ORDER_AMT    ID    USER_ID
9001            1        BOOKS        10        1        1
9002            2        SHOES        20        2        2


Address    Phone    City    User_id    ID
1234 test    2145524585    Hyd    1    1
52426 test    5246853652    Hyd    2    2
582 test st    5286943568    Bglr    3    3
768 TEST RD    56799976887    OMAHA    4    4
768 TEST RD    56799976887    OMAHA    5    5
Categories: DBA Blogs

Pages