Home
Search results “Delete partition table in oracle”
Partitioning in Oracle Explained with Real project Examples : Introduction
 
22:43
This Video series will explain partitioning and its use cases referencing real project examples from different domain. It will explain what when and why of partitioning in a simple but elaborate manner. This is the 1st video which explains why partitioning is required and what are the advantages we gain from it. The following videos will explain when should we do it and how.
Views: 30227 Tech Coach
exchange Partition , Move partition in oracle
 
08:42
exchange Partition , Move partition in oracle Possible operations with partitions SQL Tutorial SQL Tutorial for beginners PLSQL Tutorial PLSQL Tutorial for beginners PL/SQL Tutorial PL SQL Tutorial PL SQL Tutorial for beginners PL/SQL Tutorial for beginners Oracle SQL Tutorial Add Drop Rename Split Move ( Moving from Tablespace to tablespace) Exchange (Table to Table(non Partitioned table)) not partitioned only exchanging data (p)
Views: 1656 TechLake
What is a Partition in Oracle and Type of Partitions , Range Partition
 
10:52
What is Partition in Oracle and Type of Partitions Range Partition SQL Tutorial SQL Tutorial for beginners PLSQL Tutorial PLSQL Tutorial for beginners PL/SQL Tutorial PL SQL Tutorial PL SQL Tutorial for beginners PL/SQL Tutorial for beginners Oracle SQL Tutorial
Views: 2979 TechLake
Local Vs Global Partitioned Index in Oracle 11g
 
17:30
The Video Explains the difference between Local Partitioned Indexes(Prefixed vs Non Prefixed Indexes). and Global Partitioned Index along with the challenges in maintaining Global partitioned Indexes when the underlying tables partitioned is dropped/truncated/Merged/Moved. Local Partitioned Index Shares the same boundaries as the table and are in the same number as table partitions they are widely used in DSS and DWH systems. While Global Partitioned Index are predominantly used in OLTP systems
Views: 8237 Tech Coach
Table Shrinking in Oracle Database
 
14:15
1.Shrink the Table: Shrinking is started from 10g. In this method I’m using user u1 and table name sm1. Now I’m deleting some rows in sm1 COUNT ---------- 1048576 Table sm1 has 1048576 rows. [email protected]: delete from sm1 where deptno=10; 262144 rows deleted. I deleted above number of rows. Rows COUNT ---------- 786432 And I’m giving commit [email protected]: commit; Commit complete. So now we have 786432 rows in sm1 table. Now see the following command [email protected]: select OWNER,TABLESPACE_NAME,SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024||' mb'"space",BLOCKS,EXTENTS from dba_segments where tablespace_name like 'U%TS'; OWNER TABLESPACE_NAME SEGMENT_NAME SEGMENT_TYPE space BLOCKS EXTENTS ----- --------------- ------------- ------------- ------ ---------- ---------- U1 U1TS SM1 TABLE 29 mb 3712 44 After I deleted some rows in sm1 table still above result showing same values, so now our duty is shrink this table. This is done by following 2 ways, i By using COMPACT key word: In this method shrinking is done in two phases. In the first phase all fragmented space are just defragmented, but still the High Water Mark is persist with last used block only. That mean used free blocks are not de allocated and HWM is not updated here. Issue the following command before use shrink command. [email protected] alter table sm1 enable row movement; Table altered. There is particular use with above command, when we shrink the table all rows are moves to contiguous blocks, so here row movement should be done. By default the row movement is disabled for any table, so above command enabled the row movement. Then execute shrink command now. [email protected]: alter table sm1 shrink space compact; Table altered. Now see the space of table by using below command. [email protected]: select OWNER,TABLESPACE_NAME,SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024||' mb'"space",BLOCKS,EXTENTS from dba_segments where tablespace_name like 'U%TS'; OWNER TABLESPACE_NAME SEGMENT_NAME SEGMENT_TYPE space BLOCKS EXTENTS ----- --------------- ------------- ------------- ------ ---------- ---------- U1 U1TS SM1 TABLE 29 mb 3712 44 So here seems nothing happened with above shrink command, but internally the fragmented space is defragmented. But the high water mark is not updated, used free blocks are also not de allocated. For de allocating the used blocks we have to execute below command. This is the second phase. [email protected]: alter table sm1 shrink space; Table altered. Now see the space by using below command. [email protected]: select OWNER,TABLESPACE_NAME,SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024||' mb'"space",BLOCKS,EXTENTS from dba_segments where tablespace_name like 'U%TS'; OWNER TABLESPACE_NAME SEGMENT_NAME SEGMENT_TYPE space BLOCKS EXTENTS ----- --------------- ------------- ------------- ---------- ---------- ---------- U1 U1TS SM1 TABLE 20.8125 mb 2664 36 So now the space of sm1 table is reduced. Note: Actually the alter table sm1 shrink space command will complete these two phases of the shrinking of table at a time. But here we done shrink process in two phases because when we use alter table sm1 shrink space command the table locked temporarily some time period, during this period users unable to access the table. So if we use alter table sm1 shrink space compact command the table is not locked but space is defragmented. When we not in business hours issue the second phase shrink command then users are won’t get any problem. ii Because of above method the table dependent objects are goes to invalid state, to overcome this problem we have to use below command. [email protected]: alter table sm1 shrink space cascade; Table altered. The above command also shrinks the space of all dependent objects. We also do this in two phases like above two phases. See the below command. [email protected]: alter table sm1 shrink space compact cascade; Table altered. And then [email protected]: alter table sm1 shrink space cascade; Table altered. Transporting tablespace to different platform by Using RMAN : https://www.youtube.com/watch?v=CN401PUKK4A Oracle EBS apps Upgrade from 12 2 to 12 2 5 (start CD 51) : https://www.youtube.com/watch?v=zeO4goqR70Y Transport tablespace by using RMAN.: https://www.youtube.com/watch?v=YG6kWX7Par8
Views: 7283 BhagyaRaj Katta
PARTITION BY in Oracle SQL  | Oracle SQL Tutorials | By Mr.Murali
 
