By default there are 5 tablespaces.we can create more and more tablespaces.
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS
TEMPX
USERDATA
If we want to know the status,contents,block_size of tablespaces?
SQL> select tablespace_name,status,contents,block_size from dba_tablespaces;
TABLESPACE_NAME STATUS CONTENTS BLOCK_SIZE
------------------------------ --------- --------- ----------
SYSTEM ONLINE PERMANENT 8192
SYSAUX ONLINE PERMANENT 8192
UNDOTBS ONLINE UNDO 8192
TEMPX ONLINE TEMPORARY 8192
USERDATA ONLINE PERMANENT 8192
The default block size is 8k.system tablespace contains base tables.sysaux tablespace contains tables related to tools.
to check the datafiles for tablespaces?
SQL> select tablespace_name,file_name,file_id,bytes/1024/1024,autoextensible from dba_data_files;
TABLESPACE_NAME FILE_NAME FILE_ID BYTES/1024/1024 AUT
------------------------------ -------------------- ------- --------------- ---
SYSTEM /u01/app/oracle/syst 1 200 NO
em.dbf
SYSAUX /u01/app/oracle/sysa 2 200 NO
ux.dbf
UNDOTBS /u01/app/oracle/undo 3 100 NO
tbs.dbf
USERDATA /u01/app/oracle/user 4 100 NO
data.dbf
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS
TEMPX
USERDATA
If we want to know the status,contents,block_size of tablespaces?
SQL> select tablespace_name,status,contents,block_size from dba_tablespaces;
TABLESPACE_NAME STATUS CONTENTS BLOCK_SIZE
------------------------------ --------- --------- ----------
SYSTEM ONLINE PERMANENT 8192
SYSAUX ONLINE PERMANENT 8192
UNDOTBS ONLINE UNDO 8192
TEMPX ONLINE TEMPORARY 8192
USERDATA ONLINE PERMANENT 8192
The default block size is 8k.system tablespace contains base tables.sysaux tablespace contains tables related to tools.
to check the datafiles for tablespaces?
SQL> select tablespace_name,file_name,file_id,bytes/1024/1024,autoextensible from dba_data_files;
TABLESPACE_NAME FILE_NAME FILE_ID BYTES/1024/1024 AUT
------------------------------ -------------------- ------- --------------- ---
SYSTEM /u01/app/oracle/syst 1 200 NO
em.dbf
SYSAUX /u01/app/oracle/sysa 2 200 NO
ux.dbf
UNDOTBS /u01/app/oracle/undo 3 100 NO
tbs.dbf
USERDATA /u01/app/oracle/user 4 100 NO
data.dbf
No comments:
Post a Comment