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: 7125 BhagyaRaj Katta
Why and How we need to rebuild global Index in Oracle 11g
Views: 3400 Athar Fahad
This Video will teach you How to Move Object one Table Space to Another | How to Rebuild the Index ? move table from one tablespace to another in oracle 11g oracle move schema to another tablespace oracle how to move objects to another tablespace oracle 11g move schema to another tablespace alter table move tablespace oracle 8i oracle move table script oracle move cluster to new tablespace oracle move table example rebuild index oracle script alter index rebuild online parallel oracle rebuild all indexes oracle index rebuild online vs offline oracle rebuild partitioned index index rebuild oracle best practice index rebuild script in oracle 11g
Views: 1088 Oracle PL/SQL World
Ссылка на файл с презентацией: https://docs.google.com/presentation/d/1hqsC6hEGybAbEEPko56razF7eLBJQEZ3L-ECWtp7rbM/edit?usp=sharing (презентация может быть с анимацией) Ссылка на краткий конспект лекции:https://docs.google.com/document/d/1UFayi0YIXxLrRuj4-JZj-jooyvpoa4rKLOryBloTtag/edit?usp=sharing 1. Create table: permanent tables, temporary tables (on commit delete/preserve rows) 2. Physical properties таблицы: tablespace, logging, pctfree, pctused, initrans 3. Pctfree и pctused 4. Initrans и maxtrans 5.Storage clause: initial, next, minextents, maxextents, pctincrease, freelists, freelist groups, optimal, buffer_pool (keep,recycle), flash_cache (keep, none), encrypt 6. Table properties: column_properties, cache, result_cache, enable_disable_clause, flashback_archive_clause 7. Alter table: column_clause, constraint_clause, alter_table_partitioning, move_table_clause, enable_disable_clause, rename to, shrink space 8. High water mark. Low high water mark 9. Create index: table index clause, index_expr, index_properties, unusable 10. Reverse indexes 11. Function based indexes 12. Alter index: сжатие (shrink space), параллелизм, физические атрибуты (pctfree, pctused, initrans), logging/nologging, rebuild, enable/disable, unusable, visible/invisible, rename to 13. Индекс по внешнему ключу 14. Причины неиспользования индексов 15. Оператор truncate 16. Неявный commit при выполнении ddl 17. Словари данных Oracle Database, БД Oracle, вебинар Oracle, презентация Oracle, урок Oracle, лекция Oracle, обучение Oracle
Views: 4186 Moscow Development Team
Hello Today I am showing you how to create tablespace. Alter tablespace datafile using fileid & filename and resize datafile. How to check Tablespace Size. How to check datafile Size in MB. How to drop Tablespace including contents and all Datafiles of these tablespace.
Views: 1822 Parag Mahalle
https://dbagenesis.com/p/oracle-11g-advanced-dba Oracle tablespace administration - Learn how to work on tablespaces in real-time. - Tablespace management tasks - Tablespace utilization - Add space to tablespace - Add new tablespace - Tablespace best practices - Drop tablespace A tablespace is constructed by Oracle from one or more operating system files through the use of the CREATE TABLESPACE command. Every Oracle database consists of at least one tablespace, called SYSTEM.
Views: 15598 DBA Genesis
How to See Schema Size, Used Space, Remaining Space, Tables, Table Sizes Using SQL
Views: 841 Raihan Shafique
Need to reorganize a table ? Want to do it whenever you want without impacting any users ? Easy in 12.2 ====================================================== Copyright © 2017 Oracle and/or its affiliates. Oracle is a registered trademark of Oracle and/or its affiliates. All rights reserved. Other names may be registered trademarks of their respective owners. Oracle disclaims any warranties or representations as to the accuracy or completeness of this recording, demonstration, and/or written materials (the “Materials”). The Materials are provided “as is” without any warranty of any kind, either express or implied, including without limitation warranties or merchantability, fitness for a particular purpose, and non-infringement.
Views: 437 Connor McDonald
steps to relocate a table to a new tablespace
Views: 3088 Wysheid Wysheid
Learn (The easiest way)how to create tablespace in oracle database using Oracle SQL Developer GUI: SQL Developer trips and tricks tutorial ------------------------------------------------------------------------ ►►►LINKS◄◄◄ Blog : http://bit.ly/tablespace-using-sql-developer Previous Tutorial ► How to make SYS user Connection: http://bit.ly/how-to-create-new-connection-in-sql-developer ► Permanent tablespace https://youtu.be/vkwKZIssvfc ► Temporary Tablespace https://youtu.be/Hc_OXitKgWM ► Undo Tablespace https://youtu.be/HlSrHX7ffbI ------------------------------------------------------------------------- ►►►Let's Get Free Uber Cab◄◄◄ Use Referral Code UberRebellionRider and get $20 free for your first ride. ------------------------------------------------------------------------- ►►►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: 32584 Manish Sharma
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: 13570 Ram Gupta
In this video you will learn about data compression options using ‘what if’ report analysis in CA Datacom® /DB, featuring compression with CA Datacom Presspack for z/OS systems. Music adapted from “Ourobouros” Kevin MacLeod (incompetech.com). Licensed under Creative Commons: By Attribution 3.0. http://creativecommons.org/licenses/by/3.0/
Views: 53 Educate
Video tutorials on how Manage Oracle Tablespaces using Oracle Enterprise Manager 12C For more Database Administration articles and scripts go to www.aodba.com More Articles, Scripts and How-To Papers on http://www.aodba.com
Views: 7564 AO DBA
Want to selectively hide or scramble sensitive data? In this video, I'll show you how to use a new feature/tool in SQL Server 2016, called Dynamic Data Masking, that allows you to partly obfuscate certain columns of data in a table or view. -- Hey, if you enjoyed this video, please SUBSCRIBE to HandyAndy Tech Tips! -- My notes on this topic: - Now, to add the masking, you COULD define the masking in the table definition statement, but I'll use the ALTER TABLE statement. Anyway, the basic syntax is the same: MASKED WITH (FUNCTION = ''). The string can basically be one of four functions. - The easiest is default() - everything is automatic. It: ---- on a TEXT FIELD, replaces the original value of the field with 4 X's. ---- on a DATE FIELD, set s the date to 1900-01-01 00:00:00.00 ---- on a NUMERIC FIELD, sets the value to 0. - email() only shows the first letter, and replaces the remainder of the field with X's, an @ symbol, and a standard .com suffix. It, of course, also works on fields that don't include actual email addresses - so it therefore works on any VARCHAR or CHAR. - partial() - When used as partial(no-of-chars-to-reveal-before, padding, chars-to-reveal-after), it reveals the first character and the last two, separated by a string of X's. This also only works on strings - not on numeric types or dates. - random(lower-bound, upper-bound) replaces the field's original value with a randomly-generated number between 1 and 10. This, of course, only works with numeric data types like INT. - Now, it's worth noting that if you want to give a particular user - or role - the ability to see all of the data in its unmasked form, then you can simply grant them the UNMASK privilege. Annoyingly, this doesn't seem to work on a table level, only for the entire database.
Views: 3108 HandyAndy Tech Tips
http://store.markeedragon.com/affiliate.php?id=4&redirect=index.php?cat=4 Special Viewer Discount or Bonus. YOUR CHOICE! Want a bonus on your EVE new account or Plex? Use the discount code of "discount" and get 3% off your order. Or want 3.3% cash back for even more savings? Use bonus code "bonus" and get 3.3% credit in your account for future purchases This is for a limited time and the discount/bonus codes may be changed or removed at any time. The discount / bonus is provided by Markee Dragon Game Codes and we are an authorized CCP reseller. Codes delivered in 20 minutes or less. Want to Try EVE for free? Get it here: http://secure.eveonline.com/signup/?invc=d6baec26-231d-4ced-9cd2-1a8b3713d72d&action=buddy [!nojob] Want to get rid of your day job and be your own boss? Markee Dragon is sharing how he does it with a step by step guide to independence. Get it here: http://jedimarketingtricks.com/ultimate/ Join me and your favorite streamers! Put my 10+ years of experience to work for YOU. Learn how to start generating income while playing your favorite games! I include everything you need to know to get started PLUS plenty of support for when you get stuck! We do a monthly giveaway and you can get a free entry for it here: http://store.markeedragon.com/affiliate.php?id=4&redirect=index.php?cat=18 No purchase required. Join us for chat in Discord https://discord.gg/markeedragon Discord is what we use for in game chat and voice comms. This is a simulcast of http://twitch.tv/markeedragon . You can watch here live on YouTube and talk in chat. but for the giveaways mentioned on the show those currently only work in Twitch chat. You do not have to watch on Twitch. You only need to be in the Twitch chat to get in on the giveaways. WTFast is what I use to improve my connection to EVE. I get at least a 20% improvement at all times. Try it here: http://www.wtfast.com/markeedragon Videos How to convert Loyalty Points This video shows how we decided what items to use. Items Sold. https://www.youtube.com/watch?v=rnv4eW9hwP0 What worked Successful conversion of 1m LP to 1.5b ISK in 13 days. https://www.youtube.com/watch?v=eiGEj4XhKt0 Hauling Introduction https://www.youtube.com/watch?v=7NjY9aU-uBQ Hauling is a great secondary income. Market Blue Line Hauling is a great secondary income. EVE Sites Mentioned on the Show Moose Army Corp - My Null Corp I am a member of. http://moose.army Airhogs - The best all around corp and great for new players. I am also a member http://docs.google.com/document/d/1cCPjgTfOxMN7JpagSs5iUCyES2OalT5j6O4zaP5qVNw/edit?usp=sharing LP Store Conversion See what LP items are currently worth on the market. https://www.fuzzwork.co.uk/lpstore/ Daopa's LP Stores Database LP store items information http://www.ellatha.com/eve/LP-Stores EVE Central Current values for most all items in game. http://eve-central.com/ EVE Assets Manager Find your stuff. Know where your money is sitting! http://eve.nikr.net/jeveasset EVE Markets Market history data. http://eve-markets.net/ EVEPraisal Quick values for your loot and other market actions. http://evepraisal.com/ EVE Maps All kinds of map related information. http://evemaps.dotlan.net/ Deepsafe https://afowl.si/deepsafe Excellent crowed sourced information on cosmic signatures. All explorers should use this. EVE University EVE Wiki http://wiki.eveuniversity.org/Main_Page Live shows Schedule: https://docs.google.com/spreadsheets/d/1zQZoKQnzGRgWXBefzGKlyfDppGQWzJy6PafWm-4c4sQ/pubhtml Music by Monstercat http://www.monstercat.com
Views: 1548 Markee Dragon Gameplay
My advice is this: Settle! That's right. Don't worry about passion or intense connection. Don't nix a guy based on his annoying habit of yelling "Bravo!" in movie theaters. Overlook his halitosis or abysmal sense of aesthetics. Because if you want to have the infrastructure in place to have a family, settling is the way to go. Based on my observations, in fact, settling will probably make you happier in the long run, since many of those who marry with great expectations become more disillusioned with each passing year. (It's hard to maintain that level of zing when the conversation morphs into discussions about who's changing the diapers or balancing the checkbook.) Obviously, I wasn't always an advocate of settling. In fact, it took not settling to make me realize that settling is the better option, and even though settling is a rampant phenomenon, talking about it in a positive light makes people profoundly uncomfortable. Whenever I make the case for settling, people look at me with creased brows of disapproval or frowns of disappointment, the way a child might look at an older sibling who just informed her that Jerry's Kids aren't going to walk, even if you send them money. It's not only politically incorrect to get behind settling, it's downright un-American. Our culture tells us to keep our eyes on the prize (while our mothers, who know better, tell us not to be so picky), and the theme of holding out for true love (whatever that is—look at the divorce rate) permeates our collective mentality. Even situation comedies, starting in the 1970s with The Mary Tyler Moore Show and going all the way to Friends, feature endearing single women in the dating trenches, and there's supposed to be something romantic and even heroic about their search for true love. Of course, the crucial difference is that, whereas the earlier series begins after Mary has been jilted by her fiancé, the more modern-day Friends opens as Rachel Green leaves her nice-guy orthodontist fiancé at the altar simply because she isn't feeling it. But either way, in episode after episode, as both women continue to be unlucky in love, settling starts to look pretty darn appealing. Mary is supposed to be contentedly independent and fulfilled by her newsroom family, but in fact her life seems lonely. Are we to assume that at the end of the series, Mary, by then in her late 30s, found her soul mate after the lights in the newsroom went out and her work family was disbanded? If her experience was anything like mine or that of my single friends, it's unlikely. And while Rachel and her supposed soul mate, Ross, finally get together (for the umpteenth time) in the finale of Friends, do we feel confident that she'll be happier with Ross than she would have been had she settled down with Barry, the orthodontist, 10 years earlier? She and Ross have passion but have never had long-term stability, and the fireworks she experiences with him but not with Barry might actually turn out to be a liability, given how many times their relationship has already gone up in flames. It's equally questionable whether Sex and the City's Carrie Bradshaw, who cheated on her kindhearted and generous boyfriend, Aidan, only to end up with the more exciting but self-absorbed Mr. Big, will be better off in the framework of marriage and family. (Some time after the breakup, when Carrie ran into Aidan on the street, he was carrying his infant in a Baby Björn. Can anyone imagine Mr. Big walking around with a Björn?)
Views: 202890 Shari Wing