0 like 0 dislike
42 views

Please log in or register to answer this question.

1 Answer

0 like 0 dislike
answered by (4k points)  

Steps To create oracle tablespace :

1. Initially check the datafile location , you have to create tablespace in that location only. below command will help you to check the data file location

SQL>  select tablespace_name,file_name,bytes/1024/1024 as "used MB" from dba_data_files;

TABLESPACE_NAME                FILE_NAME                                                       used MB
------------------------------ ------------------------------------------------------------ ----------
SYSTEM                         /oradata/CDMPROD/system01.dbf                                       820
SYSAUX                         /oradata/CDMPROD/sysaux01.dbf                                      1110
TBL_CDM_PERM_01                /oradata/CDMPROD/tbl_cdm_prem_01.dbf                              32765
USERS                          /oradata/CDMPROD/users01.dbf                                   16266.25
IDX_CDM_01                     /oradata/CDMPROD/idx_cdm_01.dbf                                       5
UNDOTBS1                       /oradata/CDMPROD/undotbs01.dbf                                     9805
DAI_SECDATA                    /oradata/CDMPROD/dai_secdata01CDMPROD.dbf                            10
TBL_CDM_PERM_01                /oradata/CDMPROD/tbl_cdm_prem_02.dbf                              32764
TBL_CDM_PERM_01                /oradata/CDMPROD/tbl_cdm_prem_03.dbf                              32764
TBL_CDM_PERM_01                /oradata/CDMPROD/tbl_cdm_prem_04.dbf                              32764
TBL_CDM_PERM_01                /oradata/CDMPROD/tbl_cdm_prem_05.dbf                              32765
TBL_CDM_PERM_01                /oradata/CDMPROD/tbl_cdm_prem_06.dbf                               8768
TBL_CDM_PERM_01                /oradata/CDMPROD/tbl_cdm_prem_07dbf                                8768

13 rows selected.

​​​​​​​​​​​​​​​​​​​​​2.  Other way is check the data file destination location if parameter is enabled  you can create the tablespace without specifying the datafile location it will take the datafile location of parameter location .

To checking  default datafile location :

SQL>  show parameter DB_CREATE_FILE_DEST;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string

otherwise you have to enable the Default data file path :

 alter system set db_create_file_dest= '/oradata' scope=spfile;

Otherwise you can enable the Default data file path then only can create the tablespace without giving the datafile path

ALTER SYSTEM SET DB_CREATE_FILE_DEST = ' /oradata';

SQL>  show parameter DB_CREATE_FILE_DEST;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /oradata/

Now you can issue create tablesapce statement only :

create tablesapce TBL_CDM_PERM_01;

SQL>  select tablespace_name,file_name,bytes/1024/1024 as "used MB" from dba_data_files;

TABLESPACE_NAME                FILE_NAME                                                       used MB
------------------------------ ------------------------------------------------------------ ----------
SYSTEM                         /oradata/CDMPROD/system01.dbf                                       820
SYSAUX                         /oradata/CDMPROD/sysaux01.dbf                                      1110
TBL_CDM_PERM_01                /oradata/CDMPROD/tbl_cdm_prem_01.dbf                              32765
USERS                          /oradata/CDMPROD/users01.dbf                                   16266.25
IDX_CDM_01                     /oradata/CDMPROD/idx_cdm_01.dbf                                       5
UNDOTBS1                       /oradata/CDMPROD/undotbs01.dbf                                     9805
DAI_SECDATA                    /oradata/CDMPROD/dai_secdata01CDMPROD.dbf                            10
TBL_CDM_PERM_01                /oradata/CDMPROD/tbl_cdm_prem_02.dbf                              32764
TBL_CDM_PERM_01                /oradata/CDMPROD/tbl_cdm_prem_03.dbf                              32764
TBL_CDM_PERM_01                /oradata/CDMPROD/tbl_cdm_prem_04.dbf                              32764
TBL_CDM_PERM_01                /oradata/CDMPROD/tbl_cdm_prem_05.dbf                              32765
TBL_CDM_PERM_01                /oradata/CDMPROD/tbl_cdm_prem_06.dbf                               8768
TBL_CDM_PERM_01                /oradata/CDMPROD/tbl_cdm_prem_07dbf                                8768
TBL_CDM_PERM_01                /oradata/CDMPROD/tbl_cdm_prem_08.dbf                               8704

If you want create tablespace to some custom path means you have to issue following command;

CREATE TABLESPACE CDMPROD_VN_tab DATAFILE '/oradata/CDMSIT/datafile/CDMPROD_VN_tab.dbf' SIZE 500K REUSE AUTOEXTEND ON NEXT 500K MAXSIZE unlimited;

Then create the user and assign the tablesapce to that particular user

Create user CDMPROD_VN idenetified by CDMPROD_VN tablespace CDMPROD_VN_tab;  

 

Related questions

0 like 0 dislike
0 answers
0 like 0 dislike
2 answers
0 like 0 dislike
0 answers
0 like 0 dislike
1 answer
Welcome to PostgreSQL Database Discussion Forum where you can ask questions and receive answers from other members of the community. Can discuss here Oracle, Postgresql, mariadb , mySQL , AWS , Linux , MSSQL , MongoDB , Greenplum databases related queries ...etc.
...