A tablespace is a storage location where the actual data underlying database objects can be kept. It provides a layer of abstraction between physical and logical data, and serves to allocate storage for all DBMS managed segments.
A tablespace is a logical group of data files in a database. A database typically contains at least one tablespace, and usually two or more. Within the database, the tablespace plays a role similar to that of a folder on the hard drive of a computer.
Datafile : Describe all database files , Datafiles are physical files stored on your disk created by Oracle database and has .dbf extension.
CREATE DBA DATA FILES TABLESPACE
We have to find the file location in DBA DATA FILES
Now here describing dba data files
SQL> desc dba_data_files;
We can use one of them
SQL> describe dba_data_files;
Here Selection Specific columns
DBA DATA FILES Selecting the FILE_NAME
SQL> SELECT FILE_NAME FROM dba_data_files;
TABLESPACENAME describe including with Created files name users
If we will create new TABLESPACENAME it will also reflect in File Name.
SQL> SELECT FILE_NAME, TABLESPACE_NAME FROM dba_data_files;
Now I am going to change the Bytes into MB
Now you can see here I have converted BYTES in MB.
SQL> select FILE_NAME,TABLESPACE_NAME,BYTES/1024 as size_in_mb from dba_data_files;
Now converting BYTES size in GB
SQL> select FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024 as size_in_gb from dba_data_files;
Now started to create tablespace
We need to know in which directory tablespace file used to create
So here
SQL> Select FILE_NAME from dba_data_files;
So We found the tablespace directory .
Whenever we are creating tablespace we used to create tablespace in oracle default directory including with path in string format.
(extention of tablespace .dbf)
SQL> create tablespace querypanel26 datafile ‘/u01/app/oracle/oradata/q
ueryp/querypanel26.dbf’ size 400m autoextend on;
Now you can see one tablespace is included in tablespace directory.
DATAFILE : before add datafile in tablespace we must have to aware about data files create location . How do we find datafiles storage location
SQL> desc dba_data_files;
Now we are looking for File Name and tablespace
so we promote a select query
SQL> select FILE_NAME,TABLESPACE_NAME from dba_data_files;
Here ADD datafile
SQL> alter tablespace q04 add datafile ‘/u01/app/oracle/oradata/queryp/q04datafile.dbf’ size 5m autoextend on;
we can also assign unlimited tablespace
sql>grant unlimited tablespace to q02;
Lock & Unlock Users
How do lock users account and unlock account.
SQL> alter user query1 account lock;
Now we need to find the user account status.
SQL> select USERNAME,ACCOUNT_STATUS from dba_users;
Here you can see query1 user account is locked
Now need to unlock user account
SQL> alter user query1 account unlock;
Now here user is unblock
SQL> select USERNAME,ACCOUNT_STATUS from dba_users;
User password Expire
We could not login via old password
SQL> alter user query1 password expire;
Whenever we will used to expire password need to set a new password during the user login
User session
Note: When it is in read/write mode, the status will simply say “ONLINE”
Rename Tablespace
SQL> alter tablespace Q05 OFFLINE normal;
SQL> alter tablespace Q05 RENAME TO QP05;
Assign Tablespace while creation of new users
here i have mention specific tablespace to user
SQL> create user q3 identified by q3 default tablespace sept_q3;
we can check here default tablespace to user
Q3 User default tablespace is sept_q3 because i used to mention this particular user carry this tablespace
we can also tablespace while we create table
easy to assign tablespace also like this
SQL> create table q3_insert_1(q_id INT NOT NULL PRIMARY KEY,NAME VARCHAR(20)) tablespace sept_q3;
A temporary tablespace, as its name implied, stores the temporary data that only exists during the database session.
Oracle uses temporary tablespaces to improve the concurrency of multiple sort operations which do not fit in memory. On top of this, Oracle stores temporary tables, temporary indexes, temporary B-trees, and temporary LOBs in temporary tablespaces.
By defeault, Oracle creates a single temporary tablespace named TEMP for each new Oracle Database installation. This TEMP tablespace can be shared by multiple users.
Now here we are going to create temporary tablespace
SQL> CREATE TEMPORARY TABLESPACE q3temp_1 TEMPFILE ‘/u02/app/oracle/oradata/sept/q3temp_1.dbf’ SIZE 40m;
now we can check temporary tablespace in dba_temp_files
we can see temporary tablespace is created