31:56
PARTITION BY in Oracle SQL | Oracle SQL Tutorials | By Mr.Murali ►For Registration : https://goo.gl/r6kJbB ►Call: +91-8179191999 ► Visit Our Website: http://nareshit.in/ http://nareshit.com/ ► About NareshIT: "Naresh IT is the Best Software Training Institute for Hadoop, Salesforce, AWS, DevOps, Sprak, Data Science, Python, Tableau, RPA ,Java, C#.NET, ASP.NET, Oracle, Testing Tools, Silver light, Linq, SQL Server, Selenium, Android, iPhone, C Language, C++, PHP and Digital Marketing in Hyderabad, Chennai and Vijayawada, India which provides online and classroom training classes" ►For Registration : https://goo.gl/r6kJbB ►Call: India- 8179191999, USA- 404-232-9879 Email: [email protected] ►Our Online Training Features: 1.Training with Real-Time Experts 2.Industry Specific Scenario’s 3.Flexible Timings 4.Soft Copy of Material 5.Share Video's of each and every session. Check The Below Links: ►For Course Reg : https://goo.gl/r6kJbB ► Subscribe to Our Channel: https://goo.gl/q9ozyG ► Circle us on G+: https://plus.google.com/NareshIT ► Like us on Facebook: https://www.facebook.com/NareshIT ► Follow us on Twitter: https://twitter.com/nareshitech ► Visit Our Website: http://nareshit.in/ http://nareshit.com/
Views: 6071 Naresh i Technologies
Partitioning in Oracle - Performance Basics
 
28:35
This is the 2nd video from " Partitioning in Oracle " series, It explains how oracle stores and manages data. What is single Block IO and Multi Block IO ? Why full table scan is better than index access in few cases. The video is very elaborate, I have tried my level best to keep it as simple as possible
Views: 12221 Tech Coach
What is High Water Mark(HVM) in oracle and how it effects the performance of queries?
 
05:41
This video tutorial is to understand the concept of a High Water Mark or HVM of a oracle table, also it states the various changes in the HVM when we perform a insert, delete and Truncate operation on the table, and the video also informs why is the truncate operation more advisable and optimized then the delete operation.
Views: 3930 Kishan Mashru
Table Partitioning in Oracle Sql | Oracle Sql Partitioning | Oracle Sql Partitioning Step by Step
 
