Home
Search results “Select table locks oracle”
Oracle Locks Explained Part 1
 
12:02
Oracle Locks explained. How to Kill a User session in oracle database- Neway IT Solutions
Views: 1930 NewayITSolutions LLC
Understanding Locking in SQL Server - SQL Server Tutorial
 
25:54
In this video you will understand what is locking in SQL Server? video gives brief overview Following: What is Resource types in locking of SQL Server? What is Lock Modes in SQL Server? It gives live view of looking at different type of lock modes as well as the resources types where the locks are placed. It describes in greater detail about RID, Key, Page, Extent, Table and DB Resource types in SQL Server Locking concept. It also explains Shared Locks, Update Lock, Exclusive Locks, Intent Lock, Schema Lock and Bulk Update locks in SQL Server. Blog post link for the video with script http://sqlage.blogspot.com/2015/04/understanding-locking-in-sql-server-sql.html Complete Step by Step List for DBA Tutorial Videos: http://sqlage.blogspot.com/search/label/SQL%20Sever%202014%20DBA%20Video%20Tutorial
Views: 53886 TechBrothersIT
Lock and Unlock table statistics in Oracle Database
 
05:30
Lock and Unlock table statistics in Oracle Database
PL/SQL: Locks
 
08:43
In this tutorial, you'll learn the types of locks & how locks occurs while executing a query.
Views: 7740 radhikaravikumar
Difference between blocking and deadlocking
 
06:52
deadlock vs blocking sql server In this video we will discuss the difference between blocking and deadlocking. This is one of the common SQL Server interview question. Let us understand the difference with an example. SQL Script to create the tables and populate them with test data Create table TableA ( Id int identity primary key, Name nvarchar(50) ) Go Insert into TableA values ('Mark') Go Create table TableB ( Id int identity primary key, Name nvarchar(50) ) Go Insert into TableB values ('Mary') Go Blocking : Occurs if a transaction tries to acquire an incompatible lock on a resource that another transaction has already locked. The blocked transaction remains blocked until the blocking transaction releases the lock. Example : Open 2 instances of SQL Server Management studio. From the first window execute Transaction 1 code and from the second window execute Transaction 2 code. Notice that Transaction 2 is blocked by Transaction 1. Transaction 2 is allowed to move forward only when Transaction 1 completes. --Transaction 1 Begin Tran Update TableA set Name='Mark Transaction 1' where Id = 1 Waitfor Delay '00:00:10' Commit Transaction --Transaction 2 Begin Tran Update TableA set Name='Mark Transaction 2' where Id = 1 Commit Transaction Deadlock : Occurs when two or more transactions have a resource locked, and each transaction requests a lock on the resource that another transaction has already locked. Neither of the transactions here can move forward, as each one is waiting for the other to release the lock. So in this case, SQL Server intervenes and ends the deadlock by cancelling one of the transactions, so the other transaction can move forward. Example : Open 2 instances of SQL Server Management studio. From the first window execute Transaction 1 code and from the second window execute Transaction 2 code. Notice that there is a deadlock between Transaction 1 and Transaction 2. -- Transaction 1 Begin Tran Update TableA Set Name = 'Mark Transaction 1' where Id = 1 -- From Transaction 2 window execute the first update statement Update TableB Set Name = 'Mary Transaction 1' where Id = 1 -- From Transaction 2 window execute the second update statement Commit Transaction -- Transaction 2 Begin Tran Update TableB Set Name = 'Mark Transaction 2' where Id = 1 -- From Transaction 1 window execute the second update statement Update TableA Set Name = 'Mary Transaction 2' where Id = 1 -- After a few seconds notice that one of the transactions complete -- successfully while the other transaction is made the deadlock victim Commit Transaction Link for all dot net and sql server video tutorial playlists https://www.youtube.com/user/kudvenkat/playlists?sort=dd&view=1 Link for slides, code samples and text version of the video http://csharp-video-tutorials.blogspot.com/2015/09/difference-between-blocking-and.html
Views: 67708 kudvenkat
Table Locking ( MySQL ) - Tutorial
 
05:26
Table locking is an existing query in mysql, where this query is used to lock the table at the time the user or admin wants to perform INSERT, UPDATE, or DELETE. This query is run when a database resides on the server and there are few users who can access the database.So in order to avoid conflicting data during INSERT, UPDATE, DELETE then use Table Locking. - Introduction : 0:00 - Coding : 0:52
Views: 6297 Muhammad Ikram
Oracle - The SELECT clause - Beginner
 
10:04
Oracle - The SELECT clause - Beginner
Views: 523 Chris Ostrowski
02 Shared Lock & Exclusive Lock In oracle database table lock
 
11:32
Purpose Use the LOCK TABLE statement to lock one or more tables, table partitions, or table subpartitions in a specified mode. This lock manually overrides automatic locking and permits or denies access to a table or view by other users for the duration of your operation. Some forms of locks can be placed on the same table at the same time. Other locks allow only one lock for a table. A locked table remains locked until you either commit your transaction or roll it back, either entirely or to a savepoint before you locked the table. A lock never prevents other users from querying the table. A query never places a lock on a table. Readers never block writers and writers never block readers. See Also: Oracle Database Concepts for a complete description of the interaction of lock modes COMMIT ROLLBACK SAVEPOINT Prerequisites The table or view must be in your own schema or you must have the LOCK ANY TABLE system privilege, or you must have any object privilege on the table or view. ROW SHARE ROW SHARE permits concurrent access to the locked table but prohibits users from locking the entire table for exclusive access. ROW SHARE is synonymous with SHARE UPDATE, which is included for compatibility with earlier versions of Oracle Database. ROW EXCLUSIVE ROW EXCLUSIVE is the same as ROW SHARE, but it also prohibits locking in SHARE mode. ROW EXCLUSIVE locks are automatically obtained when updating, inserting, or deleting. SHARE UPDATE See ROW SHARE. SHARE SHARE permits concurrent queries but prohibits updates to the locked table. SHARE ROW EXCLUSIVE SHARE ROW EXCLUSIVE is used to look at a whole table and to allow others to look at rows in the table but to prohibit others from locking the table in SHARE mode or from updating rows. EXCLUSIVE EXCLUSIVE permits queries on the locked table but prohibits any other activity on it. NOWAIT Specify NOWAIT if you want the database to return control to you immediately if the specified table, partition, or table subpartition is already locked by another user. In this case, the database returns a message indicating that the table, partition, or subpartition is already locked by another user. WAIT Use the WAIT clause to indicate that the LOCK TABLE statement should wait up to the specified number of seconds to acquire a DML lock. There is no limit on the value of integer. If you specify neither NOWAIT nor WAIT, then the database waits indefinitely until the table is available, locks it, and returns control to you. When the database is executing DDL statements concurrently with DML statements, a timeout or deadlock can sometimes result. The database detects such timeouts and deadlocks and returns an error.
Views: 745 OnLinE ReSoUrCe
Oracle SELECT FOR UPDATE /عربي
 
07:24
you can visit my website maxvlearn.com
Views: 965 khaled alkhudari
Oracle Midlands #13: All About Table Locks - Franck Pachot
 
