Home
Search results “Alter database oracle datafile”
RECOVER LOST  DataFile in oracle database
 
15:33
Please donate on below link if you think I am helping you with your career. https://www.paypal.me/ankushthavali OR Google Pay : 9960262955 OR Account No : 31347845762 IFSC: SBIN0012311 DATAFILE file Lost ================================ Archive log mode-Enabled create tablespace test_ts datafile '/u01/app/oracle/oradata/testdb/ts_test02.dbf' size 10M; create table testtable (id number(5))tablespace test_ts; insert into testtable (id) select * from testtable; SQL>select count(*) from testtable; COUNT(*) ---------- - before deleting datafile 524288 SQL>select count(*) from testtable; COUNT(*) ---------- - After recovery 524288 select tablespace_name, segment_name from dba_segments where segment_name = 'TESTTABLE'; alter system flush buffer_cache; alter database create datafile '/u01/app/oracle/oradata/testdb/ts_test02.dbf' as '/u01/app/oracle/oradata/testdb/ts_test02.dbf' alter database datafile '/u01/app/oracle/oradata/testdb/ts_test02.dbf' offline drop; alter database create datafile '/u01/app/oracle/oradata/testdb/ts_test02.dbf' as '/u01/app/oracle/oradata/testdb/ts_test02.dbf' recover datafile '/u01/app/oracle/oradata/testdb/ts_test02.dbf'; alter database datafile '/u01/app/oracle/oradata/testdb/ts_test02.dbf' online; select count(*) from TESTTABLE; =================================================== Please like,subscribe my channel. Kindly contact for online training on below contacts. Emai: [email protected] Mob No: +91 9960262955 ====================================================== Hi Folks, Greetings!! I am professional part time oracle dba trainer and having good experience in oracle database administration. This is the right place to enroll yourself for Oracle DBA real time course. Please use following email ID for more details. Don’t hesitate to WhatsApp me any time. I will give you the best training and will create path to get DBA job easily. Enjoy the free YouTube video and connect with me for deep Oracle DBA knowledge. Mobile No : + 91 9960262955 (WhatsApp Number) EmailID: [email protected] how to recover lost datafile in oracle how to recover a datafile in oracle without backup how to recover a deleted datafile in oracle 11g how to recover datafile in oracle rman restore datafile oracle missing datafile after clone how to recover system datafile in oracle without backup oracle datafile deleted accidentally
Views: 1092 ANKUSH THAVALI
Creating  Managing  Droping Oracle Database Tablespaces
 