03:17
Table Partitioning in Oracle Sql | Oracle Sql Partitioning | Oracle Sql Partitioning Step by Step --------------------------------------------------------------------------------------------------------- oracle, partition, partitioning, sql, partitioning in oracle, hash, list, range, interval partition, reference partition, composite partition, index partition, performance, partition pruning, partition wise join, sql server, partition table, sql server table partitioning, table partitioning, sql server partitioning, sql server table partition, table partition, what is partition, partitioned table, advantages of partition, types of partitions, ,table partitioning in sql server 2012 step by step,table partitioning in sql server 2014 step by step,table partitioning in sql server 2016 step by step,partition table sql server 2012 example,sql server partition existing table,sql server partition table by month,table partitioning in sql server 2008 r2 step by step,sql server partitioning best practices,horizontal partitioning in sql server, sql tutorials for beginners, sql expert, sql expert tutorials, database, oracle, table, online training,insert into oracle,oracle insert into multiple rows,create table oracle,oracle 12c alter table add identity column,oracle create table default value sysdate,collections in oracle pl sql with examples,Oracle 12c, database, oracle database (software), sql, sql developer, sql tutorials, how to create database using dbca, how to create database in oracle,oracle cloud, database 12c, dba genesis,database services, database, create, 11g, dba, db, beginner, Please Subscribe My Channel Composite Range-Range Partitioning Composite range-range partitioning enables logical range partitioning along two dimensions; for example, partition by order_date and range subpartition by shipping_date. Composite Range-Hash Partitioning Composite range-hash partitioning partitions data using the range method, and within each partition, subpartitions it using the hash method. Composite range-hash partitioning provides the improved manageability of range partitioning and the data placement, striping, and parallelism advantages of hash partitioning. Composite Range-List Partitioning Composite range-list partitioning partitions data using the range method, and within each partition, subpartitions it using the list method. Composite range-list partitioning provides the manageability of range partitioning and the explicit control of list partitioning for the subpartitions. Composite List-Range Partitioning Composite list-range partitioning enables logical range subpartitioning within a given list partitioning strategy; for example, list partition by country_id and range subpartition by order_date. Composite List-Hash Partitioning Composite list-hash partitioning enables hash subpartitioning of a list-partitioned object; for example, to enable partition-wise joins. Composite List-List Partitioning Composite list-list partitioning enables logical list partitioning along two dimensions; for example, list partition by country_id and list subpartition by sales_channel. Composite Hash-Hash Partitioning Composite hash-hash partitioning enables hash partitioning along two dimensions. This technique is beneficial to enable partition-wise joins along two dimensions. Composite Hash-List Partitioning Composite hash-list partitioning enables hash partitioning along two dimensions. Composite Hash-Range Partitioning Composite hash-range partitioning enables hash partitioning along two dimensions.
HOW TO IDENTIFY AND DELETE DUPLICATE ROWS USING ROWID AND GROUPBY IN ORACLE SQL
 
07:53
This video demonstrates examples on how to find and delete duplicate records from a table. The video gives simple and easy to understand examples on finding duplicate records from a table using group by and having clause and row_number function. It also shows the ways in which duplicates can be deleted very efficiently using the rowid of that record. You can get the code from our website http://oracleplsqlblog.com/FullBlog/FullBlog/21
Views: 11336 Kishan Mashru
sub partitioning in oracle  or composite partitioning in Oracle RANGE-LIST, RANGE-HASH
 
07:24
sub partitioning in oracle or composite partitioning in Oracle SQL Tutorial SQL Tutorial for beginners PLSQL Tutorial PLSQL Tutorial for beginners PL/SQL Tutorial PL SQL Tutorial PL SQL Tutorial for beginners PL/SQL Tutorial for beginners Oracle SQL Tutorial
Views: 804 TechLake
SQL 12c Tutorial 23 : SQL Creating PARTITION on  Existing Table SQL 12c new feature
 
06:20
SQL 12c Tutorial 23 : SQL Creating PARTITION on Existing Table SQL 12c new feature SQL 12c New Features SQL 12c Tutorial for beginners Oracle SQL Tutorial for beginners Creating Partition on existing table
Views: 1501 TechLake
part 12 truncate partition
 
01:55
Truncate partition, alter table, data warehousing
How to delete duplicate records from a table in oracle
 
02:01
Let c how to remove duplicates from a table..I have one table here 'student' It has two duplicate records. 3 joe 4 robert These two records has duplicates. So I have to remove them. I can remove them using rowid functionality using one subquery. You can give different column names in GROUP BY clause. to identify the duplicate record. You can give just primary key values also. Duplicate rows are deleted now... Query used in video: delete from student where rowid not in (select min(rowid) from student group by sno,sname); Thanks for watching...:)
Views: 15651 WingsOfTechnology
01 Overview of table Partition in oracle
 