54:41
Timestamps: 0:45 - Basics 5:03 - TX lock demo 9:30 - TM lock info 21:04 - Foreign key index demo 28:48 - 10g, 11g, 12c locking differences 41:50 - New online operations in 12c 46:26 - Event 10704 trace 50:34 - Deadlock demo 53:42 - Q&A Franck discusses different types of locking scenarios to consider with your systems. The slides are available at: Google: https://drive.google.com/folderview?id=0B0DLaAfeW6uKZmJ3TXE0WGtLYlE Dropbox: https://www.dropbox.com/sh/9koxoemak8ts7j6/AADozVVYLoK_FdDZbQGC6TYta This event was sponsored by Red Stack Technology (http://redstacktechnology.com/). See more events at http://OracleMidlands.com/ Copyright Disclaimer Under Section 107 of the Copyright Act 1976, allowance is made for fair use for purposes such as criticism, comment, news reporting, teaching, scholarship, and research. Fair use is a use permitted by copyright statute that might otherwise be infringing. Non-profit, educational or personal use tips the balance in favor of fair use. "Fair Dealing" under UK Copyright, Designs and Patents Act 1988.
Views: 687 Oracle Midlands
SQLPLUS: LineSize & PageSize
 
03:49
In this tutorial, you'll learn how to set linesize and pagesize . PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural extension for SQL and the Oracle relational database. PL/SQL is available in Oracle Database (since version 7), TimesTen in-memory database (since version 11.2.1), and IBM DB2 (since version 9.7).[1] Oracle Corporation usually extends PL/SQL functionality with each successive release of the Oracle Database. PL/SQL includes procedural language elements such as conditions and loops. It allows declaration of constants and variables, procedures and functions, types and variables of those types, and triggers. It can handle exceptions (runtime errors). Arrays are supported involving the use of PL/SQL collections. Implementations from version 8 of Oracle Database onwards have included features associated with object-orientation. One can create PL/SQL units such as procedures, functions, packages, types, and triggers, which are stored in the database for reuse by applications that use any of the Oracle Database programmatic interfaces. PL/SQL works analogously to the embedded procedural languages associated with other relational databases. For example, Sybase ASE and Microsoft SQL Server have Transact-SQL, PostgreSQL has PL/pgSQL (which emulates PL/SQL to an extent), and IBM DB2 includes SQL Procedural Language,[2] which conforms to the ISO SQL’s SQL/PSM standard. The designers of PL/SQL modeled its syntax on that of Ada. Both Ada and PL/SQL have Pascal as a common ancestor, and so PL/SQL also resembles Pascal in several aspects. However, the structure of a PL/SQL package does not resemble the basic Object Pascal program structure as implemented by a Borland Delphi or Free Pascal unit. Programmers can define public and private global data-types, constants and static variables in a PL/SQL package.[3] PL/SQL also allows for the definition of classes and instantiating these as objects in PL/SQL code. This resembles usage in object-oriented programming languages like Object Pascal, C++ and Java. PL/SQL refers to a class as an "Abstract Data Type" (ADT) or "User Defined Type" (UDT), and defines it as an Oracle SQL data-type as opposed to a PL/SQL user-defined type, allowing its use in both the Oracle SQL Engine and the Oracle PL/SQL engine. The constructor and methods of an Abstract Data Type are written in PL/SQL. The resulting Abstract Data Type can operate as an object class in PL/SQL. Such objects can also persist as column values in Oracle database tables. PL/SQL is fundamentally distinct from Transact-SQL, despite superficial similarities. Porting code from one to the other usually involves non-trivial work, not only due to the differences in the feature sets of the two languages,[4] but also due to the very significant differences in the way Oracle and SQL Server deal with concurrency and locking. There are software tools available that claim to facilitate porting including Oracle Translation Scratch Editor,[5] CEITON MSSQL/Oracle Compiler [6] and SwisSQL.[7] The StepSqlite product is a PL/SQL compiler for the popular small database SQLite. PL/SQL Program Unit A PL/SQL program unit is one of the following: PL/SQL anonymous block, procedure, function, package specification, package body, trigger, type specification, type body, library. Program units are the PL/SQL source code that is compiled, developed and ultimately executed on the database. The basic unit of a PL/SQL source program is the block, which groups together related declarations and statements. A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords divide the block into a declarative part, an executable part, and an exception-handling part. The declaration section is optional and may be used to define and initialize constants and variables. If a variable is not initialized then it defaults to NULL value. The optional exception-handling part is used to handle run time errors. Only the executable part is required. A block can have a label. Package Packages are groups of conceptually linked functions, procedures, variables, PL/SQL table and record TYPE statements, constants, cursors etc. The use of packages promotes re-use of code. Packages are composed of the package specification and an optional package body. The specification is the interface to the application; it declares the types, variables, constants, exceptions, cursors, and subprograms available. The body fully defines cursors and subprograms, and so implements the specification. Two advantages of packages are: Modular approach, encapsulation/hiding of business logic, security, performance improvement, re-usability. They support object-oriented programming features like function overloading and encapsulation. Using package variables one can declare session level (scoped) variables, since variables declared in the package specification have a session scope.
Views: 15446 radhikaravikumar
Oracle Locks and Lock Trees
 
07:08
Rows Locks and sessions waiting in a "tree order" on Row Locks in Oracle
Views: 185 Hemant K Chitale
MSSQL - Understanding and Deciphering a Deadlock Graph XML
 
16:21
Best Most Useful Links for Understand a Deadlock (XML) in SQL Server 1) Compatible Locks Table - http://technet.microsoft.com/en-us/library/ms186396(v=sql.105).aspx 2) Understanding Deadlocks - https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/ In your example waitresource="KEY: 6:72057594090487808 (d900ed5a6cc6) 1) Database - retrieve the database involved in the deadlock with SELECT * FROM sys.databases WHERE database_id IN (6) 2) Table or Index - retrieve the table or index involved in the deadlock SELECT b.name AS TableName, c.name AS IndexName, c.type_desc AS IndexType, * FROM sys.partitions a INNER JOIN sys.objects b ON a.object_id = b.object_id INNER JOIN sys.indexes c ON a.object_id = c.object_id AND a.index_id = c.index_id WHERE partition_id IN ('72057594090487808') 3) Exact Row - retrieve the exact row or page, in your specific case the wait resource was a KEY, so you search the "column" %%lockres%% (yes the column name is actually %%lockres%%). If your table is not too out of date or if it is not a DELETE operation, then you will find the exact row from that hash, after you have determined which table that "partition id" or "hobt_id" is from then alter and run the below code (disclaimer - the hashes and page locations may have changed by the time you are doing the debugging, though unlikely with the key hashes) SELECT sys.fn_PhysLocFormatter(%%physloc%%) AS PageResource, %%lockres%% AS LockResource, * FROM InsertTableNameFromStep2Here WHERE %%lockres%% IN ('(d900ed5a6cc6)') Keep in mind that usually 2 resources are conflicting which caused the deadlock. However, it doesn't necessarily have to be both of the statements provided in the deadlock graph which is doing both of the locking. It is also possible that a statement prior, but within the same transaction (but not identified in the deadlock graph) locked 1 of the 2 resources. But 1 of the 2 statements in the deadlock is definitely involved in locking 1 of the 2 resources causing the deadlock at the time the deadlock was logged.
Views: 7281 CodeCowboyOrg
Update - Locks in SQL Server - Part 4
 
