How To Create Tablespace In Oracle Database 19c By Manish Sharma

though i am using oracle database 19c but you can follow these steps for any version from oracle 12c as the steps are the same i will try to keep this tutorial simple quick and crispy in short to the point so without wasting much of your time let's start the tutorial to create a tablespace in oracle database we will need to write a create table statement let's write that i just connected myself with the root container of oracle database 19c using sysuser which says db privileges let's clear the screen with this statement we have decided that the name of our tablespace is going to be rmn underscore backup relax the statement is not over yet next we will add a data file to this tablespace since a data file is a physical file thus i have decided to save it into the directory named oracle backup i have already created this directory let's do that make sure that the file name must end with the extension dot dbf if possible try to add the number at the end of the file name believe me it's really helpful especially during media recovery next we will decide the size of this data file for the demonstration 100 mb would be sufficient for me next we set the auto extend clause after this clause the size of data file will auto extend with another 100 mb when it is completely filled now we will set the maximum size for the tablespace with max size clause i set the maximum size of the tablespace to 1gb next we will make it a locally managed tablespace by setting the extend management to local i set the extend management clause to local and segment space management to auto let me explain what i just did here i'll promise i'll make it quick but first let's hit the enter and create this tablespace in oracle database 19c tablespace created now let's get to the explanation part in oracle ecosystem all the data is stored into files called data files since data files are stored somewhere on your hard disk drive thus they have their own file extension which is dbf to manage the storage space of these data files oracle uses three units they are blocks extends and segments let's talk about blocks the smallest unit at which oracle stores data is called blocks also known as pages one data block corresponds to a specific number of bytes of physical database space on disk let's find out how many bytes create a data block in oracle database 19c since data block size is different for all the table spaces thus we are going to check for the one that we are currently creating so let's do that so the block size for the tablespace admin underscore backup is eight one nine two bytes and then comes extends extends are a collection of a contiguous data blocks that oracle database uses for storing specific information extents are either managed locally or are dictionary based segments just like extend is a collection of data blocks similarly segment are a collection of extents each of which are allocated for a specific data structure and all of which are stored in the same table space in contrast to managing extent locally managing extents automatically is much simpler and a more efficient process that is why we set the clause segment space management to auto now that things have been cleared let's come back to our code the only thing which is left in this code is the last clause which is online this clause made our tablespace armament underscore backup available as soon as we hit that enter key and created the stable space hope you enjoyed watching if so then give this video a thumbs up otherwise thumbs down but make sure to subscribe to the channel if you haven't already because in coming week i will be doing more such interesting tutorials on database backup and recovery and yes one more thing make sure to press that bell icon to get notified every time i upload a new video thanks for watching this is manish from rebellionrider.com

Channel: Manish Sharma

Views: 16,178

Keywords: manish sharma, rebellion rider, oracle ace, oracle ace manish sharma, oracle database 19c, oracle 19c, tablespace in oracle 19c, how to create tablespace in oracle database 19c, how to create tablespace in oracle 19c, how to create tablespace in oracle db, oracle database 19c tutorial

Id: jEZ9x9mH_8Y

Length: 5min 27sec (327 seconds)

Published: Sun Jun 20 2021