07:26
Partitioning enhances the performance, manageability, and availability of a wide variety of applications and helps reduce the total cost of ownership for storing large amounts of data. Partitioning allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, enabling these database objects to be managed and accessed at a finer level of granularity. Oracle provides a rich variety of partitioning strategies and extensions to address every business requirement. Moreover, since it is entirely transparent, partitioning can be applied to almost any application without the need for potentially expensive and time consuming application changes. Partitioning allows a table, index, or index-organized table to be subdivided into smaller pieces, where each piece of such a database object is called a partition. Each partition has its own name, and may optionally have its own storage characteristics. From the perspective of a database administrator, a partitioned object has multiple pieces that can be managed either collectively or individually. This gives the administrator considerable flexibility in managing partitioned objects. However, from the perspective of the application, a partitioned table is identical to a non-partitioned table; no modifications are necessary when accessing a partitioned table using SQL queries and DML statements. Partitioning Key ======================== Each row in a partitioned table is unambiguously assigned to a single partition. The partitioning key is comprised of one or more columns that determine the partition where each row will be stored. Oracle automatically directs insert, update, and delete operations to the appropriate partition through the use of the partitioning key. When to Partition a Table ========================== Here are some suggestions for when to partition a table: Tables greater than 2 GB should always be considered as candidates for partitioning. Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only. When the contents of a table need to be distributed across different types of storage devices. When to Partition an Index ============================= Here are some suggestions for when to consider partitioning an index: Avoid rebuilding the entire index when data is removed. Perform maintenance on parts of the data without invalidating the entire index. Reduce the impact of index skew caused by an index on a column with a monotonically increasing value. Partitioned Index-Organized Tables =================================== Partitioned index-organized tables are very useful for providing improved performance, manageability, and availability for index-organized tables. For partitioning an index-organized table: ============================================ Partition columns must be a subset of the primary key columns Secondary indexes can be partitioned (both locally and globally) OVERFLOW data segments are always equi-partitioned with the table partitions See Also: Oracle Database Concepts for more information about index-organized tables System Partitioning System partitioning enables application-controlled partitioning without having the database controlling the data placement. The database simply provides the ability to break down a table into partitions without knowing what the individual partitions are going to be used for. All aspects of partitioning have to be controlled by the application. For example, an insertion into a system partitioned table without the explicit specification of a partition will fail. System partitioning provides the well-known benefits of partitioning (scalability, availability, and manageability), but the partitioning and actual data placement are controlled by the application. See Also: Oracle Database Data Cartridge Developer's Guide for more information about system partitioning Partitioning for Information Lifecycle Management Information Lifecycle Management (ILM) is concerned with managing data during its lifetime. Partitioning plays a key role in ILM because it enables groups of data (that is, partitions) to be distributed across different types of storage devices and managed individually.
Views: 7400 Md Arshad
How to Delete Huge Data from a SQL Server Table in Small Chunks - SQL Server / TSQL Tutorial
 
31:00
In this video you will learn how to delete large number of records from a SQL Server Table. By watch this video you will learn following topics 1--How to Delete large number of records from SQL Server Table in small chunks 2--What is the difference between Full Recovery Mode and Simple Recovery Mode 3--Does Transactional Log file grow if we set the database to Simple Recovery and Delete huge number or records? 4--How to get the record count while Transactions is deleting the records by using NoLock Option 5--How to Shrink Log file and release unused space to Operating System 6- How to write script to delete records in small chunks from SQL Server Table Blog post link for scripts used in the video: http://sqlage.blogspot.com/2015/03/how-to-delete-huge-data-from-sql-server.html
Views: 9937 TechBrothersIT
Overview row movement option with Partitioned table
 
14:56
Overview row movement option with Partitioned table, Oracle Database 11g
Views: 11616 Mahir M. Quluzade
Hash Partitioning in Oracle
 
16:25
The Video explains Hash partitioning in oracle and how it focuses on equal data distribution. It explains how hash partitioning uses partition joins to improve performance in VLDB by MPP.
Views: 7163 Tech Coach
Oracle Partition By clause, Oracle Analytic Functions tutorial
 
09:09
More about analytic functions, visit http://www.learn-with-video-tutorials.com Oracle Analytic Functions and Over By Partition clause enable calculating cumulative and moving aggregates returning multiple rows for each group.
Range Partitioning in Oracle
 
15:00
In this video I have explained what is range partitioning along with it's 2 real project use cases. I have also explained interval Partitioning as an extension of Range partitioning If you have not watched my Initial Videos on partitioning I will recommended watching them before watching this video Apologies for the 10 second video glitch between 6 and 7 minutes :(
Views: 5805 Tech Coach
GLOBAL TEMPORARY TABLE (GTT) IN ORACLE SQL WITH EXAMPLES
 
13:33
This video demonstrates the usage and working of GTT in oracle SQL. The video shows how internally oracle works with GTT, and also explains the working of ON COMMIT DELETE ROWS and ON COMMIT PRESERVER ROWS in oracle with easy examples.
Views: 10284 Kishan Mashru
Table Partitioning in Oracle Sql|Hash Partitioning|Range Partitioning|List Partitioning|Partitioning
 