07:48
Click here to Subscribe to IT PORT Channel : https://www.youtube.com/channel/UCMjmoppveJ3mwspLKXYbVlg Update (U) locks prevent a common form of deadlock. In a repeatable read or serializable transaction, the transaction reads data, acquiring a shared (S) lock on the resource (page or row), and then modifies the data, which requires lock conversion to an exclusive (X) lock. If two transactions acquire shared-mode locks on a resource and then attempt to update data concurrently, one transaction attempts the lock conversion to an exclusive (X) lock. The shared-mode-to-exclusive lock conversion must wait because the exclusive lock for one transaction is not compatible with the shared-mode lock of the other transaction; a lock wait occurs. The second transaction attempts to acquire an exclusive (X) lock for its update. Because both transactions are converting to exclusive (X) locks, and they are each waiting for the other transaction to release its shared-mode lock, a deadlock occurs. To avoid this potential deadlock problem, update (U) locks are used. Only one transaction can obtain an update (U) lock to a resource at a time. If a transaction modifies a resource, the update (U) lock is converted to an exclusive (X) lock. Isolation Level - https://youtu.be/ESET4zuNLoM Script for Active_Locks Function --------------------------------------------------- Create Function Active_locks () returns table return select Top 10000000 case dtl.request_session_id when -2 then 'orphaned distributed transaction' when -3 then 'deferred recovery transaction' else dtl.request_session_id end as spid, db_name(dtl.resource_database_id) as databasename, so.name as lockedobjectname, dtl.resource_type as lockedresource, dtl.request_mode as locktype, es.login_name as loginname, es.host_name as hostname, case tst.is_user_transaction when 0 then 'system transaction' when 1 then 'user transaction' end as user_or_system_transaction, at.name as transactionname, dtl.request_status from sys.dm_tran_locks dtl join sys.partitions sp on sp.hobt_id = dtl.resource_associated_entity_id join sys.objects so on so.object_id = sp.object_id join sys.dm_exec_sessions es on es.session_id = dtl.request_session_id join sys.dm_tran_session_transactions tst on es.session_id = tst.session_id join sys.dm_tran_active_transactions at on tst.transaction_id = at.transaction_id join sys.dm_exec_connections ec on ec.session_id = es.session_id cross apply sys.dm_exec_sql_text(ec.most_recent_sql_handle) as st where resource_database_id = db_id() order by dtl.request_session_id
Views: 411 IT Port
Intent - Locks in SQL Server - Part 6
 
06:10
Click here to Subscribe to IT PORT Channel : https://www.youtube.com/channel/UCMjmoppveJ3mwspLKXYbVlg The Database Engine uses intent locks to protect placing a shared (S) lock or exclusive (X) lock on a resource lower in the lock hierarchy. Intent locks are named intent locks because they are acquired before a lock at the lower level, and therefore signal intent to place locks at a lower level. Intent locks serve two purposes: --------------------------------------------------- a) To prevent other transactions from modifying the higher-level resource in a way that would invalidate the lock at the lower level. b) To improve the efficiency of the Database Engine in detecting lock conflicts at the higher level of granularity. if a transaction has an exclusive lock on a row, SQL Server places an intent lock on the table. When another transaction requests a lock on a row in the table, SQL Server knows to check the rows to see if they have locks. If a table does not have intent lock, it can issue the requested lock without checking each row for a lock Isolation Level - https://youtu.be/ESET4zuNLoM Script for Active_Locks Function --------------------------------------------------- Create Function Active_locks () returns table return select Top 10000000 case dtl.request_session_id when -2 then 'orphaned distributed transaction' when -3 then 'deferred recovery transaction' else dtl.request_session_id end as spid, db_name(dtl.resource_database_id) as databasename, so.name as lockedobjectname, dtl.resource_type as lockedresource, dtl.request_mode as locktype, es.login_name as loginname, es.host_name as hostname, case tst.is_user_transaction when 0 then 'system transaction' when 1 then 'user transaction' end as user_or_system_transaction, at.name as transactionname, dtl.request_status from sys.dm_tran_locks dtl join sys.partitions sp on sp.hobt_id = dtl.resource_associated_entity_id join sys.objects so on so.object_id = sp.object_id join sys.dm_exec_sessions es on es.session_id = dtl.request_session_id join sys.dm_tran_session_transactions tst on es.session_id = tst.session_id join sys.dm_tran_active_transactions at on tst.transaction_id = at.transaction_id join sys.dm_exec_connections ec on ec.session_id = es.session_id cross apply sys.dm_exec_sql_text(ec.most_recent_sql_handle) as st where resource_database_id = db_id() order by dtl.request_session_id
Views: 595 IT Port
DML Processing in an Oracle Database -  DBArch Video 8
 
09:07
This video explains the steps involved in processing a DML statement in an Oracle Database Server. Our Upcoming Online Course Schedule is available in the url below https://docs.google.com/spreadsheets/d/1qKpKf32Zn_SSvbeDblv2UCjvtHIS1ad2_VXHh2m08yY/edit#gid=0 Reach us at [email protected]
Views: 47156 Ramkumar Swaminathan
Oracle Locks Part2  Killing a User Session
 
12:46
Oracle Locks Part 2- Killing a User session- Neway IT Solutions
SQL Server deadlock example
 
05:14
Text version of the video http://csharp-video-tutorials.blogspot.com/2015/08/sql-server-deadlock-example.html Slides http://csharp-video-tutorials.blogspot.com/2015/08/sql-server-deadlock-example_25.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists When can a deadlock occur In a database, a deadlock occurs when two or more processes have a resource locked, and each process requests a lock on the resource that another process has already locked. Neither of the transactions here can move forward, as each one is waiting for the other to release the lock. When deadlocks occur, SQL Server will choose one of processes as the deadlock victim and rollback that process, so the other process can move forward. The transaction that is chosen as the deadlock victim will produce the following error. Msg 1205, Level 13, State 51, Line 1 Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. SQL script to create the tables and populate them with test data Create table TableA ( Id int identity primary key, Name nvarchar(50) ) Go Insert into TableA values ('Mark') Go Create table TableB ( Id int identity primary key, Name nvarchar(50) ) Go Insert into TableB values ('Mary') Go The following 2 transactions will result in a dead lock. Open 2 instances of SQL Server Management studio. From the first window execute Transaction 1 code and from the second window execute Transaction 2 code. -- Transaction 1 Begin Tran Update TableA Set Name = 'Mark Transaction 1' where Id = 1 -- From Transaction 2 window execute the first update statement Update TableB Set Name = 'Mary Transaction 1' where Id = 1 -- From Transaction 2 window execute the second update statement Commit Transaction -- Transaction 2 Begin Tran Update TableB Set Name = 'Mark Transaction 2' where Id = 1 -- From Transaction 1 window execute the second update statement Update TableA Set Name = 'Mary Transaction 2' where Id = 1 -- After a few seconds notice that one of the transactions complete -- successfully while the other transaction is made the deadlock victim Commit Transaction Next Video : We will discuss the criteria SQL Server uses to choose a deadlock victim
Views: 56739 kudvenkat
SQL: Extract function
 
