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

Enabling Autostart during server reboot of Oracle Database 19c filesystem on Oracle / Redhat Linux 7

-
============= Create a file /etc/init.d/dbora using dbora ================ vi /etc/init.d/dbora #! /bin/sh  -x # # This is my old script for auto-starting Oracle DB on Linux. # When I'm not using Cloud Databases, I now install the Oracle Database 21c RPM which includes its own script # # chkconfig: 2345 80 05 # description: start and stop Oracle Databases # # OL7: systemctl enable dbora # # Note: #   Change the awk pattern to match a DB of the ORACLE_HOME to be used #   Change the autostart entry of /etc/oratab to Y for any DB you want started; this is required by dbstart ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1 # Note: Change the value of ORACLE to the login name of the oracle owner ORACLE=oracle PATH=${PATH}:$ORACLE_HOME/bin export ORACLE_HOME PATH case $1 in 'start')         echo -n $"Starting Oracle: "         su $ORACLE -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME" &         ;; 'stop') ...
Read more

Management of Users and privilleges in Oracle Database 12c / 19c in container & Pluggable database

-
 [oracle@LNX-D1V-ORCL01 ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Fri Mar 15 07:59:58 2024 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle.  All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> show pdbs;     CON_ID CON_NAME                       OPEN MODE  RESTRICTED ---------- ------------------------------ ---------- ----------          2 PDB$SEED                       READ ONLY  NO          3 PDB                            READ WRITE NO   Create the common user using the CONTAINER clause SQL> create user c##user1 identified by c##user1 container=all; User created. SQL> grant create session to c#...
Read more

Oracle Temporary Tablespace Resize

-
  Description:- In this article I'm going to see how to resize Temp Tablespace on PDB database level Demo:- Login in to PDB Database SQL> alter session set container=ORCLPDB; Session altered. First Check Temporary Tablespace Usage From PDB Level:- SELECT A.tablespace_name tablespace, D.GB_total,SUM (A.used_blocks * D.block_size) / 1024 / 1024 / 1024 GB_used,D.GB_total , SUM (A.used_blocks * D.block_size) / 1024 / 1024 / 1024 GB_free FROM v$sort_segment A, ( SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 / 1024 GB_total FROM v$tablespace B, v$tempfile C WHERE B.ts#= C.ts# GROUP BY B.name, C.block_size ) D WHERE A.tablespace_name = D.name GROUP by A.tablespace_name, D.GB_total; TABLESPACE GB_TOTAL GB_USED GB_FREE —————————— ———- ———- ———- TEMP               .034179688            0             .034179688 From CDB level:- To find CDB and PDB temporary tablespace files s...
Read more