12:10
Creating Managing Droping Oracle Database Tablespaces select tablespace_name,block_size,status,contents,logging from dba_tablespaces; create tablespace mytbs datafile 'd:\oradata\dbrdbms\mytbs01.dbf' size 10m; alter tablespace mytbs add datafile 'd:\oradata\dbrdbms\mytbs02.dbf' size 10m; alter tablespace mytbs drop datafile 'd:\oradata\dbrdbms\mytbs02.dbf'; desc v$datafile; desc ts#,name from v$datafile; select ts#,name from v$tablespace; select v$datafile.ts#,v$datafile.name,v$tablespace.name from v$datafile,v$tablespace where v$datafile.ts#=v$tablespace.ts# and v$tablespace.name='MYTBS'; alter database datafile 'd:\oradata\dbrdbms\mytbs01.dbf' resize 20m; ALTER DATABASE DEFAULT TABLESPACE mytbs; select ts#,name from v$tablespace; select ts#,name from v$datafile; select ts#,name from v$tablespace; drop tablespace mytbs; drop tablespace mytbs ialtncluding contents and datafiles; drop tablespace mytbs including contents and datafiles cascade; alter tablespace mytbs read only; alter tablespace mytbs read write; alter tablespace mytbs force logging; alter tablespace mytbs nologging; alter tablespace mytbs flashback on; alter tablespace mytbs flashback off; purge tablespace mytbs user myuser;
Views: 21177 Abbasi Asif
How to Create, Alter, Resize, Drop Tablespace (Tablespace Management #1)
 
07:37
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: 1335 Parag Mahalle
How to Rename datafile in Oracle database
 
08:05
How to Rename datafile in Oracle ====================================== How to Rename datafile ====================================== select FILE_NAME,TABLESPACE_NAME,ONLINE_STATUS from dba_data_files; select status,instance_name,database_role,protection_mode from v$database,v$instance; alter database datafile '/opt/oracle/oradata/prim/users_test_after_rename.dbf' offline; server: mv old_data_file new_data_files alter tablespace USERS rename datafile '/opt/oracle/oradata/prim/users_test' to '/opt/oracle/oradata/prim/users_test_after_rename.dbf'; alter database datafile '/opt/oracle/oradata/prim/users_test_after_rename.dbf' online; how to move datafile from one location to another in oracle 11g alter database rename datafile file_id alter database rename datafile script how to move datafile in oracle standby database oracle move system datafile how to move datafile from one location to another in oracle 12c alter database rename file 1 to alter database move datafile 12c asm
Views: 200 ANKUSH THAVALI
21. Alter Tablespace in Oracle
 
06:33
In this video you will learn how to learn tablespace to add new datafile,or add existing datafile in tablespace. You will also learn how to list available tablespace and datafile in oracle database engine. At the end you will learn how to drop tablespace. For Support =========== Email: [email protected] Contact Form: http://www.learninhindi.com/home/contact Our Social Media ================ Facebook: https://www.facebook.com/LearnInHindi Twitter: https://twitter.com/LearnInHindi For Training & Videos ===================== For more videos and articles visit: http://www.learninhindi.com Free Java Programming In Hindi Course ===================================== https://www.youtube.com/playlist?list=PLOZ3jentlCDAwwhMyoLISrxkXTADGp7PH Free Oracle PL/SQL Programming In Hindi Course ============================================== https://www.youtube.com/playlist?list=PLB5DA82419C2D99B6 Free C Programming In Hindi Course ================================== https://www.youtube.com/playlist?list=PLOZ3jentlCDAxKpBLMWogxSdy6BZcsAJq Trips & Tricks Channel ====================== https://www.youtube.com/channel/UCGmLfkuCo-3lHHJXRJ9HUMw Programming in Hindi Channel ============================ https://www.youtube.com/channel/UCudElIDgwNrybeAvXIy1HZQ
Views: 25700 ITORIAN
TABLESPACE MANAGEMENT CREATE TABLESPACE ALTER DATAFILE
 
03:34
How to check existing tablespace information in database ========================================================= select tablespace_name from dba_tablespaces; How to create a tablespace =============================== create tablespace king datafile 'E:\DATABASE\DATABASE\ORADATA\ORCL\KING01.DBF' size 10m; How to Resize the datafile ========================== alter database datafile 'E:\DATABASE\DATABASE\ORADATA\ORCL\KING01.DBF' resize 20m; Adding a datafile to a tablespace ================================= alter tablespace king add datafile 'E:\DATABASE\DATABASE\ORADATA\ORCL\KING02.DBF' size 10m; How to get the defintion of tablespace ====================================== set head off echo off select 'select dbms_metadata.get_ddl(''TABLESPACE'',''' || tablespace_name || ''') from dual;' from dba_tablespaces;
Views: 869 Praveen Biyyapu
Managing Database Structure -Tablespaces and Datafiles part 1st
 
21:55
Hi friends in this video i am going to explain you how to create alter tablespace and datafile in oracle. #ManagingDatabaseStructure #TablespacesandDatafiles Oracle database Unbeatable,Unbreakable Platform..
Views: 11081 Oracle World
TABLESPACE MANAGEMENT RENAME OR MOVE A DATAFILE
 
04:40
RENAME OR MOVE A DATAFILE (when there is space issue in os level file movement is required) ========================== 1)TABLESPACE SHOULD BE OFFLINE(when we move or rename a datafile users should not access) 2)FROM OS LEVEL DATAFILE COPY TO NEW LOCATION 3)SQL LEVEL RENAME THE DATAFILE TO NEW LOCATION 4)MAKE THE TABLESPACE ONLINE alter tablespace king offline; os level rename king01.dbf TO GOODKING01.dbf os level move the file to new location(KING02.DBF) alter tablespace king rename datafile 'E:\DATABASE\DATABASE\ORADATA\ORCL\KING01.DBF' to 'E:\DATABASE\DATABASE\ORADATA\ORCL\GOODKING01.DBF'; alter tablespace king rename datafile 'E:\DATABASE\DATABASE\ORADATA\ORCL\KING02.DBF' TO 'D:\ORADATA\KING02.DBF'; alter tablespace king online; HOW TO CHECK DATAFILES INFO IN PARTICULAR TABLESPACE ===================================================== COL FILE_NAME FOR A50 COL TABLESPACE_NAME FOR A20 select tablespace_name,file_name from dba_data_files where tablespace_name like '&tablespace_name';
Views: 778 Praveen Biyyapu
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: 6770 BhagyaRaj Katta
How to Move a Datafile in Oracle Database - Part 1
 
01:32
Dozens of free Oracle tutorials at http://skillbuilders.com/free-oracle-tutorials. See Part 2 (the demonstration) of this tutorial at http://skillbuilders.com/How-to-Move-Datafiles-Oracle-12c. How do you move a datafile in an Oracle Database? Well, it just got a lot easier in 12c! Watch this free video tutorial - OCM John Watson of SkillBuilders will demonstrate both techniques, 11g and 12c. There are many reasons to move a datafile in an Oracle database. Here are just a few: Renaming datafiles to a standard Moving from one file system to another Converting from file system storage to ASM Changing from SAN to NFS storage In 11g, we need to take the tablespace offline, meaning downtime. Argh! In 12c, all that is required is ALTER DATABASE MOVE DATAFILE; this is an online operation with zero downtime. Even "critical" files such as the files associated with the SYSTEM tablespace can be moved online. Consider the ease of migrating to ASM this provides! This also functions in an Active Data Guard standby environment. And, there are syntax options that allow you to keep or delete the original file. Very cool stuff.
Views: 2514 SkillBuilders
OracleTablespace Administration | Bigfile tablespace in Oracle - Creation and Management
 
08:42
This video is the part of Oracle Database Self learn Kit From Wysheid. In this video we discuss about the creation and management of bigfile tablespaces in an Oracle database
Views: 2338 Wysheid Wysheid
Rename Tablespace ( Tablespace Management #3 )/* Step By Step /*
 
06:23
Hello Friends In this video I am showing you 1. How to check Tablespace, Datafile size and Datafile Location. 2. How to Rename Tablespace. 3. How to Offline Tablespace. 4. How to Online Tablespace. 5. How to Rename Datafile. 6. How to Check Tablespace. (How many tablespace Do you Have). 7. How to check Tablespace Status. It is Offline or online. 8. How to Rename Old Datafile name to New Datafile Name in controlfile.
Views: 135 Parag Mahalle
Add Datafile to Tablespace
 
14:55
Add extra datafile to exixting tablespace, how to drop the extra datafile, how to reuse a datafile, how to make a tablespace offline n online.
Views: 11980 DbaRocksOracle
TABLESPACE MANAGEMENT DROP TABLESPACE REUSE DATAFILE DROP INCLUDING CONTENTS AND DATAFILES
 
03:48
HOW TO DROP TABLESPACE ====================== drop tablespce king; (datafile will not delete from o/s level) create tablespace king datafile 'E:\DATABASE\DATABASE\ORADATA\ORCL\KING01.DBF' reuse;(no need to give size) drop tablespace king including contents and datafiles;(datafile will delete from o/s level) newly created tablespace king with king01 datafile (got deleted from o/s level)
Views: 517 Praveen Biyyapu
Recover PDB DataFile or Tablespace
 
07:38
Restore and Recover a datafile or a tablespace in a Pluggable Database
Views: 66 Hemant K Chitale
How to Add & Drop Datafile in Tablespace. ( Tablespace Management #2 )
 
06:46
Hello Friend In this video I am showing you 1. How to add datafile. 2. How to drop Datafile. 3. How to alter Tablespace. 4. How to check Datafile Location. 5. How to check free space in tablespace.
Views: 190 Parag Mahalle
Oracle DBA | How To Enlarge The Database | Bangla | DBA1-16
 
07:09
Oracle DBA | How To Enlarge The Database | Bangla | DBA1-16 To See More DBA part-01 Tutorial Click here https://www.youtube.com/playlist?list=PLXXvCMuixAJUO3yQq7-LLdfQb5HpsQKNg To See More DBA part-02 Tutorial Click here https://www.youtube.com/playlist?list=PLXXvCMuixAJUviefN3-ZzKDVgbIH0RoaS To See More Reports Tutorial Click here https://www.youtube.com/watch?v=y_0f_iILlmY&list=PLXXvCMuixAJUfU4ZISmPtHrmKe5qRvyVY To See More Forms Tutorial Click here https://www.youtube.com/watch?v=Zsu4CWo_vwk&list=PLXXvCMuixAJU3Ap_FE2zSEBicv8IC5kDU Dear viewer, In this tutorial you will know How To Enlarge The Database. Ways of Enlarging The Database: You can enlarge the database in the following ways: -Creating a new tablespace -Adding a data file to an existing smallfile tablespace -Increasing the size of a data file -Providing for the dynamic growth of a data file. Increasing the size of a data file: Command: sql- alter database datafile 'E:\app\Administrator\oradata\orcl \dfile1.dbf' resize 500m; Providing for the dynamic growth of a data file: sql- create tablespace test3 datafile 'E:\app\Administrator\oradata\orcl\dfile3.dbf' size 300m autoextend on online extent management local uniform size 5m To Get more Tutorial Subscribe The Channel click here https://www.youtube.com/channel/UCQ3ea4zVCfnKBx1_7h8raGQ?sub_confirmation=1 To Get All Post Like The Facebook Page Click here https://www.facebook.com/oracledbsolutions Follow us: Facebook: https://www.facebook.com/oracledbsolutions Youtube link: https://www.youtube.com/channel/UCQ3ea4zVCfnKBx1_7h8raGQ?view_as=subscriber Facebook Group: https://www.facebook.com/groups/465557066961548 Contact us: email: [email protected] Skype: oracle26solutions Mob: 01734557080, 01627422780
Views: 79 OracleDB Solutions
How to move a Datafile in Oracle Database 12c - part 2
 
01:26
How do you move a datafile in an Oracle Database? Well, it just got a lot easier in 12c! Watch this free video tutorial where OCM John Watson of SkillBuilders will demonstrate both techniques, 11g and 12c. http://skillbuilders.com/free-oracle-tutorials There are many reasons to move a datafile in an Oracle database. Here are just a few: Renaming datafiles to a standard Moving from one file system to another Converting from file system storage to ASM Changing from SAN to NFS storage In 11g, we need to take the tablespace offline, meaning downtime. Argh! In 12c, all that is required is ALTER DATABASE MOVE DATAFILE; this is an online operation with zero downtime (in 11g we use ALTER DATABASE RENAME DATAFILE). Even "critical" files such as the files associated with the SYSTEM tablespace can be moved online. Consider the ease of moving to ASM this provides! This also functions in an Active Data Guard standby environment. And, there are syntax options that allow you to keep or delete the original file. Very cool stuff.
Views: 504 SkillBuilders
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: 41194 Abbasi Asif
Oracle Database tablespace,  Datafile and control files
 
22:43
Oracle tablespace are logical containers of datafiles. Datafiles are physical files which contains data stored in binary format. Control file contains information about the database and the datafile location as well as checkpoint information
Views: 170 joey de jesus
Managing Database Structure -Tablespaces and Datafiles part 2nd
 
13:18
Hi friends in this video i am going to explain you how to create alter tablespace and datafile in oracle. #ManagingDatabaseStructure #TablespacesandDatafiles Oracle database Unbeatable,Unbreakable Platform..
Views: 1703 Oracle World
Quickfix: Tablespace full to resize tablespace
 
02:11
Quickfix is a demonstration on how to fix a spesific database issue. In this video we detect that a tablespace is full and go through the steps to resize the tablespace.
Views: 1659 dbWatch As
RENAME DATAFILE - ORACLE
 
09:17
In this video I explain in detail how to change the datafile to a tablespace .. Like !!!
Views: 602 FULL-ORACLE
Oracle DBA Justin - How to reset the log sequence number in an Oracle database
 
10:39
How to reset the log sequence number in an Oracle database
Views: 5114 jbleistein11
when checkpoint will occur in oracle database
 
14:13
How to check current_scn number of database? ============================================= select CURRENT_SCN from v$database; select dbms_flashback.get_system_change_number from dual; select timestamp_to_scn(sysdate) from dual; how to check datafile header scn number? ========================================= select name,CHECKPOINT_CHANGE# from v$datafile_header; when checkpoint will occur? ============================ alter system switch logfile; alter database begin backup; alter database end backup; alter system checkpoint;(manual checkpoint) shutdown immediate;(when the database is down) what will happen when checkpoint occurs? ========================================= scn of datafile header will update;
Views: 3542 Praveen Biyyapu
TABLESPACE MANAGEMENT DATAFILE SIZE ENABLE DISABLE AUTOEXTEND ON
 
02:50
HOW TO ENABLE/DISABLE AUTOEXTEND ON FOR A DATAFILE ================================================== alter database datafile 'E:\DATABASE\DATABASE\ORADATA\ORCL\GOODKING01.DBF' autoextend on; HOW TO FIND THE SIZE OF A DATAFILE ================================== SELECT SUBSTR (df.NAME, 1, 50) file_name, df.bytes / 1024 / 1024 allocated_mb, ((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) used_mb, NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb FROM v$datafile df, dba_free_space dfs WHERE df.file# = dfs.file_id(+) GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes ORDER BY file_name; set lines 180 pages 200 col tablespace_name for a20 col file_name for a55 select tablespace_name,file_name,autoextensible from dba_data_files;
Views: 1189 Praveen Biyyapu
Renaming Tablespace & Datafile
 
08:28
Video to Rename a Tablespace and its datafile
Views: 1826 DbaRocksOracle
20. Tablespace and Datafile in Oracle
 
04:34
In this video you will learn about Tablespaces and Datafiles in detail. We are going to cover something theoretically here because you will be using it frequently in next videos so it is good to watch this before proceeding. For Support =========== Email: [email protected] Contact Form: http://www.learninhindi.com/home/contact Our Social Media ================ Facebook: https://www.facebook.com/LearnInHindi Twitter: https://twitter.com/LearnInHindi For Training & Videos ===================== For more videos and articles visit: http://www.learninhindi.com Free Java Programming In Hindi Course ===================================== https://www.youtube.com/playlist?list=PLOZ3jentlCDAwwhMyoLISrxkXTADGp7PH Free Oracle PL/SQL Programming In Hindi Course ============================================== https://www.youtube.com/playlist?list=PLB5DA82419C2D99B6 Free C Programming In Hindi Course ================================== https://www.youtube.com/playlist?list=PLOZ3jentlCDAxKpBLMWogxSdy6BZcsAJq Trips & Tricks Channel ====================== https://www.youtube.com/channel/UCGmLfkuCo-3lHHJXRJ9HUMw Programming in Hindi Channel ============================ https://www.youtube.com/channel/UCudElIDgwNrybeAvXIy1HZQ
Views: 30247 ITORIAN
Oracle - CLEAR CORRUPTED LOGFILE
 
22:25
STARTUP MOUNT; ALTER DATABASE DROP LOGFILE GROUP 1; ALTER DATABASE CLEAR LOGFILE GROUP 1; ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;
Views: 535 Dario Rafael
How to work with Tablespaces in Real-Time? Tablespace Administration
 
01:00:59
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: 14707 DBA Genesis
TABLESPACE MANAGEMENT BIGFILE TABLESPACE RESIZING TABLESPACE TEMPORARY TABLESPACE GROUP RENAMING TAB
 
03:52
HOW TO RENAME A TABLESPACE =========================== alter tablespace king rename to king1; 10G FEATURES ON TABLESPACES 1)HOW TO CREATE BIGFILE TABLESPACE ================================== create bigfile tablespace king_big datafile 'E:\DATABASE\DATABASE\ORADATA\ORCL\king_big.dbf' size 20m; 2)HOW TO RESIZE A BIGFILE TABLESPACE ==================================== alter tablespace king_big resize 40m; 3)HOW TO CREATE TEMPORARY TABLESPACE GROUP ========================================== create temporary tablespace king_temp tempfile 'E:\DATABASE\DATABASE\ORADATA\ORCL\king_temp.dbf' size 20m tablespace group group1; select * from dba_tablespace_groups;
Views: 754 Praveen Biyyapu
Tablespaces in oracle database
 
08:27
This video will give you basic idea about tablespace issue. Plese find the following important query . Query to check tablespace usage ============================================== select a.TABLESPACE_NAME, a.BYTES bytes_used, b.BYTES bytes_free, b.largest, round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used from ( select TABLESPACE_NAME, sum(BYTES) BYTES from dba_data_files group by TABLESPACE_NAME ) a, ( select TABLESPACE_NAME, sum(BYTES) BYTES , max(BYTES) largest from dba_free_space group by TABLESPACE_NAME ) b where a.TABLESPACE_NAME=b.TABLESPACE_NAME order by ((a.BYTES-b.BYTES)/a.BYTES) desc; To check datafiles respective to tablespace_name. ======================================================== col tablespace_name format a30; col file_name format a40; SELECT TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024 FROM DBA_DATA_FILES where TABLESPACE_NAME='&tablespace_name'; To check ASM disk group usage ======================================== select name, group_number, name, type, state, total_mb, free_mb from v$asm_diskgroup; Please comment , like, subscribe and reach me if you have any query. EmailId: [email protected] ================================================ Hi Folks, Greetings!! I am professional part time oracle dba trainer and having good experience in oracle database administration. This is the right place to enroll yourself for Oracle DBA real time course. Please use following email ID for more details. Don’t hesitate to WhatsApp me any time. I will give you the best training and will create path to get DBA job easily. Enjoy the free YouTube video and connect with me for deep Oracle DBA knowledge. Mobile No : + 91 9960262955 (WhatsApp Number) EmailID: [email protected]
Views: 796 ANKUSH THAVALI
Managing Resumable Space Allocation in Oracle database
 
02:56
By Vivek Saxena Insa Shabad Coachings Shamli Resumable Space Allocation is given by extending the data file size with a open database Alter session enable resumable; Create tablespace spr datafile 'c:\db3\data\spr1a.dbf' size 100k; Create table spr(rn number(10) tablespace spr; Insert into spr(select salary from hr.employees); Insert into spr (select * from spr); Set oracle_sid= orcl Sqlplus/nolog Conn/as syadba Alter database datafile 'c:\db3\data\spr1a.dbf' resize 300k;
how to take  hot backup of database manually
 
07:37
can we take hot backup when the database is in noarchive log mode? =================================================================== no archive log list; alter database begin backup; can we shutdown a database when it is in backup mode? ===================================================== alter database begin backup; shutdown immediate; ==no shutdown normal;==no shutdown transcational;==no shutdown abort;==yes database is in hot backup mode,db crashes now how to recover and open the database? =================================================================================== alter databse begin backup; shutdown abort; ORA-10873: file 1 needs to be either taken out of backup mode or media recovered ORA-01110: data file 1: 'E:\DATABASE\DATABASE\ORADATA\ORCL\SYSTEM01.DBF' solution: startup mount; alter database end backup; alter database open; how to check whether the database is in backup mode or not? =========================================================== select * from v$backup; how to take hot backup of database manually? ============================================ alter database begin backup; copy datafiles manually from original location to backup location alter database end backup; alter system switch logfile; alter system switch logfile; alter system switch logfile; copy archives from archive location to backup location. alter database backup controlfile to trace; (or) alter database backup controlfile to location; copy controlfile to backup location. How to check current_scn number of database? ============================================= select CURRENT_SCN from v$database; select dbms_flashback.get_system_change_number from dual; select timestamp_to_scn(sysdate) from dual; how to check datafile header scn number? ========================================= select name,CHECKPOINT_CHANGE# from v$datafile_header; when checkpoint will occur? ============================ alter system switch logfile; alter database begin backup; alter database end backup; alter system checkpoint;(manual checkpoint) shutdown immediate;(when the database is down) what will happen when checkpoint occurs? ========================================= scn of datafile header will update;
Views: 1941 Praveen Biyyapu
How to find the tablespaces in an Oracle Database - Database Tutorial 55 - Oracle DBA Tutorial
 
05:10
Video 55 How to find the tablespaces in an Oracle Database - Oracle DBA Tutorial This video explains about how to view the list of tablespaces that are there in an Oracle Database. To do that: select * from dba_tablespaces;
Views: 1468 Sam Dhanasekaran
19. Create Tablespace, Create User, Create Role, Drop Role, Drop User, Drop Tablespace in Oracle
 
13:58
In this video you will learn about Create Tablespace, Create User, Create Role, Drop Role, Drop User, Drop Tablespace in PL/SQL Oracle. For Support =========== Email: [email protected] Contact Form: http://www.learninhindi.com/home/contact Our Social Media ================ Facebook: https://www.facebook.com/LearnInHindi Twitter: https://twitter.com/LearnInHindi For Training & Videos ===================== For more videos and articles visit: http://www.learninhindi.com Free Java Programming In Hindi Course ===================================== https://www.youtube.com/playlist?list=PLOZ3jentlCDAwwhMyoLISrxkXTADGp7PH Free Oracle PL/SQL Programming In Hindi Course ============================================== https://www.youtube.com/playlist?list=PLB5DA82419C2D99B6 Free C Programming In Hindi Course ================================== https://www.youtube.com/playlist?list=PLOZ3jentlCDAxKpBLMWogxSdy6BZcsAJq Trips & Tricks Channel ====================== https://www.youtube.com/channel/UCGmLfkuCo-3lHHJXRJ9HUMw Programming in Hindi Channel ============================ https://www.youtube.com/channel/UCudElIDgwNrybeAvXIy1HZQ
Views: 14742 ITORIAN
How to Detect Soft Corruption in Oracle 12c Database
 
18:19
Detection of Soft Corruption in 12c: To use the scripts below replace : , ",# and $ by proper bracket Lets Create a tablespace and small table. SQL: create tablespace DEMO1 datafile '/u02/tstdb1/TSTDB1/datafile/demo01.dbf' size 50M SQL: create table objects tablespace DEMO1 as select * from dba_objects; SQL: alter table objects add constraint pk_obj primary key #object_id$; SQL: create index idx_obj_name on objects#object_name$ tablespace demo1; Backup tablespace. RMAN: backup tablespace DEMO1; We need to put the DB in archivelog first Rebuild index with NOLOGGING option to simulate soft corruption later. RMAN: alter index idx_obj_name rebuild nologging; Confirm that we have datafiles that require backup because they have been affected with NOLOGGING operation. RMAN: report unrecoverable; Simulate corruption. RMAN: alter database datafile 5 offline; RMAN: restore datafile 5; RMAN: recover datafile 5; RMAN: alter database datafile 5 online; Query table with corrupted index and notice error. SQL: select count#*$ from objects where object_name like 'A%'; Let’s perform validation of datafile to check block corruption. RMAN: backup validate check logical datafile 5; Notice that we have 457 blocks marked corrupt but v$database_block_corruption view is empty. SQL: select count#*$ from v$database_block_corruption; Let’s query v$nonlogged_block view. set lines 200 set pages 999 select file#, block#, blocks,object#,reason from v$nonlogged_block; Will RMAN detect that we have corrupted blocks? RMAN: backup datafile 5; RMAN backup won’t fail due to NOLOGGING corrupt blocks and our backup will contain soft corrupted blocks. Let’s Identify corrupt segments using v$nonlogged_block view. set lines 2000 set pages 9999 col owner for a20 col partition_name for a10 col segment_name for a20 SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file# , greatest#e.block_id, c.block#$ corr_start_block# , least#e.block_id+e.blocks-1, c.block#+c.blocks-1$ corr_end_block# , least#e.block_id+e.blocks-1, c.block#+c.blocks-1$ - greatest#e.block_id, c.block#$ + 1 blocks_corrupted FROM dba_extents e, V$NONLOGGED_BLOCK c WHERE e.file_id = c.file# AND e.block_id "= c.block# + c.blocks - 1 AND e.block_id + e.blocks - 1 := c.block# UNION SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file# , header_block corr_start_block# , header_block corr_end_block# , 1 blocks_corrupted FROM dba_segments s, V$NONLOGGED_BLOCK c WHERE s.header_file = c.file# AND s.header_block between c.block# and c.block# + c.blocks - 1 UNION SELECT null owner, null segment_type, null segment_name, null partition_name, c.file# , greatest#f.block_id, c.block#$ corr_start_block# , least#f.block_id+f.blocks-1, c.block#+c.blocks-1$ corr_end_block# , least#f.block_id+f.blocks-1, c.block#+c.blocks-1$ - greatest#f.block_id, c.block#$ + 1 blocks_corrupted FROM dba_free_space f, V$NONLOGGED_BLOCK c WHERE f.file_id = c.file# AND f.block_id "= c.block# + c.blocks - 1 AND f.block_id + f.blocks - 1 := c.block# order by file#, corr_start_block# / This is the best outcome to get if you notice corruption errors. All errors are related to index corruption so we could fix this problem rebuilding index. alter index idx_obj_name rebuild; Simply issuing "alter index rebuild" command won't work. We should mark index unusable to drop segment before rebuilding it or just rebuild index with online option. It is better choice to mark index unusable because you don't need additional space then, but I will simply rebuild index with online option and see what will happen. SQL: alter index idx_obj_name rebuild online; Index altered. SQL: select count#*$ from objects where object_name like 'A%'; No errors... but, let's validate datafile for corruption. RMAN: backup validate check logical datafile 5; Notice "Marked Corrupt" column. Hm... 457 like before. Don't worry, this is not new corruption. These are FREE blocks which will be reused and Oracle will automatically re-format those blocks. Query the v$nonlogged_block view again as given above. We could force re-formatting creating dummy table and inserting data to dummy table. Check Doc ID 336133.1. create table s # n number, c varchar2#4000$ $ nologging tablespace DEMO1; SQL: BEGIN FOR i IN 1..1000000 LOOP INSERT /*+ APPEND */ INTO sys.s select i, lpad#'REFORMAT',3092, 'R'$ from dual; commit ; END LOOP; END; / SQL: drop table sys.s purge; Notice that we don't have corrupted blocks any more. RMAN: backup validate check logical datafile 5;
Views: 160 OracleDBA
database is in hot backup mode,db crashes now how to recover and open the database
 
04:06
can we take hot backup when the database is in noarchive log mode? =================================================================== no archive log list; alter database begin backup; can we shutdown a database when it is in backup mode? ===================================================== alter database begin backup; shutdown immediate; ==no shutdown normal;==no shutdown transcational;==no shutdown abort;==yes database is in hot backup mode,db crashes now how to recover and open the database? =================================================================================== alter databse begin backup; shutdown abort; ORA-10873: file 1 needs to be either taken out of backup mode or media recovered ORA-01110: data file 1: 'E:\DATABASE\DATABASE\ORADATA\ORCL\SYSTEM01.DBF' solution: startup mount; alter database end backup; alter database open; how to check whether the database is in backup mode or not? =========================================================== select * from v$backup; how to take hot backup of database manually? ============================================ alter database begin backup; copy datafiles manually from original location to backup location alter database end backup; alter system switch logfile; alter system switch logfile; alter system switch logfile; copy archives from archive location to backup location. alter database backup controlfile to trace; (or) alter database backup controlfile to location; copy controlfile to backup location. How to check current_scn number of database? ============================================= select CURRENT_SCN from v$database; select dbms_flashback.get_system_change_number from dual; select timestamp_to_scn(sysdate) from dual; how to check datafile header scn number? ========================================= select name,CHECKPOINT_CHANGE# from v$datafile_header; when checkpoint will occur? ============================ alter system switch logfile; alter database begin backup; alter database end backup; alter system checkpoint;(manual checkpoint) shutdown immediate;(when the database is down) what will happen when checkpoint occurs? ========================================= scn of datafile header will update;
Views: 775 Praveen Biyyapu
Oracle DBA | Adding Data File Existing Table Space | Bangla | DBA1-15
 
04:01
Oracle DBA | Adding Data File Existing Table Space | Bangla | DBA1-15 To See More DBA part-01 Tutorial Click here https://www.youtube.com/playlist?list=PLXXvCMuixAJUO3yQq7-LLdfQb5HpsQKNg To See More DBA part-02 Tutorial Click here https://www.youtube.com/playlist?list=PLXXvCMuixAJUviefN3-ZzKDVgbIH0RoaS To See More Reports Tutorial Click here https://www.youtube.com/watch?v=y_0f_iILlmY&list=PLXXvCMuixAJUfU4ZISmPtHrmKe5qRvyVY To See More Forms Tutorial Click here https://www.youtube.com/watch?v=Zsu4CWo_vwk&list=PLXXvCMuixAJU3Ap_FE2zSEBicv8IC5kDU Dear viewer, In this tutorial you will learn Adding Data File Existing Table Space Adding Data File: Command: Connect sysdba SQL-alter tablespace test1 add datafile 'E:\app\Administrator\oradata\orcl\dfile2.dbf' size 500m; To Get more Tutorial Subscribe The Channel click here https://www.youtube.com/channel/UCQ3ea4zVCfnKBx1_7h8raGQ?sub_confirmation=1 To Get All Post Like The Facebook Page Click here https://www.facebook.com/oracledbsolutions Follow us: Facebook: https://www.facebook.com/oracledbsolutions Youtube link: https://www.youtube.com/channel/UCQ3ea4zVCfnKBx1_7h8raGQ?view_as=subscriber Facebook Group: https://www.facebook.com/groups/465557066961548 Contact us: email: [email protected] Skype: oracle26solutions Mob: 01734557080, 01627422780
Views: 75 OracleDB Solutions
Create Tablespace Oracle | Create Tablespace Oracle 12c Example | Tablespace Management Oracle 12c
 
09:27
Create Tablespace Oracle | Create Tablespace Oracle 12c Example | Tablespace Management Oracle 12c --------------------------------------------------------------------------------------------------- Create Tablespace Oracle,create tablespace oracle 12c example,types of tablespace in oracle,how to check tablespace in oracle,tablespace in oracle 11g,tablespace management in oracle 12c,system tablespace in oracle,tablespace management in oracle 11g,oracle, tablespaces, creating, managing, database, tablespace management, tablespace administration, oracle tablespaces, create tablespace, list tablespace, free space in tablespace, tablespacees, types of tablespaces, undo tablespace, temporary tablespace, oracle dba training, oracle locally managed tablespace, oracle different tablespace, oralce tablespace, sql tutorials, oracle database tutorial, how to create tablespace, tablespace, oracle sql developer, managing database structure, oracle 12c,oracle 12c download,oracle database 12c,oracle client 12c,oracle 12c new features,uninstall oracle 12c,oracle xe 12c,oracle 12c documentation,oracle 12c installation, 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
Open Database Recovery when Initially Oracle Database was Closed on Window
 
18:20
By Vivek Saxena Insa Shabad Coachings Shamli Open Database Recovery(Recover database at run time) in Archive Mode when Database Initially Closed Create tabkespace vivek2 datafile 'e:\vivek2a.dbf' size 200k; Create table vivek(rn number(20)) tabkespace vivek2; Insert into vivek values(1); Commit ; Alter system switch logfile ; Shut immediate Startup Database mounted ERROR:ORA-01157:can't identified/lock data file 5 - see DBWR trace file ORA-01110: data file 5 Alter database datafile 5 offline ; Alter database open ; Alter database rename file 'e:\vivek2a.dbf' to 'c:\db3\data\vivek2a.dbf'; Set autorecovery on Select * from dba_data_files; Desc v$datafile ; Select file#, status from v$datafile ; Select * from v$recover_file ; Recover datafile 5; Alter database datafile 'c:\db3\data\vivek2a.dbf online ; select * from vivek ;
How to create tablespace in Oracle PL SQL
 
03:50
This video will show you "How to create tablespace in Oracle" tablespace management in oracle tablespace size in oracle oracle tutorial for beginners #techquerypond https://techquerypond.wordpress.com https://techquerypond.com https://twitter.com/techquerypond
Views: 785 Tech Query Pond
How to create oracle 11g Table space (user schema creation)
 
11:02
create tablespace rfdemo logging datafile '/oradata/DB11G/rfdemo.dbf' size 5G autoextend on next 512m maxsize 10G extent management local; CREATE USER rfdemo IDENTIFIED BY rfdemo DEFAULT TABLESPACE rfdemo TEMPORARY TABLESPACE TEMP; GRANT "RESOURCE" to rfdemo; GRANT "CONNECT" to rfdemo; GRANT "DATAPUMP_EXP_FULL_DATABASE" to rfdemo; GRANT "DATAPUMP_IMP_FULL_DATABASE" to rfdemo; REVOKE UNLIMITED TABLESPACE FROM rfdemo; ALTER USER rfdemo QUOTA UNLIMITED ON rfdemo; commit;
Views: 7794 ab1 ek
oracle tablespace
 
10:44
Creacion y manupulacion de tablespaces en oracle
Views: 283 Elias Villanueva
How to change Database name using control file backup
 
16:31
Hello guys today i am going to explaining to you how to change your database name using Trace control file backup. #changeDatabasename #usingcontrolfilebackup Oracle database Unbeatable,Unbreakable Platform..
Views: 2231 Oracle World
Oracle Default Tablespace.mp4
 
00:52
how to audit oracle database default tablespace assignment
Views: 303 checklist20
How to Start Physical Standby database & Check if Database is in Sync
 
06:32
How to Start Physical Standby database & Check if Database is in Sync Dataguard Status ==================================================== Start Standby Database =============================== startup nomount alter database mount standby database; alter database recover managed standby database disconnect from session; DB STATUS ======================= select status,instance_name,database_role,protection_mode from v$database,v$instance; TO check if dataguard in synchronizing with standby. ============================================================================= select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#; Status of MRP Process --------------------------- select process,status,client_process,thread#,sequence#,block#,blocks,delay_mins from v$managed_standby; To get the diffrence between Recived and applied logs. ------------------------------------------------------- select MAX_RECEIVED,MAX_APPLIED,(MAX_RECEIVED - MAX_APPLIED) as "Difference" from (SELECT ARCHIVED_THREAD#, MAX(ARCHIVED_SEQ#) as MAX_RECEIVED, APPLIED_THREAD#, MAX(APPLIED_SEQ#) as MAX_APPLIED FROM V$ARCHIVE_DEST_STATUS) ARCH ; ================================================= Hi Folks, Greetings!! I am professional part time oracle dba trainer and having good experience in oracle database administration. This is the right place to enroll yourself for Oracle DBA real time course. Please use following email ID for more details. Don’t hesitate to WhatsApp me any time. I will give you the best training and will create path to get DBA job easily. Enjoy the free YouTube video and connect with me for deep Oracle DBA knowledge. Mobile No : + 91 9960262955 (WhatsApp Number) EmailID: [email protected] Please visit my official website for oracle database training and hadoop blog. https://www.orcldata.com/ Please donate on below link if you think I am helping you with your career. https://www.paypal.me/ankushthavali OR Google Pay : 9960262955 OR Account No : 31347845762 IFSC: SBIN0012311 how to check data guard status oracle how to check lag in data guard how to sync primary and standby database in oracle how to check archive log gap standby database how to check if archive logs are applied to standby database query to check archive log gap how to check if database has standby how to check standby database name from primary
Views: 1253 ANKUSH THAVALI

Ventolin 4 mg 30 comprimidos timeline
Metformin hcl er ter 500 mg tylenol
Prednisolone acis 20 mg dosierung
Kontab 50mg clomid
Sulfacetamide sulfur lotion generic viagra