03:38
In this tutorial, you'll learn how to make use of extract function. PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural extension for SQL and the Oracle relational database. PL/SQL is available in Oracle Database (since version 7), TimesTen in-memory database (since version 11.2.1), and IBM DB2 (since version 9.7).[1] Oracle Corporation usually extends PL/SQL functionality with each successive release of the Oracle Database. PL/SQL includes procedural language elements such as conditions and loops. It allows declaration of constants and variables, procedures and functions, types and variables of those types, and triggers. It can handle exceptions (runtime errors). Arrays are supported involving the use of PL/SQL collections. Implementations from version 8 of Oracle Database onwards have included features associated with object-orientation. One can create PL/SQL units such as procedures, functions, packages, types, and triggers, which are stored in the database for reuse by applications that use any of the Oracle Database programmatic interfaces. PL/SQL works analogously to the embedded procedural languages associated with other relational databases. For example, Sybase ASE and Microsoft SQL Server have Transact-SQL, PostgreSQL has PL/pgSQL (which emulates PL/SQL to an extent), and IBM DB2 includes SQL Procedural Language,[2] which conforms to the ISO SQL’s SQL/PSM standard. The designers of PL/SQL modeled its syntax on that of Ada. Both Ada and PL/SQL have Pascal as a common ancestor, and so PL/SQL also resembles Pascal in several aspects. However, the structure of a PL/SQL package does not resemble the basic Object Pascal program structure as implemented by a Borland Delphi or Free Pascal unit. Programmers can define public and private global data-types, constants and static variables in a PL/SQL package.[3] PL/SQL also allows for the definition of classes and instantiating these as objects in PL/SQL code. This resembles usage in object-oriented programming languages like Object Pascal, C++ and Java. PL/SQL refers to a class as an "Abstract Data Type" (ADT) or "User Defined Type" (UDT), and defines it as an Oracle SQL data-type as opposed to a PL/SQL user-defined type, allowing its use in both the Oracle SQL Engine and the Oracle PL/SQL engine. The constructor and methods of an Abstract Data Type are written in PL/SQL. The resulting Abstract Data Type can operate as an object class in PL/SQL. Such objects can also persist as column values in Oracle database tables. PL/SQL is fundamentally distinct from Transact-SQL, despite superficial similarities. Porting code from one to the other usually involves non-trivial work, not only due to the differences in the feature sets of the two languages,[4] but also due to the very significant differences in the way Oracle and SQL Server deal with concurrency and locking. There are software tools available that claim to facilitate porting including Oracle Translation Scratch Editor,[5] CEITON MSSQL/Oracle Compiler [6] and SwisSQL.[7] The StepSqlite product is a PL/SQL compiler for the popular small database SQLite. PL/SQL Program Unit A PL/SQL program unit is one of the following: PL/SQL anonymous block, procedure, function, package specification, package body, trigger, type specification, type body, library. Program units are the PL/SQL source code that is compiled, developed and ultimately executed on the database. The basic unit of a PL/SQL source program is the block, which groups together related declarations and statements. A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords divide the block into a declarative part, an executable part, and an exception-handling part. The declaration section is optional and may be used to define and initialize constants and variables. If a variable is not initialized then it defaults to NULL value. The optional exception-handling part is used to handle run time errors. Only the executable part is required. A block can have a label. Package Packages are groups of conceptually linked functions, procedures, variables, PL/SQL table and record TYPE statements, constants, cursors etc. The use of packages promotes re-use of code. Packages are composed of the package specification and an optional package body. The specification is the interface to the application; it declares the types, variables, constants, exceptions, cursors, and subprograms available. The body fully defines cursors and subprograms, and so implements the specification. Two advantages of packages are: Modular approach, encapsulation/hiding of business logic, security, performance improvement, re-usability. They support object-oriented programming features like function overloading and encapsulation. Using package variables one can declare session level (scoped) variables, since variables declared in the package specification have a session scope.
Views: 3387 radhikaravikumar
Oracle username and password and Account unlocking
 
08:37
all education purpose videos
Views: 265253 Chandra Shekhar Reddy
03 Dead Lock in oracle database
 
11:37
DML Locks DML locks or data locks guarantee the integrity of data being accessed concurrently by multiple users. DML locks help to prevent damage caused by interference from simultaneous conflicting DML or DDL operations. By default, DML statements acquire both table-level locks and row-level locks. The reference for each type of lock or lock mode is the abbreviation used in the Locks Monitor from Oracle Enterprise Manager (OEM). For example, OEM might display TM for any table lock within Oracle rather than show an indicator for the mode of table lock (RS or SRX). Row Locks (TX) Row-level locks serve a primary function to prevent multiple transactions from modifying the same row. Whenever a transaction needs to modify a row, a row lock is acquired by Oracle. There is no hard limit on the exact number of row locks held by a statement or transaction. Also, unlike other database platforms, Oracle will never escalate a lock from the row level to a coarser granular level. This row locking ability provides the DBA with the finest granular level of locking possible and, as such, provides the best possible data concurrency and performance for transactions. The mixing of multiple concurrency levels of control and row level locking means that users face contention for data only whenever the same rows are accessed at the same time. Furthermore, readers of data will never have to wait for writers of the same data rows. Writers of data are not required to wait for readers of these same data rows except in the case of when a SELECT... FOR UPDATE is used. Writers will only wait on other writers if they try to update the same rows at the same point in time. In a few special cases, readers of data may need to wait for writers of the same data. For example, concerning certain unique issues with pending transactions in distributed database environments with Oracle. Transactions will acquire exclusive row locks for individual rows that are using modified INSERT, UPDATE, and DELETE statements and also for the SELECT with the FOR UPDATE clause. Modified rows are always locked in exclusive mode with Oracle so that other transactions do not modify the row until the transaction which holds the lock issues a commit or is rolled back. In the event that the Oracle database transaction does fail to complete successfully due to an instance failure, then Oracle database block level recovery will make a row available before the entire transaction is recovered. The Oracle database provides the mechanism by which row locks acquire automatically for the DML statements mentioned above. Whenever a transaction obtains row locks for a row, it also acquires a table lock for the corresponding table. Table locks prevent conflicts with DDL operations that would cause an override of data changes in the current transaction. Table Locks (TM) What are table locks in Oracle? Table locks perform concurrency control for simultaneous DDL operations so that a table is not dropped in the middle of a DML operation, for example. When Oracle issues a DDL or DML statement on a table, a table lock is then acquired. As a rule, table locks do not affect concurrency of DML operations. Locks can be acquired at both the table and sub-partition level with partitioned tables in Oracle. A transaction acquires a table lock when a table is modified in the following DML statements: INSERT, UPDATE, DELETE, SELECT with the FOR UPDATE clause, and LOCK TABLE. These DML operations require table locks for two purposes: to reserve DML access to the table on behalf of a transaction and to prevent DDL operations that would conflict with the transaction. Any table lock prevents the acquisition of an exclusive DDL lock on the same table, and thereby prevents DDL operations that require such locks. For example, a table cannot be altered or dropped if an uncommitted transaction holds a table lock for it. A table lock can be held in any of several modes: row share (RS), row exclusive (RX), share (S), share row exclusive (SRX), and exclusive (X). The restrictiveness of a table lock's mode determines the modes in which other table locks on the same table can be obtained and held.
Views: 225 OnLinE ReSoUrCe
PL/SQL: Object Type
 