09:04
Table Partitioning in Oracle Sql | Hash Partitioning | Range Partitioning | List Partitioning | Partitioning --------------------------------------------------------------------------------------------------------- hash partitioning,range partitioning,oracle hash partitioned index example,partition by range interval,when to use range partitioning,what is table partitioning,list partitioning,how to query partition table in oracle,partition, sql server, sql, partitioning in oracle, hash, performance, oracle, partitioning, list, range, interval partition, reference partition, composite partition, index partition, partition pruning, table partitioning, sql server table partition, table partition, hash partitioning, composite partitioning, sql server partitioning, sql server table partitioning, partition table, types of partitions, what is partition, advantages of partition, partition index, sql tutorials for beginners, sql expert, sql expert tutorials, database, oracle, table, online training,insert into oracle,oracle insert into multiple rows,create table oracle,oracle 12c alter table add identity column,oracle create table default value sysdate,collections in oracle pl sql with examples,Oracle 12c, database, oracle database (software), sql, sql developer, sql tutorials, how to create database using dbca, how to create database in oracle,oracle cloud, database 12c, dba genesis,database services, database, create, 11g, dba, db, beginner, Please Subscribe My Channel
MSSQL - What is Table Partitioning Explained
 
05:49
Link to Scripts in Demo - https://goo.gl/JCQLUm Link to Adventure Works DB - http://msftdbprodsamples.codeplex.com/releases/view/55330 Brief explanation of what is Table Partitioning in SQL Server 2014 and a short Demo
Views: 14952 CodeCowboyOrg
SQL with Oracle 10g XE - Using DELETE to Remove a Row from a Table
 
03:17
In this video I use the DELETE command to remove an entire row from a table. When using the DELETE command be sure to use the keywords FROM and WHERE to choose the table and record you wish to delete. The code I used is: DELETE FROM BOOKS WHERE BOOK_ID = '11'; This video is part of a series of videos with the purpose of learning the SQL language. For more information visit Lecture Snippets at http://lecturesnippets.com.
Views: 9889 Lecture Snippets
Oracle Partitioning
 
18:12
Oracle Partitioning watch more videos at https://www.tutorialspoint.com/videotutorials/index.htm Lecture By: Mr. Parth Panjab, Tutorials Point India Private Limited
PL/SQL tutorial 16: Table Auditing using DML Triggers in Oracle PL/SQL
 
09:55
Learn Table Auditing using DML Triggers in Oracle Database and Increase the level of security by keep an eye on user tempering your table data. ------------------------------------------------------------------------ ►►►LINKS◄◄◄ Blog : http://bit.ly/table-auditing Previous Tutorial ► DML Triggers with Examples https://youtu.be/-OR7zLzCh_I ► Select-Into Statement: https://youtu.be/F5eMJhwmCQs ►Sysdate Blog : http://bit.ly/sysdate-in-oracle-by-rebellionrider ------------------------------------------------------------------------- ►►►Let's Get Free Uber Cab◄◄◄ Use Referral Code UberRebellionRider and get $20 free for your first ride. ------------------------------------------------------------------------- ►Make sure you SUBSCRIBE and be the 1st one to see my videos! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ►►►Find me on Social Media◄◄◄ Follow What I am up to as it happens on https://twitter.com/rebellionrider https://www.facebook.com/imthebhardwaj http://instagram.com/rebellionrider https://plus.google.com/+Rebellionrider http://in.linkedin.com/in/mannbhardwaj/ http://rebellionrider.tumblr.com/ http://www.pinterest.com/rebellionrider/ You can also Email me at for E-mail address please check About section Please please LIKE and SHARE my videos it makes me happy. Thanks for liking, commenting, sharing and watching more of our videos This is Manish from RebellionRider.com ♥ I LOVE ALL MY VIEWERS AND SUBSCRIBERS
Views: 76141 Manish Sharma
Part 4   Delete duplicate rows in sql
 
