Creation of BIGFILE TABLESPACE

-

===========Create a bigfile tablespace==========================


CREATE BIGFILE TABLESPACE DATA82 DATAFILE 

  '+DATA' SIZE 10G AUTOEXTEND ON NEXT 100M MAXSIZE 300G

LOGGING

DEFAULT 

  NO INMEMORY

ONLINE

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT AUTO

FLASHBACK ON;



=======List all the tablespaces under a schema===================


SQL> SELECT DISTINCT tablespace_name

FROM dba_segments

WHERE owner = 'APPSUSER';  2    3


TABLESPACE_NAME

------------------------------

DATA80

APPSDATA

SYSTEM

DATA81



=======Verify the Default Tablespace==============================


col USERNAME for a30

col DEFAULT_TABLESPACE for a30

SELECT username, default_tablespace

FROM dba_users

WHERE username = 'APPSUSER';


USERNAME                       DEFAULT_TABLESPACE

------------------------------ ------------------------------

APPSUSER                       APPSDATA




======Set a Default Tablespace for the Schema=====================


Note : This step is not needed if the tablespace have already default tablespace


ALTER USER APPSADMIN DEFAULT TABLESPACE DATA82;



======Grant Quota on the Tablespace (Required if Quota is Limited)======


ALTER USER APPSADMIN QUOTA UNLIMITED ON DATA82;



======Create Objects in the Assigned Tablespace (Optional)=============


CREATE TABLE Test_table (

  id NUMBER,

  name VARCHAR2(100)

) TABLESPACE DATA82;



=======List all the tablespaces under a schema==============================


SQL>  SELECT DISTINCT tablespace_name

FROM dba_segments

WHERE owner = 'APPSUSER';  2    3


TABLESPACE_NAME

------------------------------

DATA82

DATA80

APPSDATA

SYSTEM

DATA81


Comments

Post a Comment

Popular posts from this blog

Check the Oracle Database size of non CDB, CDB and PDB

-
Image
Queries for the non-CDB databases- on Check the Oracle Database size CDB and PDB Following Queries for NON-CDB database: Check the database physical consumed size select sum(bytes)/1024/1024/1024 size_in_gb from dba_data_files; Total space used by the data in the database. select sum(bytes)/1024/1024/1024 size_in_gb from dba_segments; Check the size of the User or Schema in Oracle database. select owner, sum(bytes)/1024/1024/1024 Size_GB from dba_segments group by owner; Find free space and used space in the Oracle database. select "Reserved_Space(MB)", "Reserved_Space(MB)" - "Free_Space(MB)" "Used_Space(MB)","Free_Space(MB)" from( select (select sum(bytes/(1014 1024)) from dba_data_files) "Reserved_Space(MB)", (select sum(bytes/(1024 1024)) from dba_free_space) "Free_Space(MB)" from dual ); Check overall size of database,temp and redo file. select ( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) + ...
Read more

Kill or Release PORT of LISTENER which is already in use

-
Port No. : 1521  sudo kill -9 $(sudo lsof -t -i :1521) OR [root@test ~]# sudo netstat -tulnp | grep 1521 tcp        0      0 127.0.0.1:1521          0.0.0.0:*               LISTEN      3168/tnslsnr [root@test ~]# sudo ss -tulnp | grep 1521 tcp   LISTEN 0      128         127.0.0.1:1521       0.0.0.0:*    users:(("tnslsnr",pid=3168,fd=8)) [root@test ~]# sudo kill -9 3168 [root@test ~]# sudo ss -tulnp | grep 1521
Read more

Confusion of Service Name in Oracle Networking in 12c,19c, 21c,23c Oracle Database

-
Image
********For Connection through SQL Developer (or any IDE) using service name *************  Let Suppose, sid,db_name,db_unique_name,instance_name = oradb (are same by default installation in Single Instances) service_names (in DB) =oradb (by default)  Suppose, we set service names (in DB) = ora 1. Service name inside oracle database. show parameter service_names; 2. Global names should be same in case of Static Listener.  3. For Connection through SQL developer using service should match above criteria. Service name = " ora " will be used   ******************For Connection through " tnsnames.ora " alias name ********************* Let Suppose, SID = oradb service_names (in DB) =oradb (by default)  Suppose, we set service names (in DB) = ora We can give Alias name in tnsnames.ora file whatever like oradb, but there service name must be the ora. If we set service name in tnsnames.ora or in db. We must bounce the Listener. For Testing TNS connection is working...
Read more