08:31
In this tutorial, you'll learn what is a object type in sql/plsql PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural extension for SQL and the Oracle relational database. PL/SQL is available in Oracle Database (since version 7), TimesTen in-memory database (since version 11.2.1), and IBM DB2 (since version 9.7).[1] Oracle Corporation usually extends PL/SQL functionality with each successive release of the Oracle Database. PL/SQL includes procedural language elements such as conditions and loops. It allows declaration of constants and variables, procedures and functions, types and variables of those types, and triggers. It can handle exceptions (runtime errors). Arrays are supported involving the use of PL/SQL collections. Implementations from version 8 of Oracle Database onwards have included features associated with object-orientation. One can create PL/SQL units such as procedures, functions, packages, types, and triggers, which are stored in the database for reuse by applications that use any of the Oracle Database programmatic interfaces. PL/SQL works analogously to the embedded procedural languages associated with other relational databases. For example, Sybase ASE and Microsoft SQL Server have Transact-SQL, PostgreSQL has PL/pgSQL (which emulates PL/SQL to an extent), and IBM DB2 includes SQL Procedural Language,[2] which conforms to the ISO SQL’s SQL/PSM standard. The designers of PL/SQL modeled its syntax on that of Ada. Both Ada and PL/SQL have Pascal as a common ancestor, and so PL/SQL also resembles Pascal in several aspects. However, the structure of a PL/SQL package does not resemble the basic Object Pascal program structure as implemented by a Borland Delphi or Free Pascal unit. Programmers can define public and private global data-types, constants and static variables in a PL/SQL package.[3] PL/SQL also allows for the definition of classes and instantiating these as objects in PL/SQL code. This resembles usage in object-oriented programming languages like Object Pascal, C++ and Java. PL/SQL refers to a class as an "Abstract Data Type" (ADT) or "User Defined Type" (UDT), and defines it as an Oracle SQL data-type as opposed to a PL/SQL user-defined type, allowing its use in both the Oracle SQL Engine and the Oracle PL/SQL engine. The constructor and methods of an Abstract Data Type are written in PL/SQL. The resulting Abstract Data Type can operate as an object class in PL/SQL. Such objects can also persist as column values in Oracle database tables. PL/SQL is fundamentally distinct from Transact-SQL, despite superficial similarities. Porting code from one to the other usually involves non-trivial work, not only due to the differences in the feature sets of the two languages,[4] but also due to the very significant differences in the way Oracle and SQL Server deal with concurrency and locking. There are software tools available that claim to facilitate porting including Oracle Translation Scratch Editor,[5] CEITON MSSQL/Oracle Compiler [6] and SwisSQL.[7] The StepSqlite product is a PL/SQL compiler for the popular small database SQLite. PL/SQL Program Unit A PL/SQL program unit is one of the following: PL/SQL anonymous block, procedure, function, package specification, package body, trigger, type specification, type body, library. Program units are the PL/SQL source code that is compiled, developed and ultimately executed on the database. The basic unit of a PL/SQL source program is the block, which groups together related declarations and statements. A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords divide the block into a declarative part, an executable part, and an exception-handling part. The declaration section is optional and may be used to define and initialize constants and variables. If a variable is not initialized then it defaults to NULL value. The optional exception-handling part is used to handle run time errors. Only the executable part is required. A block can have a label. Package Packages are groups of conceptually linked functions, procedures, variables, PL/SQL table and record TYPE statements, constants, cursors etc. The use of packages promotes re-use of code. Packages are composed of the package specification and an optional package body. The specification is the interface to the application; it declares the types, variables, constants, exceptions, cursors, and subprograms available. The body fully defines cursors and subprograms, and so implements the specification. Two advantages of packages are: Modular approach, encapsulation/hiding of business logic, security, performance improvement, re-usability. They support object-oriented programming features like function overloading and encapsulation. Using package variables one can declare session level (scoped) variables, since variables declared in the package specification have a session scope.
Views: 12104 radhikaravikumar
Oracle Database Users / User Management (Simple)
 
09:33
Create, Alter, Drop, Grant Rights, Default Tablespace, Temporary Tablespace, Quota Setup, View Users Information, Lock / Unlock user account select username,account_status,default_tablespace, temporary_tablespace,created from dba_users; create user myuser identified by myuser; alter user myuser quota unlimited on mytbs; alter user myuser quota 100m on mytbs; alter user temporary tablespace temp; alter user myuser default tablespace mytbs; alter user myuser accout unlock; alter user myuser account lock; alter user myuser password expire; alter user myuser identified by youuser; create user myuser identified by myuser default tablespace users temporary tablespace temp quota unlimited on users; grant create session, resource to myuser; grant create session, resource to myuser with grant option; drop user asif; drop user asif cascade;
Views: 40705 Abbasi Asif
Audit table changes in sql server
 
06:45
sql server 2008 ddl trigger audit sql server table changes tracking In this video we will discuss, how to audit table changes in SQL Server using a DDL trigger. Table to store the audit data Create table TableChanges ( DatabaseName nvarchar(250), TableName nvarchar(250), EventType nvarchar(250), LoginName nvarchar(250), SQLCommand nvarchar(2500), AuditDateTime datetime ) Go The following trigger audits all table changes in all databases on a SQL Server. CREATE TRIGGER tr_AuditTableChanges ON ALL SERVER FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE AS BEGIN DECLARE @EventData XML SELECT @EventData = EVENTDATA() INSERT INTO SampleDB.dbo.TableChanges (DatabaseName, TableName, EventType, LoginName, SQLCommand, AuditDateTime) VALUES ( @EventData.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(250)'), @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(250)'), @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(250)'), @EventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(250)'), @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2500)'), GetDate() ) END In the above example we are using EventData() function which returns event data in XML format. Text version of the video http://csharp-video-tutorials.blogspot.com/2015/09/audit-table-changes-in-sql-server.html Slides http://csharp-video-tutorials.blogspot.com/2015/09/audit-table-changes-in-sql-server_12.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html Full SQL Server Course https://www.youtube.com/playlist?list=PL4cyC4G0M1RQ_Rm52cQ4CcOJ_T_HXeMB4 All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists
Views: 37801 kudvenkat
How to Easily Identifying Oracle Locks
 
02:10
How to Easily Identifying Oracle Locks. For more information, please go to: http://www.embarcadero.com/products/dbartisan-xe
Views: 2010 DBPowerStudio
Connect Excel to Oracle Database
 
05:03
How to connect an Oracle Database to Microsoft Excel. https://www.elance.com/s/edtemb/
Views: 96581 i_marketing
How to manager Oracle database locks and deadlocks with Oracle Enterprise Manager 12C
 
02:21
Video tutorials on How to manager Oracle database locks and deadlocks with Oracle Enterprise Manager 12C For more Database Administration articles and scripts go to www.aodba.com & www.verticablog.com More Articles, Scripts and How-To Papers on http://www.aodba.com
Views: 3087 AO DBA
SQL: External Table Part-1
 
05:43
In this tutorial, you'll learn what are external tables and how to create external tables..
Views: 24871 radhikaravikumar
install oracle DB, unlock HR table ,connect sql developer and NetBeans
 