03:26
Link for all dot net and sql server video tutorial playlists http://www.youtube.com/user/kudvenkat/playlists Link for slides, code samples and text version of the video http://csharp-video-tutorials.blogspot.com/2014/05/part-4-delete-duplicate-rows-in-sql.html In this video, we will discuss deleting all duplicate rows except one from a sql server table. SQL Script to create Employees table Create table Employees ( ID int, FirstName nvarchar(50), LastName nvarchar(50), Gender nvarchar(50), Salary int ) GO Insert into Employees values (1, 'Mark', 'Hastings', 'Male', 60000) Insert into Employees values (1, 'Mark', 'Hastings', 'Male', 60000) Insert into Employees values (1, 'Mark', 'Hastings', 'Male', 60000) Insert into Employees values (2, 'Mary', 'Lambeth', 'Female', 30000) Insert into Employees values (2, 'Mary', 'Lambeth', 'Female', 30000) Insert into Employees values (3, 'Ben', 'Hoskins', 'Male', 70000) Insert into Employees values (3, 'Ben', 'Hoskins', 'Male', 70000) Insert into Employees values (3, 'Ben', 'Hoskins', 'Male', 70000) The delete query should delete all duplicate rows except one. Here is the SQL query that does the job. PARTITION BY divides the query result set into partitions. WITH EmployeesCTE AS ( SELECT *, ROW_NUMBER()OVER(PARTITION BY ID ORDER BY ID) AS RowNumber FROM Employees ) DELETE FROM EmployeesCTE WHERE RowNumber ] 1
Views: 358331 kudvenkat
Reference partitioning in Oracle 11g
 
09:50
The video explains Reference partitioning and its advantages in Oracle with real project example. It builds on the limitations of reference partitioning in oracle 11g (Interval partitioning) and scenarios where you should implement reference partitioning
Views: 2304 Tech Coach
Difference Between Delete and Truncate
 
03:41
Below is link for Useful Pl/SQL Books http://goo.gl/XMy0tt ---------------------------------------------------------- Difference Between Delete and Truncate in Detail On bigger picture they serve the same purpose but there are many Differences listed with examples Point Delete Truncate 1. Data Recovery Delete: Come under the DML Category, we need to commit or Rollback explicitly to make the changes permanent, so we can recover the data by Rollback command fully with in a session or up to a point if Save Points are used Fall In DDL Category (DDL Command issue the Auto commit implicitly) so no chances of Recovery even not using the Flashback table method. But Truncate operations are also logged , they didn’t generate redo SQL but they are logged , view for truncated data info V$LOGMNR_CONTENTS 2. Data Removal Delete Can remove all or selected data using the Where Clause predicates. Or we can say delete any subset of rows We can Truncate complete table or a partition or sub partition of a table. 3. Speed Delete is Slower because oracle maintain the redo logs for Read Consistency (so that every session connected can see a consistent data at a given point of time ) Delete is very time consuming activity especially when table have numerous indexes and Triggers associated with table Faster as no data logs are maintained no associated trigger firing. 4. DML Triggers Firing DML (Delete) triggers associated with table will fire. DML Trigger will not fire in case of truncate method. 5. Flashback Technology Data can be recovered even after commit operation using Flashback Table options Flashback_transaction_query table will give what to recover and up to which point. Data cannot be recovered in truncate method by Flashback table option. 6. Referential Integrity Constraint Behavior if we don’t have related data in child table then we can delete the data from the parent table or we have variants like On Delete Cascade & on Delete set Null. We can’t truncate a table with enable Referential Integrity Constraint, even there is no data in the child table, we have to disable or drop the constraint if we want to truncate the table. Exception: Truncate is possible if the FK is self-referential means primary key and foreign key are on the same table. 7. Space De allocation or Space Utilization No extent reset with delete when deleting rows from a table, extents are not de allocated, So if there were 50 extents in the table before the deletion, there will still be 50 after the deletion. Truncate: When a table is truncated it will free the space allocated except in case of reuse storage clause. This space can subsequently be used only by new data in the table or cluster resulting from insert or update operations .All extents are de allocated leaving only the extents specified when the table was originally created .Example So if the table was originally created with min extents 3, there will be 3 extents remaining when the tables is truncated. When you truncate a table, NEXT is automatically reset to the last extent deleted. 8. High Water Mark Delete will not reset the high water mark Truncate will reset the High Water mark which is very important for performance point of view as in case of full table scan and full index scan oracle will read all the block under high water mark this makes a lot of difference in terms of performance. 9. Cluster No as such restriction with delete. You cannot individually truncate a table that is part of a cluster. You must truncate the cluster, Delete all rows from the table, or drop and re-create the table. 10. Information Capturing Delete : we can capture the row information what we have deleted using Delete Method, f you are deleting multiple records then use composite data types (collections & records) Truncate Don’t have this feature of capturing the deleted records. 11. Function Based Index Impact DELETE You cannot delete rows from a table if a function-based index on the table has become invalid. You must first validate the function-based index. Truncate: No as such restriction 12. UNUSABLE Indexes Delete no as such feature. Truncate if table is not empty then truncate make all unusable indexes to useable. 13. Complex views You cannot delete data from a Complex view except through INSTEAD OF triggers. But we can delete data from simple Views and MV. We cannot truncate a view simple or complex but you can truncate MV with special Features like Preserve MV Logs and Purge MV Logs. 14. Privileges Delete You need to provide delete table privilege on object. Truncate you must have drop table privilege there is no truncate table privilege exists. 15. Domain Index No as such restriction You cannot truncate the object having domain index in invalid or In progress state
Views: 13804 Ram Gupta
ORACLE SQL AND PL/SQL INTERVIEW QUESTION : DELETE DUPLICATE RECORDS FROM  A TABLE(3 ways)
 
