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
DBNEWID is a database utility, in $ORACLE_HOME/bin directory, that can change the internal database identifier (DBID) and the database name (DBNAME) for an operational database. Prior to the introduction of the DBNEWID utility, we used to manually create a copy of a database and give it a new database name (DBNAME) by re-creating the control file. However, alteration of the internal database identifier (DBID) of an instance was impossible.
The DBID is an internal, unique identifier for a database. Because Recovery Manager(RMAN) distinguishes databases by DBID, you could not register a seed database and a manually copied database together in the same RMAN repository. The DBNEWID utility solves this problem.
NID utility allows us to change
Only DBID of a database
Only DBNAME of a database
Both DBNAME and DBID of a database
Changing the DBID of a database is a serious procedure. When the DBID of a database is changed all previous backups and archived logs of the database become unusable. After you change the DBID, you must open the database with the RESETLOGS option, which re-creates the online redo logs and resets their sequence to 1. Consequently, you should make a backup of the whole database immediately after changing the DBID.
Changing DBNAME & DBID
SQL> select dbid, name from v$database;
DBID NAME
---------- ---------
247698686 TRAINING
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT
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.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@training ~]$ nid TARGET=sqlplus / as sysdba DBNAME=testdb LOGFILE=testdb.log
Password:
SQL> select status from v$instance;
STATUS
------------
STARTED
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01103: database name 'TESTDB' in control file is not 'TRAINING'
[oracle@training dbs]$ . oraenv
ORACLE_SID = [training] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@training dbs]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul 29 02:57:59 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup pfile=$ORACLE_HOME/dbs/inittraining.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.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
SQL> select dbid,name from v$database;
DBID NAME
---------- ---------
2862448691 TESTDB
SQL> select status,instance_name from v$instance;
STATUS INSTANCE_NAME
------------ ----------------
OPEN training
SQL> create spfile from pfile;
File created.
SQL> show parameter spfile;
NAME TYPE VALUE
------------- ----------- ------------------------------
spfile string /u01/app/oracle/product/19.0.0
/dbhome_1/dbs/spfiletraining.ora
Change the only DBNAME
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
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.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0
[oracle@training ~]$ nid target=sqlplus / as sysdba dbname=training setname=yes
DBNEWID: Release 19.0.0.0.0 - Production on Thu Jul 29 21:48:14 2021
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to database TESTDB (DBID=2862448691)
Connected to server version 19.3.0
Control Files in database:
/u01/app/oracle/oradata/TRAINING1/control01.dbf
/u01/app/oracle/oradata/TRAINING1/control02.dbf
Change database name of database TESTDB to TRAINING? (Y/[N]) => y
Proceeding with operation
Changing database name from TESTDB to TRAINING
Control File /u01/app/oracle/oradata/TRAINING1/control01.dbf -modified
Control File/u01/app/oracle/oradata/TRAINING1/control02.dbf - modified
Datafile /u01/app/oracle/oradata/TRAINING1/system.dbf - wrote new name
Datafile /u01/app/oracle/oradata/TRAINING1/sysaux.dbf - wrote new name
Datafile /u01/app/oracle/oradata/TRAINING1/undo.dbf - wrote new name
Datafile /u01/app/oracle/oradata/TRAINING1/users.dbf - wrote new name
Datafile /u01/app/oracle/oradata/TRAINING1/temp.tmp - wrote new name
Instance shut down
Database name changed to TRAINING.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.
[oracle@training dbs]$ . oraenv
ORACLE_SID = [training1] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@training dbs]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul 29 21:52:50 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
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$database;
NAME
---------
TRAINING
SQL> select status,instance_name from v$instance;
STATUS INSTANCE_NAME
--------- ----------------
OPEN training1
Change only DBID
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
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.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0
[oracle@training dbs]$ nid target=sqlplus / as sysdba
DBNEWID: Release 19.0.0.0.0 - Production on Thu Jul 29 22:58:56 2021
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to database TRAINING (DBID=2862448691)
Connected to server version 19.3.0
Control Files in database:
/u01/app/oracle/oradata/TRAINING1/control01.dbf
/u01/app/oracle/oradata/TRAINING1/control02.dbf
Change database ID of database TRAINING? (Y/[N]) => y
Proceeding with operation
Changing database ID from 2862448691 to 254963316
Control File /u01/app/oracle/oradata/TRAINING1/control01.dbf - modified
Control File /u01/app/oracle/oradata/TRAINING1/control02.dbf - modified
Datafile /u01/app/oracle/oradata/TRAINING1/system.dbf - dbid changed
Datafile /u01/app/oracle/oradata/TRAINING1/sysaux..dbf - dbid changed
Datafile /u01/app/oracle/oradata/TRAINING1/undo.dbf - dbid changed
Datafile /u01/app/oracle/oradata/TRAINING1/users.dbf - dbid changed
Datafile /u01/app/oracle/oradata/TRAINING1/data01.dbf - dbid changed
Datafile /u01/app/oracle/oradata/TRAINING1/temp.tmp - dbid changed
Control File /u01/app/oracle/oradata/TRAINING1/control01.dbf - dbid changed
Control File /u01/app/oracle/oradata/TRAINING1/control02.dbf - dbid changed
Instance shut down
Database ID for database TRAINING changed to 254963316.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed successfully.
[oracle@training dbs]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul 29 22:59:46 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
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.
SQL> alter database open resetlogs;
Database altered.
SQL> select name,dbid from v$database;
NAME DBID
--------- ----------
TRAINING 254963316
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:
Below Query will convert all the unusable and not valid indexes in Oracle. The query will cover the complete index with partition index and subpartition index.
The result will give you the rebuild command of the invalid or unusable indexes.
You can directly run that and on sqlplus and make them a valid or usable state.
QUERY :
SELECT 'ALTER INDEX ' || OWNER || '.' ||
INDEX_NAME || ' REBUILD ' ||
' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_INDEXES
WHERE STATUS='UNUSABLE'
UNION
SELECT 'ALTER INDEX ' || INDEX_OWNER || '.' ||
INDEX_NAME ||
' REBUILD PARTITION ' || PARTITION_NAME ||
' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_IND_PARTITIONS
WHERE STATUS='UNUSABLE'
UNION
SELECT 'ALTER INDEX ' || INDEX_OWNER || '.' ||
INDEX_NAME ||
' REBUILD SUBPARTITION '||SUBPARTITION_NAME||
' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_IND_SUBPARTITIONS
WHERE STATUS='UNUSABLE';
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:
The recycle bin is a logical collection of previously dropped objects. This feature doesn’t use flashback logs or undo, so it is distinct from the other flashback technologies.
Enable/Disable Recycle Bin
SQL> show parameter recyclebin;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string OFF
SQL> alter session set recyclebin = on;
Session altered.
SQL> show parameter recyclebin;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string ON
(RECYCLE BIN)
SQL> CREATE TABLE flashback_drop_test (id NUMBER(10));
Table created.
SQL> INSERT INTO flashback_drop_test (id) VALUES (1);
1 row created.
SQL> commit;
Commit complete.
SQL> DROP TABLE flashback_drop_test;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
FLASHBACK_DROP_T BIN$TstgCMiwQA66fl5FFDTBgA==$0 TABLE 2004-03-29:11:09:07
EST
SQL> FLASHBACK TABLE flashback_drop_test TO BEFORE DROP;
SELECT * FROM flashback_drop_test;
ID
----------
1
Rename Table
SQL> show RECYCLEBIN;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
S BIN$x3mKgoseD/7gUxkBqMAnAA==$0 TABLE 2021-07-19:17:20:17
SQL> create table s(n number);
Table created.
SQL> FLASHBACK TABLE s TO BEFORE DROP;
FLASHBACK TABLE s TO BEFORE DROP
*
ERROR at line 1:
ORA-38312: original name is used by an existing object
SQL> FLASHBACK TABLE s TO BEFORE DROP rename to b;
Flashback complete.
SQL> select * from b;
no rows selected
SQL> show RECYCLEBIN;
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 Query allows the contents of a table to be queried with reference to a specific point in time, using the AS OF clause. Essentially it is the same as the DBMS_FLASHBACK functionality.
SOL> SELECT COUNT(*) FROM flashback_query_test AS OF SCN 4933950; COUNT(*)
----------
1SOL> SELECT COUNT(*) FROM flashback_query_test AS OF SCN4934857 ; COUNT(*)
----------
0
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:
Checking Tablespaces in Database
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS
TEMP
DATA
Create New Tablespace
SQL> create tablespace india datafile
'/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf' size 30m;
Tablespace created.
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS
TEMP
DATA
INDIA
Check Datafile in whole Database
SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
-------------------- --------------------------------------------------
SYSTEM /u01/app/oracle/oradata/ORACLEAGENT/system.dbf
SYSAUX /u01/app/oracle/oradata/ORACLEAGENT/sysaux.dbf
UNDOTBS /u01/app/oracle/oradata/ORACLEAGENT/undo1.dbf
DATA /u01/app/oracle/oradata/ORACLEAGENT/data01.dbf
INDIA /u01/app/oracle/oradata/ORACLEAGENT/india01.dbf
Check Datafile's in particular Tablespace.
SQL> select file_name from dba_data_files where tablespace_name='INDIA';
FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf
CheckTablespace size in particular tablespace
SQL> select sum(bytes)/1024/1024 from dba_data_files where tablespace_name='INDIA';
SUM(BYTES)/1024/1024
--------------------
2048
How toCheckTablespace size in whole Database
SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;
TABLESPACE_NAME SUM(BYTES)/1024/1024
-------------- --------------------
SYSTEM 2048
INDIA 30
SYSAUX 2048
UNDOTBS 2048
DATA 60
How toResize Datafiles
SQL> select file_name from dba_data_files where tablespace_name='INDIA';
FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf
SQL>alter database datafile '/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf' resize60m;
Database altered.
SQL> select sum(bytes)/1024/1024 from dba_data_files where tablespace_name='INDIA';
SUM(BYTES)/1024/1024
--------------------
60
Add New Datafile
SQL> select file_name from dba_data_files where tablespace_name='INDIA';
FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf
SQL> alter tablespace india add datafile '/u01/app/oracle/oradata/ORACLEAGENT/india02.dbf' size 30m;
Tablespace altered.
SQL> select file_name from dba_data_files where tablespace_name='INDIA';
FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf
/u01/app/oracle/oradata/ORACLEAGENT/india02.dbf
Rename or Relocate datafilesafter the 12c method
SQL> select file_name from dba_data_files where tablespace_name='INDIA';
FILE_NAME
-------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf
/u01/app/oracle/oradata/ORACLEAGENT/india02.dbf
SQL> alter database move datafile '/u01/app/oracle/oradata/ORACLEAGENT/india02.dbf' to '/u02/india03.dbf';
Database altered.
SQL> select file_name from dba_data_files where tablespace_name='INDIA';