08:07
oracle DB HR table unlock, oracle xe installation and conntect with Sql developer ,NetBeans
Views: 22089 TAYFUN ÇELİK
MSSQL - Understanding Isolation Level By Example (Serializable)
 
08:46
Example SQL Statements below used in the video, you can Copy and Paste for Transaction Isolation Level of Serializable, Read Committed, Read Uncommitted, Repeatable Read --===================================== -- Windows/Session #1 --===================================== SELECT @@SPID IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'SampleTable') DROP TABLE SampleTable CREATE TABLE [SampleTable] ( [Id] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](100) NULL, [Value] [varchar](100) NULL, [DateChanged] [datetime] DEFAULT(GETDATE()) NULL, CONSTRAINT [PK_SampleTable] PRIMARY KEY CLUSTERED ([Id] ASC) ) INSERT INTO SampleTable(Name, Value) SELECT 'Name1', 'Value1' UNION ALL SELECT 'Name2', 'Value2' UNION ALL SELECT 'Name3', 'Value3' SELECT * FROM SampleTable BEGIN TRAN INSERT INTO SampleTable(Name, Value) VALUES('Name4', 'Value4') --UPDATE SampleTable SET Name = Name + Name --UPDATE SampleTable SET Name = Name + Name WHERE Name = 'Name1' UPDATE SampleTable SET Name = Name + Name WHERE ID = 2 DELETE FROM SampleTable WHERE ID = 4 WAITFOR DELAY '00:0:10' COMMIT TRAN --===================================== -- Windows/Session #2 --===================================== --------------------------------------------------- -- This window/session is default READ COMMITTED -- --------------------------------------------------- SELECT @@SPID BEGIN TRAN SELECT * FROM SampleTable WAITFOR DELAY '00:00:10' SELECT * FROM SampleTable WAITFOR DELAY '00:00:10' SELECT * FROM SampleTable ROLLBACK SELECT b.name, c.name, a.* FROM sys.dm_tran_locks a INNER JOIN sys.databases b ON a.resource_database_id = database_id INNER JOIN sys.objects c ON a.resource_associated_entity_id = object_id --===================================== -- Windows/Session #3 --===================================== ----------------------------------------------------- -- This window/session is REPEATABLE READ -- ----------------------------------------------------- SELECT @@SPID SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRAN SELECT * FROM SampleTable WAITFOR DELAY '00:00:10' SELECT * FROM SampleTable WAITFOR DELAY '00:00:10' SELECT * FROM SampleTable COMMIT TRAN --===================================== -- Windows/Session #4 --===================================== ----------------------------------------------------- -- This window/session is SERIALIZABLE -- ----------------------------------------------------- SELECT @@SPID SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRAN SELECT * FROM SampleTable WAITFOR DELAY '00:00:10' SELECT * FROM SampleTable WAITFOR DELAY '00:00:10' SELECT * FROM SampleTable COMMIT TRAN
Views: 11777 CodeCowboyOrg
Merge Statement in Oracle SQL with Example (UPDATE/DELETE or INSERT)
 
15:04
This video demonstrates how to write a Merge Statement in oracle sql, how it is useful in doing a conditional update or delete or both of them when the condition between the target table and source table is evaluated to true, and how we can insert the record into the target table when condition is evaluated to false. The video also shows the use of UPDATE, DELETE and INSERT with Merge, and also the various possible ways to use a Merge statement!!!
Views: 7591 Kishan Mashru
Understanding RID Lock Part 1 in sql server
 
05:01
RID LOcking Demo : CReate table Demo_lock(Id int,name varchar(1000)) select * from Demo_lock go insert into Demo_lock select 1,'Shrikant' go begin transaction insert into Demo_lock select 2,'Demo'
Views: 426 SqlIsEasy
Oracle Rowid, RowNum and row_number
 
06:44
Oracle Rowid, RowNum and row_number... Explained in detailed...
Views: 5430 CONNECT TO LEARN
PL/SQL: Cursors using FOR loop
 
05:10
In this tutorial, you'll learn h.ow to write a cursor using for loop and the advantage of it. PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural extension for SQL and the Oracle relational database. PL/SQL is available in Oracle Database (since version 7), TimesTen in-memory database (since version 11.2.1), and IBM DB2 (since version 9.7).[1] Oracle Corporation usually extends PL/SQL functionality with each successive release of the Oracle Database. PL/SQL includes procedural language elements such as conditions and loops. It allows declaration of constants and variables, procedures and functions, types and variables of those types, and triggers. It can handle exceptions (runtime errors). Arrays are supported involving the use of PL/SQL collections. Implementations from version 8 of Oracle Database onwards have included features associated with object-orientation. One can create PL/SQL units such as procedures, functions, packages, types, and triggers, which are stored in the database for reuse by applications that use any of the Oracle Database programmatic interfaces. PL/SQL works analogously to the embedded procedural languages associated with other relational databases. For example, Sybase ASE and Microsoft SQL Server have Transact-SQL, PostgreSQL has PL/pgSQL (which emulates PL/SQL to an extent), and IBM DB2 includes SQL Procedural Language,[2] which conforms to the ISO SQL’s SQL/PSM standard. The designers of PL/SQL modeled its syntax on that of Ada. Both Ada and PL/SQL have Pascal as a common ancestor, and so PL/SQL also resembles Pascal in several aspects. However, the structure of a PL/SQL package does not resemble the basic Object Pascal program structure as implemented by a Borland Delphi or Free Pascal unit. Programmers can define public and private global data-types, constants and static variables in a PL/SQL package.[3] PL/SQL also allows for the definition of classes and instantiating these as objects in PL/SQL code. This resembles usage in object-oriented programming languages like Object Pascal, C++ and Java. PL/SQL refers to a class as an "Abstract Data Type" (ADT) or "User Defined Type" (UDT), and defines it as an Oracle SQL data-type as opposed to a PL/SQL user-defined type, allowing its use in both the Oracle SQL Engine and the Oracle PL/SQL engine. The constructor and methods of an Abstract Data Type are written in PL/SQL. The resulting Abstract Data Type can operate as an object class in PL/SQL. Such objects can also persist as column values in Oracle database tables. PL/SQL is fundamentally distinct from Transact-SQL, despite superficial similarities. Porting code from one to the other usually involves non-trivial work, not only due to the differences in the feature sets of the two languages,[4] but also due to the very significant differences in the way Oracle and SQL Server deal with concurrency and locking. There are software tools available that claim to facilitate porting including Oracle Translation Scratch Editor,[5] CEITON MSSQL/Oracle Compiler [6] and SwisSQL.[7] The StepSqlite product is a PL/SQL compiler for the popular small database SQLite. PL/SQL Program Unit A PL/SQL program unit is one of the following: PL/SQL anonymous block, procedure, function, package specification, package body, trigger, type specification, type body, library. Program units are the PL/SQL source code that is compiled, developed and ultimately executed on the database. The basic unit of a PL/SQL source program is the block, which groups together related declarations and statements. A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords divide the block into a declarative part, an executable part, and an exception-handling part. The declaration section is optional and may be used to define and initialize constants and variables. If a variable is not initialized then it defaults to NULL value. The optional exception-handling part is used to handle run time errors. Only the executable part is required. A block can have a label. Package Packages are groups of conceptually linked functions, procedures, variables, PL/SQL table and record TYPE statements, constants, cursors etc. The use of packages promotes re-use of code. Packages are composed of the package specification and an optional package body. The specification is the interface to the application; it declares the types, variables, constants, exceptions, cursors, and subprograms available. The body fully defines cursors and subprograms, and so implements the specification. Two advantages of packages are: Modular approach, encapsulation/hiding of business logic, security, performance improvement, re-usability. They support object-oriented programming features like function overloading and encapsulation. Using package variables one can declare session level (scoped) variables, since variables declared in the package specification have a session scope.
Views: 17123 radhikaravikumar
performance tuning
 