13:15
This video tutorial demonstrates about how we can delete duplicate records from a table, the video shows 3 different ways to delete duplicate record from a table, one using Group by, second one by using correlated joins and third on by using rownum analytical function You can get the code from our website http://oracleplsqlblog.com/FullBlog/FullBlog/21
Views: 5403 Kishan Mashru
What is a PARTITION in Oracle? Why to use Partition And Types of Partitions
 
07:09
Hi guys in my today scenario I'm going to explain with you What is a PARTITION in Oracle? #WhatisPARTITION Why to use Partition And Types of Partitions Oracle database Unbeatable,Unbreakable Platform..
Views: 23656 Oracle World
Oracle PL SQL interview question Difference between SQL LOADER and EXTERNAL TABLE
 
12:34
Oracle PL SQL interview question Difference between SQL LOADER and EXTERNAL TABLE Click here to subscribe to my youtube channel http://www.youtube.com/channel/UCBt6VrxAIb5jLh9HLDcdwtQ?sub_confirmation=1 Oracle Interview questions and Answers at https://www.youtube.com/playlist?list=PLb1qVSx1k1Vr0v4wVyvT3GEuA0J0M4xBm Oracle 18C New features at https://www.youtube.com/playlist?list=PLb1qVSx1k1Vqe06V1tIBcvnslMPWX69bW Oracle 12C New features at https://www.youtube.com/playlist?list=PLb1qVSx1k1VqM1u2IHWzZIgziqejl6wx- Oracle PLSQL records and collections at https://www.youtube.com/playlist?list=PLb1qVSx1k1VpAFTXopXvAh_D3PLcTntdm
Views: 661 Siva Academy
How to delete a Record Table In Oracle database
 
00:57
How to delete a Record Table In Oracle database
Views: 380 latest ITChannel
SQL Tutorial : How To Delete Duplicate Records in SQL Oracle
 
07:43
SQL Tutorial : How To Delete Duplicate Records in SQL Oracle SQL Tutorial SQL Tutorial for beginners PLSQL Tutorial PLSQL Tutorial for beginners PL/SQL Tutorial PL SQL Tutorial PL SQL Tutorial for beginners PL/SQL Tutorial for beginners Oracle SQL Tutorial
Views: 4404 TechLake
Partition Table in SQL Server
 
34:31
Create a filegroup Create a partition function Create a partition scheme Create or modify a table and specify the partition scheme as the storage location. Split Patition
Views: 741 Data Science Center
What is the LIST PARTITION in Oracle ?
 
07:56
Hi guys in my today scenario I'm going to explain with you What is the LIST PARTITION in Oracle, #LISTPARTITIONinOracle Oracle database Unbeatable,Unbreakable Platform..
Views: 3673 Oracle World
ORACLE SQL 31: Delete
 
05:34
http://studyandshare.wordpress.com/ http://www.facebook.com/groups/studyandsharevn/
Views: 471 studyandsharevn
Delete, Drop, and Truncate Statements in Hive
 
06:41
Delete, Drop, and Truncate Statements in Hive Delete Statement: Delete statement is used to delete certain rows from a table based on a condition specified using the WHERE clause. The syntax is in below as highlighted. DELETE FROM tablename [WHERE expression]; Note: DELETE statement is available from hive 0.14.0 version and above Drop Statement: The drop table statement is used to drop the table altogether. The syntax is highlighted here: DROP TABLE IF EXISTS TABLENAME; Let’s quickly see how the managed table and external table differs when dropping the table. We have already discussed this in the “Two different types of hive tables” video. Take a look at it if you haven’t. For the managed hive tables, the drop table statement removes the metadata information as well as the data. The data will be actually moved to .Trash/Current directory. metadata will be permanently deleted. However for external tables, only the metadata information is permanently deleted and the actual data remains intact in the hdfs directories. So in the case of external table, if the table was deleted accidentally, then the table can be re-created by using the same schema and specifying the same location for the hdfs data directory. If deleting the external table was the intention, then after deleting the table using the drop statement, you may also delete the underlying hdfs data directory by issuing the hdfs dfs -rm command. Truncate Statement: Truncate table statement is used to remove only the data from the table. The table structure with the schema remains intact. Truncate removes all the rows from a table or partition. The syntax is highlighted below. TRUNCATE table syntax TRUNCATE TABLE TABLENAME;
Views: 1898 BigDataElearning
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: 10092 Kishan Mashru
ON DELETE CLAUSE OF FOREIGN KEY CONSTRAINT IN ORACLE SQL WITH EXAMPLE (CASCADE/SET NULL/NO ACTION)
 
