Every file and directory in your Linux system has the following 3 permissions defined for all the 3 owners discussed above.
Read: This permission give you the authority to open and read a file.
Write: The write permission on a directory gives you the authority to add, remove and rename files stored in the directory
Execute: In Windows, an executable program usually has an extension “.exe” and which you can easily run. Linux, you cannot run a program unless the execute permission is set. If the execute permission is not set, you might still be able to see/modify the program code(provided read & write permissions are set), but not run it.
Let’s see file permissions in Linux with examples:
ls – l This list command will show the file permissions
[oracle@oracletest scripts]$ ls -l
total 24
-rw-rw-r--. 1 oracle oracle 42 Oct 21 01:49 kiruba.txt
-rwxrwxr-x. 1 oracle oracle 1291 Oct 20 23:47 rman_bkp.sh
-rw-rw-r--. 1 oracle oracle 6979 Oct 20 23:48 rman.log
-rwxr--r--. 1 oracle oinstall 515 Sep 9 14:45 setEnv.sh
-rwxr--r--.1 oracle oinstall 134 Sep 7 10:54 stop_all.sh
So, Here we listed some files with permissions
r = read permission w = write permission x = execute permission – = no permission
permissions with chmod command
We can use the chmod command which stands for change mode. Using the command, we can set permissions (read, write, execute) on a file/directory for the owner, group.
There are 2 ways to use the command
Absolute mode
Symbolic mode
Absolute(Numeric) Mode
In this mode file permissions are not represented as characters, we can give in a three-digit octal number.
Number
Permission Type
Symbol
0
No Permission
—
1
Execute
–x
2
Write
-w-
3
Execute + Write
-wx
4
Read
r–
5
Read + Execute
r-x
6
Read +Write
rw-
7
Read + Write +Execute
rwx
Example: chmod three-digit octal number filename
chmod 777 kiruba.txt
we were given this permission to text file we can able to read, write and execute the specified file
Symbolic Mode
In the Absolute mode, we can change permissions for all 3 owners. In the symbolic mode, we can modify the permissions of a specific owner also make use of mathematical symbols to modify the Linux file permissions.
Operator
Description
+
Adds permission to a file or directory
–
Removes the permission
=
Sets the permission and overrides the permissions set earlier.
The various owners are represented as –
User Denotations
u
user/owner
g
group
o
other
a
all
Changing Ownership and Group:
changing the ownership of a file/directory we can use the following command
chown user filename
Eg: chown kiruba rman_backup.log
select s.sid,s.process,p.spid,s.status ,s.action,s.module, (s.last_call_et/3600) from
v$session s, v$process p where round(last_call_et/3600) >4 and action like ‘%FRM%’ and
p.addr=s.paddr ;
2. To list inactive Sessions respective username
SELECT username,count(*) num_inv_sess
FROM v$session
where last_call_et > 3600
and username is not null
AND STATUS=’INACTIVE’
group by username
order by num_inv_sess DESC;
SELECT count(*) FROM v$session where last_call_et > 43200 and username is not null AND
STATUS=’INACTIVE’;
SELECT count(*) FROM v$session where last_call_et > 3600 and username is not null AND
STATUS=’INACTIVE’;
3. To find session-id with a set of SPIDs
select sid from v$session, v$process where addr=paddr and spid in (‘11555′,’26265′,’11533’);
4. To find SQL Text given SQLHASH & SQLADDR
select piece,sql_text from v$sqltext where HASH_VALUE = &hash and ADDRESS =’&addr’ order by piece;
select piece,sql_text from v$sqltext where ADDRESS =’&addr’ order by piece;
5.Checking for Active Transactions SID
select username,t.used_ublk,t.used_urec from v$transaction t,v$session s where t.addr=s.taddr;
6. Session details from Session longops
select inst_id,SID,SERIAL#,OPNAME,SOFAR,TOTALWORK,START_TIME,LAST_UPDATE_TIME, username from gv$session_longops;
7. Session details with SPID
select sid, serial#, USERNAME, STATUS, OSUSER, PROCESS,
MACHINE, MODULE, ACTION, to_char(LOGON_TIME,’yyyy-mm-dd hh24:mi:ss’)
from v$session where paddr in (select addr from v$process where spid = ‘&spid’)
/
8. To find Undo Generated For a given session
select username,
t.used_ublk ,t.used_urec
from gv$transaction t,gv$session s
where t.addr=s.taddr and
s.sid=’&sid’;
9. To list the count of connections from other machines
select count(1),machine from gv$session where inst_id=’&inst_id’ group by machine;
10. To get the total count of sessions and processes
select count(*) from v$session;
select count(*) from v$process;
select (select count() from v$session) sessions, (select count() from v$process) processes from dual;
11. To find SQL text through SQL address
select sql_address from v$session where sid=1999;
select sql_text from v$sqltext where ADDRESS=’C00000027FF00AF0′ order by PIECE;
12. To find SQL text for different SQL hash value
select hash_value,sql_text from v$sql where hash_value in (1937378691,1564286875,
248741712,2235840973,2787402785)
13. Session details associated with SID and Event waiting for
set pages 50000 lines 32767
col EVENT for a40
select a.sid, a.serial#, a.status, a.program, b.event,to_char(a.logon_time, ‘dd-mon-yy hh24:mi’) LOGON_TIME,to_char(Sysdate, ‘dd-mon-yy-hh24:mi’) CURRENT_TIME, (a.last_call_et/3600) “Hrs connected” from v$session a,v$session_wait b where a.sid in(&SIDs) and a.sid=b.sid order by 8;
14. Active Sessions running for more than 1 hour
set pages 50000 lines 32767
col USERNAME for a10
col MACHINE for a15
col PROGRAM for a40
SELECT USERNAME,machine,inst_id,sid,serial#,PROGRAM,
to_char(logon_time,’dd-mm-yy hh:mi:ss AM’)”Logon Time”,
ROUND((SYSDATE-LOGON_TIME)(2460),1) as MINUTES_LOGGED_ON,
ROUND(LAST_CALL_ET/60,1) as Minutes_FOR_CURRENT_SQL
From gv$session
WHERE STATUS=’ACTIVE’
AND USERNAME IS NOT NULL and ROUND((SYSDATE-LOGON_TIME)(2460),1) > 60
ORDER BY MINUTES_LOGGED_ON DESC;
15.SQLs Running from longtime
alter session set nls_date_format = ‘dd/mm/yyyy hh24:mi’;
set pages 50000 lines 32767
col target format a25
col opname format a40
select sid
,opname
,target
,round(sofar/totalwork*100,2) as percent_done
,start_time
,last_update_time
,time_remaining
from v$session_longops;
16. Last/Latest Running SQL
set pages 50000 lines 32767
select inst_id,sample_time,session_id,session_serial#,sql_id from gv$active_session_history
where sql_id is not null
order by 1 desc;
17. Current Running SQLs
set pages 50000 lines 32767
col program format a20
col sql_text format a50
select b.sid,b.status,b.last_call_et,b.program,c.sql_id,c.sql_text
from v$session b,v$sqlarea c
where b.sql_id=c.sql_id;
18. Current Running SQLs
set pages 50000 lines 32767
col HOST_NAME for a20
col EVENT for a40
col MACHINE for a30
col SQL_TEXT for a50
col USERNAME for a15
select sid,serial#,a.sql_id,a.SQL_TEXT,S.USERNAME,i.host_name,machine,S.event,S.seconds_in_wait sec_wait,
to_char(logon_time,’DD-MON-RR HH24:MI’) login
from gv$session S,gV$SQLAREA A,gv$instance i
where S.username is not null
— and S.status=’ACTIVE’
AND S.sql_address=A.address
and s.inst_id=a.inst_id and i.inst_id = a.inst_id
and sql_text not like ‘select S.USERNAME,S.seconds_in_wait%’;
Please find out all of our articles send us the Invitation or Follow us:
Redo log files are filled with redo records. A redo record also called a redo entry, is made up of a group of change vectors, each of which is a description of a change made to a single block in the database. For example, if you change a salary value in an employee table, you generate a redo record containing change vectors that describe changes to the data segment block for the table, the undo segment data block, and the transaction table of the undo segments.
Redo entries record data that you can use to reconstruct all changes made to the database, including the undo segments. Therefore, the redo log also protects rollback data. When you recover the database using redo data, the database reads the change vectors in the redo records and applies the changes to the relevant blocks.
SQL> select * from v$log;
SQL> select group#,status,sequence# from v$log;
GROUP# STATUS SEQUENCE#
---------- ---------------- ----------
1 CURRENT 91
2 INACTIVE 89
3 INACTIVE 90
SQL> select * from v$logfile;
Active (Current) and Inactive Redo Log Files
Oracle Database uses only one redo log file at a time to store redo records written from the redo log buffer. The redo log file that LGWR is actively writing to is called the current redo log file.
Redo log files that are required for instance recovery are called active redo log files. Redo log files that are no longer required for instance recovery are called inactive redo log files.
If you have enabled archiving (the database is in ARCHIVELOG mode), then the database cannot reuse or overwrite an active online log file until one of the archiver background processes (ARCn) has archived its contents. If archiving is disabled (the database is in NOARCHIVELOG mode), then when the last redo log file is full, LGWR continues by overwriting the first available active file.
Add New redo log file
SQL> alter database add logfile group 4 '/u01/app/oracle/oradata/TESTDB/redo04.dbf' size 100m;
Database altered.
Add Member to an existing group
SQL> alter database add logfile member '/u01/app/oracle/oradata/TESTDB/redo04b.log' to group 4;
Database altered.
Drop member
SQL> alter database drop logfile member '/u01/app/oracle/oradata/TESTDB/redo04b.log';
Database altered.
Drop Logfile Group
SQL> alter database drop logfile group 4;
Database altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 INACTIVE
3 INACTIVE
Adding Two member
SQL> alter database add logfile group 4 ('/u01/app/oracle/oradata/TESTDB/redo04a.log', '/u01/app/oracle/oradata/TESTDB/redo04C.log') size 100m;
Database altered.
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- ---------------------------
4 /u01/app/oracle/oradata/TESTDB/redo04a.log
4 /u01/app/oracle/oradata/TESTDB/redo04C.log
Log Switches and Log Sequence Numbers
A log switch is a point at which the database stops writing to one redo log file and begins writing to another. Normally, a log switch occurs when the current redo log file is completely filled and writing must continue to the next redo log file. However, you can configure log switches to occur at regular intervals, regardless of whether the current redo log file is completely filled. You can also force log switches manually.
Oracle Database assigns each redo log file a new log sequence number every time a log switch occurs and LGWR begins writing to it. When the database archives redo log files, the archived log retains its log sequence number. A redo log file that is cycled back for use is given the next available log sequence number.
Each online or archived redo log file is uniquely identified by its log sequence number. During a crash, instance, or media recovery, the database properly applies to redo log files in ascending order by using the log sequence number of the necessary archived and redo log files.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Once you have configured the flash recovery area, you can put the database in ARCHIVELOG mode. Unfortunately, this requires that the database be shutdown first with the shutdown command (however, from earlier in the chapter, we note that shutdown immediate is the best option). Once you have shutdown the database, you will start the database in mount Stage with the startup mount command. Then put the database in ARCHIVELOG mode, and finally open the database. Here is an example of how this all works from the command line.
SQL> archive log list;
Database log mode No Archive ModeAutomatic archival Disabled
Archive destination d:oracleoradataDB10Garchive
Oldest online log sequence 427
Current log sequence 429
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1610609200 bytes
Fixed Size 8897072 bytes
Variable Size 385875968 bytes
Database Buffers 1207959552 bytes
Redo Buffers 7876608 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination d:oracleoradataDB10Garchive
Oldest online log sequence 426
Next log sequence to archive 427
Current log sequence 428
How to Disable archive log mode
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination d:oracleoradataDB10Garchive
Oldest online log sequence 426
Next log sequence to archive 427
Current log sequence 428
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1610609200 bytes
Fixed Size 8897072 bytes
Variable Size 385875968 bytes
Database Buffers 1207959552 bytes
Redo Buffers 7876608 bytes
Database mounted.
SQL> alter database noarchivelog;Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list;
Database log mode NoArchive Mode
Automatic archival Enabled
Archive destination d:oracleoradataDB10Garchive
Oldest online log sequence 426
Next log sequence to archive 427
Current log sequence 428
Change Archive log mode and Destination in oracle 19c
[oracle@training ~]$ export ORACLE_SID=training1
[oracle@training ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 28 23:30:16 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 2432695144 bytes
Fixed Size 8899432 bytes
Variable Size 536870912 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0
[oracle@training ~]$ mkdir -p /u01/ARC_BKP
[oracle@training ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 28 23:38:09 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
To check the current archiving location
SQL> SHOW PARAMETER DB_RECOVERY_FILE_DEST
NAME TYPE VALUE
--------------------- --------------- --------------
db_recovery_file_dest string /u01/app/oracle /fast_recovery_area
db_recovery_file_dest_size big integer 8256M
SQL> alter system set DB_RECOVERY_FILE_DEST='/u01/ARC_BKP';
System altered.
SQL> alter system set log_archive_dest=’/u01/ARC_BKP’;
System altered.
SQL> alter database close;
Database altered.
SQL> alter database archivelog;
Database altered.
SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0
[oracle@training u01]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 28 23:52:05 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 2432695144 bytes
Fixed Size 8899432 bytes
Variable Size 536870912 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/ARC_BKP
Oldest online log sequence 89
Next log sequence to archive 91
Current log sequence 91
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
SQL> select name from v$controlfile;
NAME
------------------------------------------------------------------
/u01/app/oracle/oradata/TRAINING/controlfile/o1_mf_j9bdm6dk_.ctl
/u01/app/oracle/oradata/TRAINING/controlfile/o1_mf_j9bdm6dk_1.ctl
SQL> alter system set control_files= '/u01/app/oracle/oradata/TRAINING/controlfile/o1_mf_j9bdm6dk_.ctl', '/u01/app/oracle/oradata/TRAINING/controlfile/o1_mf_j9bdm6dk_1.ctl', '/u01/app/oracle/oradata/TRAINING/control01.dbf' scope=spfile;
System altered.
SQL> startup
ORACLE instance started.
Total System Global Area 2432695144 bytes
Fixed Size 8899432 bytes
Variable Size 536870912 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/TRAINING/controlfile/o1_mf_j9bdm6dk_.ctl
/u01/app/oracle/oradata/TRAINING/controlfile/o1_mf_j9bdm6dk_1.ctl
/u01/app/oracle/oradata/TRAINING/control01.dbf
SQL> create pfile from spfile;
File created.
MULTIPLEXING USING PFILE
Multiplexing is the process of mintaining a copy of same control files on different disk drivers (and idealy on different controllers). To multiplex your control files, we copy the control file to multiple locations and change the CONTROL_FILES parameter in the text based initialization file init.ora to include all control files names.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0
[oracle@training ~]$ cd $ORACLE_HOME/dbs
[oracle@training dbs]$ vi inittraining.ora
*.control_files='/u01/app/oracle/oradata/TRAINING/controlfile/o1_mf_j9bdm6dk_.ctl', '/u01/app/oracle/oradata/TRAINING/controlfile/o1_mf_j9bdm6dk_1.ctl', '/u01/app/oracle/oradata/TRAINING/control01.dbf'
[oracle@training dbs]$ cp /u01/app/oracle/oradata/TRAINING/control01.dbf /u01/app/oracle/oradata/TRAINING/control02.dbf
[oracle@training dbs]$ sqlplus / as sysdba
SQL> startup pfile=$ORACLE_HOME/dbs/inittraining1.ora
ORACLE instance started.
Total System Global Area 2432695144 bytes
Fixed Size 8899432 bytes
Variable Size 536870912 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/TRAINING1/controlfile/o1_mf_j9bdm6dk_.ctl
/u01/app/oracle/oradata/TRAINING1/controlfile/o1_mf_j9bdm6dk_1.ctl
/u01/app/oracle/oradata/TRAINING1/control01.dbf
/u01/app/oracle/oradata/TRAINING1/control02.dbf
SQL> create spfile from pfile;
File created.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
The LMT tablespace is implemented by adding EXTENT MANAGEMENT LOCAL clause to the tablespace definition.LMT tablespaces automate extent management and remove the ability to specify the NEXT storage parameter.
The only exception is when NEXT is used with MINEXTENTS at table creation time.
LMT means Locally Management system,LMT tablespace size is uniform.
By default uniform size is 1,By default lmt tablespace is Autoallocate.
Auto allocate
SQL> create tablespace india datafile '/u01/app/oracle/oradata/TESTDB/india01.dbf' size 50m;
Tablespace created.
Uniform Size
SQL> create tablespace USA datafile '/u01/app/oracle/oradata/TESTDB/USA01.dbf' size 50m extent management local uniform size 512k;
Tablespace created.
Deferred segment creation
SQL> create table india (reg_id number,reg_name varchar2(200));
Table created.
SQL> select count(*) from dba_segments where segment_name='INDIA';
COUNT(*)
----------
0
SQL> select count(*) from dba_extents where segment_name='INDIA';
COUNT(*)
----------
0
SQL> insert into india values (5,'tamil');
1 row created.
SQL> select count(*) from dba_segments where segment_name='INDIA';
COUNT(*)
----------
1
SQL> select count(*) from dba_extents where segment_name='INDIA';
COUNT(*)
----------
1
SQL> create table italy (reg_no number,reg_name varchar2(2000))
segment creation immediate;
Table created.
SQL> show parameter deferred_segment_creation;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean TRUE
SQL> alter system set deferred_segment_creation=false;
System altered.
SQL> show parameter deferred_segment_creation;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean FALSE
Extents
Continuous blocks it’s called extents,Data Stored in blocks,blocks Stored in extents.
By default Extents Size 64k.
TYPES OF EXTENTS
Initial - Initial is First No of transaction by default 100.
Next - Next no of transaction is 200.
Minextents - minimum number of transactions by default 1.
Maxextents - maximum number of transactions by default Unlimited.
Pct increase - pct increase by default 100.
BLOCKS
Smallest storage unit it’s called Blocks.After database creation we can specify block size but before database creation we can’t change block size. block size is 2k,4k,8k,16k,32k. By default Block size is 8k.
Check Block Size in Database
SQL> show parameter db_block_size;
NAME TYPE VALUE
--------------------- ----------- --------------
db_block_size integer 8192
SQL> select block_size,tablespace_name from dba_tablespaces;
BLOCK_SIZE TABLESPACE_NAME
---------- ------------------------------
8192 SYSTEM
8192 SYSAUX
8192 UNDOTBS1
8192 TEMP
8192 USERS
8192 TEST
8192 INDIA
SQL> select block_size,tablespace_name from dba_tablespaces;
BLOCK_SIZE TABLESPACE_NAME
---------- ---------------------
8192 SYSTEM
8192 SYSAUX
8192 UNDOTBS1
8192 TEMP
8192 USERS
8192 TEST
8192 INDIA
16384 SPAIN
Block Utilization Parameters
INITRANS
MAXTRANS
PCTUSED
PCTFREE
Initans is the initial number of transactions by default 1. Maxtrans is Maximum number of Transactions default 255.Inserted data Stored in pct used by default allocate space is 89%, in case pct used is full Remaining data goes to Next pct used. Update happens in pct free by default allocate space 10%, pct used and pct free information stored by block header by default allocated space 1%.
Block-level problems
Row chaining
Row migration
Row chaining
Inserted data stored in pct used,that pct used is full remaining data goes to next pct used it is called Row chaining.Row chaining occurs when a row can't physically fit into an Oracle block.
Another block is required to store the remainder of the row.Chaining can cause serious performance problems and is especially prevalent with those storing multimedia data or large binary objects (blobs).
You should pay special attention to the DB_BLOCK_SIZE parameter when you create your database.Block sizes of 4 kilobytes or more are the norm, not the exception.
Row migration
Date updates happen for pct free ,that updated data stored in pct used.pct used is full that time remaining data goes to the next pct used that is called row Migration.
Migration of an Oracle row occurs when a row is updated in an Oracle block and the amount of free space in the block is not adequate to store all of the row's data. The row is migrated to another physical block in the table. The problem is that the indexes that refer to the migrated row are still pointing to the block where the row used to be, and hence the table reads are doubled. Note however that full table scans will scan blocks as they come and will perform the same number of reads whether the rows are migrated or not.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Flashback version query allows the versions of a specific row to be tracked during a specified time period using the VERSIONS BETWEEN clause.
Flashback version query is based on UNDO. As a result, the amount of time you can flashback is dependent on how long undo information is retained, as specified by the UNDO_RETENTION parameter.
SQL> CREATE TABLE flashback_version_query_test (id NUMBER(10),description VARCHAR2(50));
Table created.
SQL> INSERT INTO flashback_version_query_test (id, description) VALUES (1, 'ONE');
1 row created.
SQL> commit;
Commit complete.
SQL> SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;
CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
4991867 2021-07-20 22:29:52
SQL> UPDATE flashback_version_query_test SET description = 'TWO' WHERE id = 1;
1 row updated.
SQL> commit;
Commit complete.
SQL> UPDATE flashback_version_query_test SET description = 'THREE' WHERE id = 1;
1 row updated.
SQL> commit;
Commit complete.
SQL> SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;
CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
4992195 2021-07-20 22:34:30
SQL> SELECT versions_startscn, versions_starttime,versions_endscn, versions_endtime, versions_xid, versions_operation, description from flashback_version_query_test VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP(' 2021-07-20 22:29:52', 'YYYY-MM-DD HH24:MI:SS') AND TO_TIMESTAMP('2021-07-20 22:34:30', 'YYYY-MM-DD HH24:MI:SS')WHERE id = 1;
VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN VERSIONS_ENDTIME VERSIONS_XID V DESCRIPTION
---------- --------------------- ------------------ -------------- ----------- -----------
4992172 20-JUL-21 10.33.41 PM 0D0020001B040000 U THREE
4992136 20-JUL-21 10.32.23 PM 0B00050021040000 U TWO
4992172 20-JUL-21 10.33.41 PM ONE
SQL> SELECT versions_startscn, versions_starttime, versions_endscn, versions_endtime, versions_xid, versions_operation, description FROM flashback_version_query_test
VERSIONS BETWEEN SCN 4991867 and 4992195 WHERE id = 1;
VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN VERSIONS_ENDTIME VERSIONS_XID V DESCRIPTION
------------------ ------------------------ ------------------ ------------------------ ---------------- - -----------
4992172 20-JUL-21 10.33.41 PM 0D0020001B040000 U THREE
4992136 20-JUL-21 10.32.23 PM 4992172 20-JUL-21 10.33.41 PM 0B00050021040000 U TWO
4992136 20-JUL-21 10.32.23 PM ONE
SQL> SELECT xid, operation, start_scn,commit_scn, logon_user, undo_sql FROM flashback_transaction_query WHERE xid = HEXTORAW('06000000FA030000');
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
SQL> select file#,status,name from v$tempfile;
FILE# STATUS NAME
---------- ------- --------------------------------------------------
1 ONLINE /u01/app/oracle/oradata/ORACLEAGENT/temp01.dbf
Create a New Temporary Tablespace
SQL> create temporary tablespace India tempfile '/u01/app/oracle/oradata/ORACLEAGENT/India01.dbf' size 100m;
Tablespace created.
SQL> select tablespace_name from dba_temp_files;
TABLESPACE_NAME
------------------------------
Italy
India
Check Property valuein Database
SQL> select property_value from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
PROPERTY_VALUE
-----------------------
Italy
SQL> alter database default temporary tablespace India;
Database altered.
SQL> select property_value from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
PROPERTY_VALUE
------------------------
India
CreateTemporary Tablespace Group
SQL> create temporary tablespace France
tempfile '/u01/app/oracle/oradata/ORACLEAGENT/France01.dbf' size 30m tablespace group Tempgroup;
Tablespace created.
SQL> alter tablespace India tablespace group tempgroup;
Tablespace altered.
SQL> alter database default temporary tablespace tempgroup;
Database altered.
SQL> select property_value from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
PROPERTY_VALUE
---------------------- TEMPGROUP
CheckTemporary tablespace size
SQL> select tablespace_name,TABLESPACE_SIZE,ALLOCATED_SPACE,FREE_SPACE from dba_temp_free_space;
TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
--------------- --------------- --------------- ----------
INDIA 524288000 10485760 517996544
ITALY 104857600 2097152 102760448
FRANCE 31457280 1048576 30408704
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