18:59
Tracing: Transaction ST12 = Combination of two transactions . SAT (ABAP Trace) , ST05 = SQL Trace SQL Trace: To monitor the database access of reports and transactions. Less common traces : Enqueue Trace: (the locking system),RFC Trace, Table buffer trace: Performance Improvements ABAP Coding: Keep an eye on those loops. Select statements in loops = absolutely avoid = use for all entries instead Function call, or performs in loops = re - visit and see if you can optimize The "READ TABLE" statement Sorted table- Binary search .. Or even better use "Index" on internal tables. Use mass operations on internal table = internal table copy one to another, Append lines of int_fligh1 to int_fligh2. Use field symbols to modify internal table in a loop (minor improvement ) A little trick if you call performs in a loop:: Use static variables Avoid sleep, wait etc commands.. Or synchronous RFC calls 2)Database bottleneck Use Indexes : Make sure Indexes are being used in your select statement (check with trace if needed) Extreme case , create new index (this has the disadvantage of making the insert slower) Select -- End select loop : Usually database optimiser will fix it, still avoid it Select * into corresponding fields = about a millisecond slower Make clever use of inner joins Use views Use (max,min, avg): example : Select max( flightnum ) from flight . GET RUN TIME command Reduce I/O first, then memory, then CPU activity.Field-groups can be useful for multi-level sorting and displaying. However, they write their data to the system's paging space, rather than to memory (internal tables use memory)
Views: 14601 Sourabh Bora
Oracle 11gR2 Performance Tuning
 
20:24
Learn Oracle Performance Tuning with Real Time Activities. Watch this video to see how HARD PARSING issue can be rectified with simple resolution. This course will load the system and show you a real time approach to resolve performance issues on your environment. Get more details at : www.orskl.com/training Queries? WhatsApp: +919951696808 Get your hands dirty now and become performance tuning expert.
Views: 9328 OrSkl Academy
Shared - Locks in SQL Server - Part 3
 
06:49
Click here to Subscribe to IT PORT Channel : https://www.youtube.com/channel/UCMjmoppveJ3mwspLKXYbVlg Shared (S) locks allow concurrent transactions to read (SELECT) a resource under pessimistic concurrency control. No other transactions can modify the data while shared (S) locks exist on the resource. Shared (S) locks on a resource are released as soon as the read operation completes, unless the transaction isolation level is set to repeatable read or higher, or a locking hint is used to retain the shared (S) locks for the duration of the transaction. Isolation Level - https://youtu.be/ESET4zuNLoM Script for Active_Locks Function --------------------------------------------------- Create Function Active_locks () returns table return select Top 10000000 case dtl.request_session_id when -2 then 'orphaned distributed transaction' when -3 then 'deferred recovery transaction' else dtl.request_session_id end as spid, db_name(dtl.resource_database_id) as databasename, so.name as lockedobjectname, dtl.resource_type as lockedresource, dtl.request_mode as locktype, es.login_name as loginname, es.host_name as hostname, case tst.is_user_transaction when 0 then 'system transaction' when 1 then 'user transaction' end as user_or_system_transaction, at.name as transactionname, dtl.request_status from sys.dm_tran_locks dtl join sys.partitions sp on sp.hobt_id = dtl.resource_associated_entity_id join sys.objects so on so.object_id = sp.object_id join sys.dm_exec_sessions es on es.session_id = dtl.request_session_id join sys.dm_tran_session_transactions tst on es.session_id = tst.session_id join sys.dm_tran_active_transactions at on tst.transaction_id = at.transaction_id join sys.dm_exec_connections ec on ec.session_id = es.session_id cross apply sys.dm_exec_sql_text(ec.most_recent_sql_handle) as st where resource_database_id = db_id() order by dtl.request_session_id
Views: 431 IT Port
Move tables between databases PostgreSQL with a DBLINK
 
03:51
Move tables between databases PostgreSQL with a DBLINK do $$ declare r record ; begin for r in select * from dblink ('host=localhost user=postgres password=password dbname=source', 'select indexdef from pg_indexes where tablename = ''mytable''') as linktable ( indexdef text ) loop execute r.indexdef; end loop; end $$
Views: 3288 randomtutorial
Last DBA Office Hours before OpenWorld
 
58:56
We're heading into OpenWorld season with this month's DBA Office Hours. We preview OpenWorld and how to get the best out of it, as well as covering - issues with the MERGE command - flashback database - unusual blocking locks - when EXPLAIN PLAN drops table from view blog: https://connor-mcdonald.com twitter: https://twitter.com/connor_mc_d https://developer.oracle.com/ https://cloud.oracle.com/en_US/tryit
Views: 111 Oracle Developers
PL/SQL: Ref cursor Types
 