15:31
This is a free video tutorial explaining about the nonmandatory on delete clause in the foreign key constraint in Oracle SQL database, the video explains about how the on delete set null and on delete cascade works, how it can be implemented with easy to understand examples. We have also shown practical demonstration on creating the foreign key constraint, and then deleting the data from the parent table and observing the changes that take place in the child table.
Views: 1554 Kishan Mashru
Creating and Formatting Partitions with FDISK and mkfs - BASH - Linux
 
08:21
http://filmsbykris.com/wordpress/?p=318 Got Questions? visit http://FilmsByKris.com/forum Chat with us and learn more http://FilmsByKris.com/irc
Views: 111477 Kris Occhipinti
18c Merge Partitions Online
 
06:24
blog: https://connor-mcdonald.com Having trouble merging ? 18c has got you covered.
Views: 406 Connor McDonald
How to Delete Duplicate Record from Table
 
07:08
Hi friends today i will show you a basic sql query to delete th duplicate entry from table usinf rownum & rowid. #DeleteDuplicateRecord Oracle database Unbeatable,Unbreakable Platform..
Views: 2719 Oracle World
How To Create and Drop Tablespace in Oracle 11g
 
09:42
How to Create & Drop a Tablespace
Views: 26865 DbaRocksOracle
How to Delete Duplicate records(rows) in SQL Server
 
10:21
This video shows to find duplicate records and how to delete Duplicate records in a table.This video explains , best 5 methods to delete duplicate records in table.In this way we can maintain Accuracy and consistence of data data in Database .
Views: 40385 Tech Jago
how to delete duplicate row in oracle or pl/sql
 
02:40
simple create new table and store distinct data of old table in new table. after drop old table and rename new table to old table name..........
Views: 18 EasyWay Coding
What is RANGE-LIST SUBPARTITIONS in Oracle via OEM ?
 
14:04
Hi friends today in my videos I'm going to explain with you What is RANGE-LIST SUB-PARTITIONS in Oracle via OEM Oracle database Unbeatable,Unbreakable Platform.. # RANGE-LISTSUBPARTITIONS #OEM
Views: 1474 Oracle World
SQL tutorial 62: Indexes In Oracle Database By Manish Sharma RebellionRider
 
04:03
Watch and learn concepts of SQL Index In Oracle Database. In this tutorial you will learn about B-Tree Index and Function based Index. ------------------------------------------------------------------------ ►►►LINKS◄◄◄ Blog : Previous Tutorial ► ------------------------------------------------------------------------- ►►►Help Me In Getting A Job◄◄◄ ►Help Me In Getting A Good Job By Connecting With Me on My LinkedIn and Endorsing My Skills. All My Contact Info is Down Below. You Can Also Refer Me To Your Company Thanks ------------------------------------------------------------------------- Copy Cloud referral link || Use this link to join copy cloud and get 20GB of free storage https://copy.com?r=kb4rc1 -------------------------------------------------------------------------- ►Make sure you SUBSCRIBE and be the 1st one to see my videos! -------------------------------------------------------------------------- Amazon Wishlist: http://bit.ly/wishlist-amazon ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ►►►Find me on Social Media◄◄◄ Follow What I am up to as it happens on https://twitter.com/rebellionrider https://www.facebook.com/imthebhardwaj http://instagram.com/rebellionrider https://plus.google.com/+Rebellionrider http://in.linkedin.com/in/mannbhardwaj/ http://rebellionrider.tumblr.com/ http://www.pinterest.com/rebellionrider/ You can also Email me at for E-mail address please check About section Please please LIKE and SHARE my videos it makes me happy. Thanks for liking, commenting, sharing and watching more of our videos This is Manish from RebellionRider.com ♥ I LOVE ALL MY VIEWERS AND SUBSCRIBERS
Views: 42818 Manish Sharma
part 8 drop partition
 
01:18
drop partition alter table data warehousing
PART 5 ADD PARTITION
 
02:35
alter table, data warehousing add partition, range partition
Views: 1073 Dr. Girija Narasimhan

Top songs by moss
New oromo music tadele gemechu la
Aye mere dost laut ke aaja instrumental music youtube
Alex ubago aunque no te pueda ver instrumental music
Study music business in new york