segment type on tablespace

segment type on tablespace
=============================================================================


-- segment_type_per_tbs.sql

set linesize 200 pages 9999
col bytesh for a12
col OWNER for a20
column counted format 99G999
column mb format 9G999G999D99
compute sum of mb on owner report
compute sum of counted on owner report

break on owner skip 1 on tablespace_name on report

select owner, tablespace_name, segment_type, sum(bytes)/1024/1024 MB,dbms_xplan.format_size(sum(bytes)) bytesh ,count(*) counted
from dba_segments
where owner not in ( 'DBSNMP', 'DIP', 'MGMT_VIEW', 'ORACLE_OCM', 'OUTLN', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 'WMSYS' )
group by owner, tablespace_name, segment_type
order by owner, tablespace_name, segment_type;

===================================
--- all the cdb 
set linesize 200 pages 9999
col bytesh for a12
col OWNER for a20
column counted format 99G999
column mb format 9G999G999D99
compute sum of mb on owner report
compute sum of counted on owner report

break on owner skip 1 on tablespace_name on report

select con_id,owner, tablespace_name, segment_type, sum(bytes)/1024/1024 MB,dbms_xplan.format_size(sum(bytes)) bytesh ,count(*) counted
from cdb_segments
where owner not in ( 'DBSNMP', 'DIP', 'MGMT_VIEW', 'ORACLE_OCM', 'OUTLN', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 'WMSYS' )
group by  con_id,owner, tablespace_name, segment_type
order by con_id,owner, tablespace_name, segment_type;

ORA-01012: not logged on Process ID: 0 Session ID: 0 Serial number: 0

idle sqlplus> startup nomount pfile='/u01/app/oracle/product/12.2.0/dbhome_1/dbs/initvihcdbd8x.ora';
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0





 sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 10 05:36:54 2025

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected.
idle sqlplus> startup ;
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0





[oracle@rac02 dbs]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Mar 10 05:43:36 2025

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-01089: immediate shutdown or close in progress - no operations are permitted


**************************************************************************************************************************************************************

Before this  Cannot login to the Database after Changing $ORACLE_HOME Directory Permission ( ORA-01012 ) (Doc ID 2497191.1)


Try below !!!!!!!!!!!!!!!!


https://anuj-singh.blogspot.com/2013/12/how-to-use-prelim-option-in-sqlplus.html


 echo $ORACLE_SID
vihcdbd8x

[oracle@ibrac02 dbs]$ echo $ORACLE_HOME
/u01/app/oracle/product/12.2.0/dbhome_1

[oracle@rac02 dbs]$






[oracle@rac02 dbs]$ sqlplus -prelim

SQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 10 05:48:28 2025

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Enter user-name: / as sysdba

idle sqlplus> shutdown abort ;
ORACLE instance shut down.


idle sqlplus>


pwd
/u01/app/oracle/product/12.2.0/dbhome_1



idle sqlplus> startup nomount pfile='initvihcdbd8x.ora'
ORACLE instance started.

Total System Global Area 2.2750E+10 bytes
Fixed Size                 19421832 bytes
Variable Size            3355444600 bytes
Database Buffers         1.9327E+10 bytes
Redo Buffers               47685632 bytes
idle sqlplus>

Oracle copy command !!!!

Oracle copy command !!!! ==================================================================

SQL> alter session set container=PDB1 ;

Session altered.



SQL> create table dba_table1 as select * from dba_tables where 1=1 ;

Table created.



SET ARRAYSIZE 1000 COPYCOMMIT 2

copy from system/sys@//192.168.1.120:1521/pdb1.localdomain insert dba_table1 using select * from dba_tables ;

Array fetch/bind size is 1000. (arraysize is 1000)
Will commit after every 2 array binds. (copycommit is 2)
Maximum long size is 80. (long is 80)
   2185 rows selected from system@//192.168.1.120:1521/pdb1.localdomain.
   2185 rows inserted into DBA_TABLE1.
   2185 rows committed into DBA_TABLE1 at DEFAULT HOST connection.




SQL> select count(*) from dba_table1 ;

  COUNT(*)
----------
      4369




SQL>
SET ARRAYSIZE 1000 COPYCOMMIT 2

copy from system/sys@//192.168.1.120:1521/pdb1.localdomain insert dba_table1 using select * from dba_tables ;
Array fetch/bind size is 1000. (arraysize is 1000)
Will commit after every 2 array binds. (copycommit is 2)
Maximum long size is 80. (long is 80)
   2185 rows selected from system@//192.168.1.120:1521/pdb1.localdomain.
   2185 rows inserted into DBA_TABLE1.
   2185 rows committed into DBA_TABLE1 at DEFAULT HOST connection.

SQL> select count(*) from dba_table1 ;

  COUNT(*)
----------
      6554

SQL>


copy from system/sys@//192.168.1.120:1521/pdb1.localdomain append dba_table1 using select * from dba_tables ;


copy from system/sys@//192.168.1.120:1521/pdb1.localdomain append dba_table1 using select * from dba_tables ;SQL>

Array fetch/bind size is 1000. (arraysize is 1000)
Will commit after every 2 array binds. (copycommit is 2)
Maximum long size is 80. (long is 80)
   2185 rows selected from system@//192.168.1.120:1521/pdb1.localdomain.
   2185 rows inserted into DBA_TABLE1.
   2185 rows committed into DBA_TABLE1 at DEFAULT HOST connection.

SQL> select count(*) from dba_table1 ;

  COUNT(*)
----------
      8739


===


SQL> COPY FROM system/sys@//192.168.1.120:1521/pdb1.localdomain TO system/sys@//192.168.1.120:1521/pdb1.localdomain  APPEND DBA_TABLE1 using select * from dba_tables ;

Array fetch/bind size is 1000. (arraysize is 1000)
Will commit after every 2 array binds. (copycommit is 2)
Maximum long size is 80. (long is 80)
Table DBA_TABLE1 created.

   2186 rows selected from system@//192.168.1.120:1521/pdb1.localdomain.
   2186 rows inserted into DBA_TABLE1.
   2186 rows committed into DBA_TABLE1 at system@//192.168.1.120:1521/pdb1.localdomain.

SQL>