07:57
In this tutorial, you'll learn the types of ref cursors.. PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural extension for SQL and the Oracle relational database. PL/SQL is available in Oracle Database (since version 7), TimesTen in-memory database (since version 11.2.1), and IBM DB2 (since version 9.7).[1] Oracle Corporation usually extends PL/SQL functionality with each successive release of the Oracle Database. PL/SQL includes procedural language elements such as conditions and loops. It allows declaration of constants and variables, procedures and functions, types and variables of those types, and triggers. It can handle exceptions (runtime errors). Arrays are supported involving the use of PL/SQL collections. Implementations from version 8 of Oracle Database onwards have included features associated with object-orientation. One can create PL/SQL units such as procedures, functions, packages, types, and triggers, which are stored in the database for reuse by applications that use any of the Oracle Database programmatic interfaces. PL/SQL works analogously to the embedded procedural languages associated with other relational databases. For example, Sybase ASE and Microsoft SQL Server have Transact-SQL, PostgreSQL has PL/pgSQL (which emulates PL/SQL to an extent), and IBM DB2 includes SQL Procedural Language,[2] which conforms to the ISO SQL’s SQL/PSM standard. The designers of PL/SQL modeled its syntax on that of Ada. Both Ada and PL/SQL have Pascal as a common ancestor, and so PL/SQL also resembles Pascal in several aspects. However, the structure of a PL/SQL package does not resemble the basic Object Pascal program structure as implemented by a Borland Delphi or Free Pascal unit. Programmers can define public and private global data-types, constants and static variables in a PL/SQL package.[3] PL/SQL also allows for the definition of classes and instantiating these as objects in PL/SQL code. This resembles usage in object-oriented programming languages like Object Pascal, C++ and Java. PL/SQL refers to a class as an "Abstract Data Type" (ADT) or "User Defined Type" (UDT), and defines it as an Oracle SQL data-type as opposed to a PL/SQL user-defined type, allowing its use in both the Oracle SQL Engine and the Oracle PL/SQL engine. The constructor and methods of an Abstract Data Type are written in PL/SQL. The resulting Abstract Data Type can operate as an object class in PL/SQL. Such objects can also persist as column values in Oracle database tables. PL/SQL is fundamentally distinct from Transact-SQL, despite superficial similarities. Porting code from one to the other usually involves non-trivial work, not only due to the differences in the feature sets of the two languages,[4] but also due to the very significant differences in the way Oracle and SQL Server deal with concurrency and locking. There are software tools available that claim to facilitate porting including Oracle Translation Scratch Editor,[5] CEITON MSSQL/Oracle Compiler [6] and SwisSQL.[7] The StepSqlite product is a PL/SQL compiler for the popular small database SQLite. PL/SQL Program Unit A PL/SQL program unit is one of the following: PL/SQL anonymous block, procedure, function, package specification, package body, trigger, type specification, type body, library. Program units are the PL/SQL source code that is compiled, developed and ultimately executed on the database. The basic unit of a PL/SQL source program is the block, which groups together related declarations and statements. A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords divide the block into a declarative part, an executable part, and an exception-handling part. The declaration section is optional and may be used to define and initialize constants and variables. If a variable is not initialized then it defaults to NULL value. The optional exception-handling part is used to handle run time errors. Only the executable part is required. A block can have a label. Package Packages are groups of conceptually linked functions, procedures, variables, PL/SQL table and record TYPE statements, constants, cursors etc. The use of packages promotes re-use of code. Packages are composed of the package specification and an optional package body. The specification is the interface to the application; it declares the types, variables, constants, exceptions, cursors, and subprograms available. The body fully defines cursors and subprograms, and so implements the specification. Two advantages of packages are: Modular approach, encapsulation/hiding of business logic, security, performance improvement, re-usability. They support object-oriented programming features like function overloading and encapsulation. Using package variables one can declare session level (scoped) variables, since variables declared in the package specification have a session scope.
Views: 11939 radhikaravikumar
DB2 Tips n Tricks Part 22 - How to Exclusively lock set of Rows using select query
 
08:56
How to Exclusively lock a set of Rows using select query Solution: LOCK REQUEST Clause USE and KEEP SHARE/UPDATE/EXCLUSIVE Locks Happy Learning & Sharing
Views: 1891 DB2-LUW-ACADEMY
MySQL Metadata Locks
 
05:23
Introduction to metadata locks starting in MySQL 5.5 and some quick ways to avoid running into issues in your environment.
Views: 573 dtest
Truncate All Tables from a SQL Server Database - TSQL Tutorial
 
10:09
Let's think about a database that we use for our ETL process and we call that database staging. We want to truncate all the tables in morning so those can be used for the daily process. Once way is to write our Truncate statement for each of the table but tables can be dropped or new tables can be created as well. We want to write a query that we can be use to Truncate all the tables from a database and we don't have to rewrite our Truncate statements. If the tables do not have Primary Key -Foreign Key relationship, we can use Truncate. But we often have tables which are referenced by other tables. We can not use Truncate statement for those tables. The only way to Truncate those tables is to drop the Foreign Key Constraint before Truncating and then Truncate and Recreate Relationship after that. The below Code can be used to Truncate all the tables from the database. If Tables will have the Pk-Fk relationship, The script will drop the Foreign Key Constraints first and then Truncate those tables and finally recreate the Foreign Key constraints. Before you run below Script, Make sure you are pointing to correct Database and Server! All the best with Truncating all the tables from a SQL Server Database. Link to Blog for Script used in this video http://sqlage.blogspot.com/2015/02/truncate-all-tables-in-database-in-sql.html You can visit our blog http://sqlage.blogspot.com/ Twitter https://twitter.com/AamirSh48904922 Facebook https://www.facebook.com/SqlAge
Views: 32305 TechBrothersIT
Optimizing SQL Performance
 
51:42
Held on July 12 2018 In July's session we mainly looked at performance. Highlights include: 1:30 How does the database process subqueries? 5:20 Performance: comparing insert ... select to create tmp table, insert select from tmp; DDL in PL/SQL; dynamic SQL problems 12:45 18c private temporary tables; tables specific to a session; DDL you can rollback across! 21:00 Improving update performance: things to watch for; insert vs. update; "join-update" - create a view instead; create table as select "update" 34:05 Analytic function performance: first_value non-determinism; min keep vs first_value; computing function in a subquery; indexes for analytic functions AskTOM Office Hours offers free, monthly training and tips on how to make the most of Oracle Database, from Oracle product managers, developers and evangelists. https://asktom.oracle.com/ Oracle Developers portal: https://developer.oracle.com/ Sign up for an Oracle Cloud trial: https://cloud.oracle.com/en_US/tryit music: bensound.com
Views: 345 Oracle Developers
Simplified DBA-019-Data Concurrency and Locks
 
03:56
DATA CONCURRENCY More than one user connections ( session ) can access same data. For example user1 and user2 can view same employee information at the same time. But not two users can modify same data in one time. This is maintained by oracle lock mechanism. Before the database allows a session to modify data, the session must first lock the data that is being modified. A lock gives the session exclusive control over the data so that no other transaction can modify the locked data until the lock is released. Transactions can lock individual rows of data, multiple rows, or even entire tables. Oracle Database supports both manual and automatic locking. Automatically acquired locks always choose the lowest possible level of locking to minimize potential conflicts with other transactions. Demo: User1 is modifying an employee. User1 session will acquire a lock on the row. Same time user2 is trying to modify same employee. Since User1 has acquired lock, user2 has to wait until user1 releases the lock. Manual locking is possible. A user can place a lock manually as follows: LOCK TABLE mytable1 IN EXCLUSIVE MODE; With the preceding statement, any other transaction that tries to update a row in the locked table must wait until the transaction that issued the lock request completes. EXCLUSIVE is the strictest lock mode. The following are the other lock modes: ROW SHARE: Permits concurrent access to the locked table but prohibits sessions from locking the entire table for exclusive access ROW EXCLUSIVE: Is the same as ROW SHARE, but also prohibits locking in SHARE mode. The ROW EXCLUSIVE locks are automatically obtained when updating, inserting, or deleting data. ROW EXCLUSIVE locks allow multiple readers and one writer. SHARE: Permits concurrent queries but prohibits updates to the locked table. A SHARE lock is required (and automatically requested) to create an index on a table. However, online index creation requires a ROW SHARE lock that is used when building the index.
SQL: Rank & Dense_rank functions
 
06:47
In this tutorial, you'll learn the difference between rank() and dense_rank() functions..
Views: 26030 radhikaravikumar
SqlServer transaction locking vs postgreSQL
 
04:18
Video demonstrating the transaction interference in Sqlserver due to its locking mechanis vs the expected behavior on postgreSQL A table is created in both databases, first sqlserver is used and two clients are used to simulate diferent conections. While sqlserver locks on insert, remove, or update. Postgres does as expected and allows you to complete other transactions
Views: 216 Dario Garcia

Sample cover letter for mortgage application
How to write a cover letter for a resume samples
Investment banking cover letter uk
Uvm admissions essay sample
Iti mallepally hyderabad admissions essay