SQL> select 'create tablespace ' || df.tablespace_name || chr(10)
|| ' datafile ''' || df.file_name || ''' size ' || df.bytes
2 || decode(autoextensible,'N',null, chr(10) || ' autoextend on maxsize '
|| maxbytes)
|| chr(10)
|| 'default storage ( initial ' || initial_extent
3 4 5 6 || decode (next_extent, null, null, ' next ' || next_extent )
|| ' minextents ' || min_extents
|| ' maxextents ' || decode(max_ 7 extents,'2147483645','unlimited',max_extents)
|| ') ;' as TBS_DDL
from dba_data_files df, dba_tablespaces t
where df.tablespace_name=t.tablespace_name
/ 8 9 10 11 12 13
TBS_DDL
----------------------------------------------------------------------
create tablespace SYSTEM
datafile '/oradata/vijay/system01.dbf' size 838860800
autoextend on maxsize 34359721984
default storage ( initial 65536 minextents 1 maxextents unlimited) ;
create tablespace SYSAUX
datafile '/oradata/vijay/sysaux01.dbf' size 503316480
autoextend on maxsize 34359721984
default storage ( initial 65536 minextents 1 maxextents unlimited) ;
create tablespace UNDOTBS1
datafile '/oradata/vijay/undotbs01.dbf' size 62914560
autoextend on maxsize 34359721984
default storage ( initial 65536 minextents 1 maxextents unlimited) ;
create tablespace USERS
datafile '/oradata/vijay/users01.dbf' size 5242880
autoextend on maxsize 34359721984
default storage ( initial 65536 minextents 1 maxextents unlimited) ;
DDL for all tablespace in oracle database.
How to get self SID in Oracle
SQL> SELECT SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA') AS username, sys_context('USERENV','SID') "My SID" from dual;
USERNAME My SID
-------------- --------
SHAAN 138
SQL> select sys_context('USERENV','SID') "My SID" from dual;
My SID
--------
138
Last DDL and DML Date/Time of Any Table
Get Last DDL and DML Date Time Of Any Table.
SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> select (select last_ddl_time from dba_objects where object_name='EMP' and owner='SHAAN') "DDL Time",
decode(maxscn,0,'N/A',scn_to_timestamp(maxscn)) "DML Time"
from (select nvl(max(ora_rowscn),0) maxscn from SHAAN.EMP); 2 3
DDL Time DML Time
-------------------- ----------------------------------------
05-OCT-2022 22:30:16 05-OCT-22 10.31.21.000000000 PM
SQL> col "Owner Object" for a30
set lines 200 pages 1000
select (select owner||'.'||object_name from dba_objects where object_name='EMP' and owner='SHAAN') "Owner Object",
(select created from dba_objects where object_name='EMP' and owner='SHAAN') "Created Time",
(select last_ddl_time from dba_objects where object_name='EMP' and owner='SHAAN') "DDL Time",
decode(maxscn,0,'N/A',scn_to_timestamp(maxscn)) "DML Time"
from (select nvl(max(ora_rowscn),0) maxscn from SHAAN.EMP);SQL> SQL> 2 3 4 5
Owner Object Created Time DDL Time DML Time
------------------------------ -------------------- -------------------- ----------------------------------------
SHAAN.EMP 05-OCT-2022 22:30:16 05-OCT-2022 22:30:16 05-OCT-22 10.31.21.000000000 PM
ORA-65096: invalid common user or role name
ORA-65096: invalid common user or role name means you logged on the CDB where you should be logged into a PDB.
This can be avoid by setting hidden parameter "_ORACLE_SCRIPT"=true.
Since this hidden parameter hence it is always advisable to use it under direction of Oracle Support.
SQL> create user shaan identified by shaan123 default tablespace users quota unlimited on users;
create user shaan identified by shaan123 default tablespace users quota unlimited on users
*
ERROR at line 1:
ORA-65096: invalid common user or role name
SQL> alter session set "_ORACLE_SCRIPT"=true;
Session altered.
SQL> create user shaan identified by shaan123 default tablespace users quota unlimited on users;
User created.
SQL> grant connect, resource to shaan;
Grant succeeded.
SQL> conn shaan/shaan123
Connected.
SQL> show user
USER is "SHAAN"