ORA-12916: Cannot use default permanent tablespace with this release

COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A30
COLUMN description FORMAT A50
SET LINESIZE 200

SELECT *
FROM database_properties
WHERE property_name like '%TABLESPACE';

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION

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

DEFAULT_TEMP_TABLESPACE TEMP1 ID of default temporary tablespace

DEFAULT_PERMANENT_TABLESPACE SYSTEM Default Permanent Tablespace ID





SQL> ALTER DATABASE DEFAULT TABLESPACE USERS ;

ALTER DATABASE DEFAULT TABLESPACE USERS

*

ERROR at line 1:

ORA-12916: Cannot use default permanent tablespace with this release




if " *.compatible='9.2.0.0.0' " this parameter set then you can't change .

Is My Linux 32 bit or 64 bit ?

Quick way to find out

anuj-amd-02:/home/anujs/Downloads/Goldengate # uname -m
x86_64


oracle@anuj:~> uname -m
i686

How to grant select on v$session (or v$ ) views

v$session is synonyms of v_$session .


SQL> col OWNER format a15

SQL> col OBJECT_NAME like OWNER

SQL> col OBJECT_TYPE like OWNER



SQL> r

1* select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where OBJECT_NAME='V$SESSION'



OWNER OBJECT_NAME OBJECT_TYPE

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

PUBLIC V$SESSION SYNONYM


SQL> grant select on v$session to prod1;

grant select on v$session to cccprod1
*
ERROR at line 1:

ORA-02030: can only select from fixed tables/views


you can't give grant to synonyms



SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where OBJECT_NAME='V_$SESSION';



OWNER OBJECT_NAME OBJECT_TYPE

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

SYS V_$SESSION VIEW





SQL> grant select on v_$session to prod1;



Grant succeeded.

Oracle ORA_ROWSCN Pseudocolumn and AS OF Flashback Query

SQL> CREATE TABLE anuj (c1 NUMBER, c2 VARCHAR2(16), c3 DATE) ;


SQL> INSERT INTO anuj VALUES (1,'A',SYSDATE);



1 row created.



SQL> INSERT INTO anuj VALUES (1,'B',SYSDATE);



1 row created.



SQL> commit;



Commit complete.



SQL> SELECT ORA_ROWSCN, a.* FROM anuj a;



ORA_ROWSCN C1 C2

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

C3

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

45111390 1 A

18-AUG-10



45111390 1 B

18-AUG-10

Oracle The OPatch Utility to check oracle patch information

cd $ORACLE_HOME
oracle@solomon:/opt/oracle/product/10.2/OPatch>

cd OPatch

oracle@solomon:/opt/oracle/product/10.2/OPatch> ./opatch lsinventory –detail –oh /opt/oracle/product/10.2
Invoking OPatch 10.2.0.4.2

Oracle Interim Patch Installer version 10.2.0.4.2
Copyright (c) 2007, Oracle Corporation. All rights reserved.


Oracle Home : /opt/oracle/product/10.2
Central Inventory : /opt/oracle/oraInventory
from : /etc/oraInst.loc
OPatch version : 10.2.0.4.2
OUI version : 10.2.0.4.0
OUI location : /opt/oracle/product/10.2/oui
Log file location : /opt/oracle/product/10.2/cfgtoollogs/opatch/opatch2010-08-16_14-26-22PM.log

Lsinventory Output file location : /opt/oracle/product/10.2/cfgtoollogs/opatch/lsinv/lsinventory2010-08-16_14-26-22PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (3):

Oracle Database 10g 10.2.0.1.0
Oracle Database 10g Products 10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 3 10.2.0.4.0
There are 3 products installed in this Oracle Home.


Interim patches (1) :

Patch 8340387 : applied on Tue Apr 14 11:50:02 GMT 2009
Created on 24 Mar 2009, 00:28:01 hrs PST8PDT
Bugs fixed:
8340387, 7528105, 7196894



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

OPatch succeeded.



For Path !!!
PATH=$PATH:$ORACLE_HOME/OPatch




env|grep -i ora
HOSTNAME=cloud-ora
ORACLE_UNQNAME=ora12
USER=oracle
LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0/db_1/lib:/lib:/usr/lib
ORACLE_SID=ora12c
ORACLE_BASE=/u01/app/oracle
MAIL=/var/spool/mail/oracle
PATH=/u01/app/oracle/product/12.1.0/db_1/bin:/usr/sbin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin:/u01/app/oracle/product/12.1.0/db_1/OPatch
PWD=/home/oracle
HOME=/home/oracle
LOGNAME=oracle
CLASSPATH=/u01/app/oracle/product/12.1.0/db_1/jlib:/u01/app/oracle/product/12.1.0/db_1/rdbms/jlib
ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1


PATH=$PATH:$ORACLE_HOME/OPatch



opatch lsinventory -oh /u01/app/oracle/product/12.1.0/db_1 -detail
Oracle Interim Patch Installer version 12.1.0.1.3
Copyright (c) 2018, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/12.1.0/db_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.1.0/db_1/oraInst.loc
OPatch version    : 12.1.0.1.3
OUI version       : 12.1.0.2.0
Log file location : /u01/app/oracle/product/12.1.0/db_1/cfgtoollogs/opatch/opatch2018-01-06_12-23-36PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/12.1.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2018-01-06_12-23-36PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Database 12c                                                  12.1.0.2.0
There are 1 products installed in this Oracle Home.


Installed Products (135):

Assistant Common Files                                               12.1.0.2.0
Buildtools Common Files                                              12.1.0.2.0
Cluster Verification Utility Common Files                            12.1.0.2.0
Database Configuration and Upgrade Assistants                        12.1.0.2.0
Database Migration Assistant for Unicode                             12.1.0.2.0
Database SQL Scripts                                                 12.1.0.2.0
Database Workspace Manager                                           12.1.0.2.0

opatch util listorderedinactivepatches

--  opatch util deleteinactivepatches

opatch util cleanup

$ORACLE_HOME/OPatch/opatch lsinventory | grep -E "(^Patch.*applied)|(^Sub-patch)"

sqlplus / as sysdba hanged ...

if sqlplus / as sysdba hanged .... ( and database down ) .. then try this




oracle@solomon:~> rman

Recovery Manager: Release 10.2.0.4.0 - Production on Mon Aug 16 11:02:28 2010

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

RMAN> connect target /

connected to target database (not started)

RMAN> startup;

Oracle instance started
database mounted
database opened

Total System Global Area 608174080 bytes

Fixed Size 1268896 bytes
Variable Size 213910368 bytes
Database Buffers 385875968 bytes
Redo Buffers 7118848 bytes

Oracle data pump export through cron

# !/bin/bash

TODAY=`date`

ORACLE_SID=cccdb ; export ORACLE_SID

ORACLE_BASE=/opt/oracle ; export ORACLE_BASE

ORACLE_HOME=/opt/oracle/product/10.2 ; export ORACLE_HOME

PATH=${ORACLE_HOME}/bin:$PATH ; export PATH

EXPFILE=$ORACLE_SID.datapump.`date +%m%d%Y`.dmp; export EXPFILE

ERRFILE=$ORACLE_SID.datapump.`date +%m%d%Y`.err; export ERRFILE

LOGFILE=$ORACLE_SID.datapump.`date +%m%d%Y`.log ; export LOGFILE

SUCC=/opt/oracle/backup/DataPump/Backup/SUCC.txt ; export SUCC



# echo $EXPFILE



find /opt/oracle/backup/DataPump/Backup -name "*.err" -ctime +1 -exec rm {} \;





find /opt/oracle/backup/DataPump/Backup -name "*.dmp*" -ctime +1 -exec rm {} \;



ps -ef | grep -i smon|grep -v grep|wc -l> oracle.exp



if [ `cat oracle.exp|grep -v grep|grep -i "1"|wc -l` -eq 1 ]; then



[ -f $ERRFILE ]; rm $ERRFILE



expdp "'/ as sysdba'" directory=datapump DUMPFILE=$EXPFILE SCHEMAS=CCCPROD1,CCCUAT2,ESEARCH logfile=$LOGFILE EXCLUDE=STATISTICS

# parallel=4



fi



# find /opt/oracle/backup/DataPump/Backup -name "$ORACLE_SID.export.*" -mtime +2 -exec rm {} \;

List all srvctl commands

List all srvctl commands

srvctl –h

Shutdown service

srvctl stop service –d database_name

Shutdown the database on all nodes

srvctl stop database –d database_name

Shutdown a specific instance

srvctl stop instance –d database_name -i instance_name

Shutdown nodeapps (includes listener)

srvctl stop nodeapps –n node_name

! node_name is usually the hostname of the machine

Shutdown the listener

srvctl stop listener –n node_name

Shutdown ASM

srvctl stop asm –n node_name

To Startup, replace stop with start.

If you shutdown the database it will stop the service since the service is dependent upon the database. The reverse is also true. If you startup the service it will implicitly startup the database.

To check to see if your services are running.

$CRS_HOME/bin/crs_stat –t

If you want more detailed information, drop the –t.

$CRS_HOME/bin/crs_stat

You may include the $CRS_HOME/bin in your $PATH for ease of use

=====================================================================================
crs_stat command



crs_stat | awk -F= '/NAME=/{n=$2}/TYPE=/{t=$2}/TARGET=/{g=$2}/STATE=/{s=$2; printf("%-30s%-30s%-10s%-30s\n", n,t,g,s)}'
ora.DATA.dg ora.diskgroup.type ONLINE ONLINE on oracle-host01
ora.LISTENER.lsnr ora.listener.type ONLINE ONLINE on oracle-host01
ora.asm ora.asm.type ONLINE ONLINE on oracle-host01
ora.cssd ora.cssd.type ONLINE ONLINE on oracle-host01
ora.diskmon ora.diskmon.type ONLINE ONLINE on oracle-host01
ora.evmd ora.evm.type ONLINE ONLINE on oracle-host01
ora.ons ora.ons.type ONLINE ONLINE on oracle-host01
ora.orc11202.db ora.database.type ONLINE ONLINE on oracle-host01

Oracle Invalid objects .... 

object info 

define OBJECT_NAME='xxxxxxx'

set linesize 300 

col OWNER for a15

col OBJECT_NAME for a25

select  *   from   (select 1 con_id,OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from dba_objects where OBJECT_NAME='&OBJECT_NAME'

                    union 

                    select con_id, OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from cdb_objects where OBJECT_NAME='&&OBJECT_NAME'

                  )

;

======

@$ORACLE_HOME/rdbms/admin/utlrp.sql

utlprp.sql =

DECLARE

   threads pls_integer := &&1;

BEGIN

   utl_recomp.recomp_parallel(threads);

END;

/

declare

threads pls_iteger :=&&1;

begin 

utl_recomp.recomp_parallel(threds, flags =>utl_recomp.oracle_maintained);

end;

or 

define threds=4

exec utl_recomp.recomp_parallel(&threds, flags =>utl_recomp.oracle_maintained);  --- <<<<< Only for Oracle manage objects 

======================

set recsep off

column invalid_object format A30

column likely_reason format A35 word_wrapped

break on type on invalid_object

select owner || '.' || object_name invalid_object,'--- ' || object_type || ' ---' likely_reason from dba_objects

where status = 'INVALID'

union

select d.owner || '.' || d.name,'Non-existent referenced db link ' || d.referenced_link_name from dba_dependencies d

where not exists (select 'x'from dba_db_links

                  where owner in ('PUBLIC', d.owner) 

                  and db_link = d.referenced_link_name

                 )

and d.referenced_link_name is not null

and (d.owner, d.name, d.type) in (select owner, object_name, object_type from dba_objects where status = 'INVALID')

union

select d.owner || '.' || d.name,'Depends on invalid ' || d.referenced_type || ' '|| d.referenced_owner || '.' || d.referenced_name from dba_objects ro,dba_dependencies d

where ro.status = 'INVALID'

and ro.owner    = d.referenced_owner

and ro.object_name = d.referenced_name

and ro.object_type = d.referenced_type

and d.referenced_link_name is null

and (d.owner, d.name, d.type) in (select owner, object_name, object_type from  dba_objects where status = 'INVALID')

union

select d.owner || '.' || d.name,'Depends on newer ' || d.referenced_type || ' '|| d.referenced_owner || '.' || d.referenced_name from dba_objects ro,dba_dependencies d,dba_objects o

where nvl(ro.last_ddl_time, ro.created) > nvl(o.last_ddl_time, o.created)

and ro.owner = d.referenced_owner

and ro.object_name = d.referenced_name

and ro.object_type = d.referenced_type

and d.referenced_link_name is null

and d.owner  = o.owner

and d.name   = o.object_name

and d.type   = o.object_type

and o.status = 'INVALID'

union

select d.owner || '.' || d.name,'Depends on ' || d.referenced_type || ' '|| d.referenced_owner || '.' || d.referenced_name|| decode(d.referenced_link_name,NULL, '','@' || d.referenced_link_name) from dba_dependencies d

where d.referenced_owner != 'PUBLIC' -- Public synonyms generate noise

and d.referenced_type = 'NON-EXISTENT'

and (d.owner, d.name, d.type) in (select owner, object_name, object_type from dba_objects where status = 'INVALID')

union

select d.owner || '.' || d.name invalid_object,'No privilege on referenced ' || d.referenced_type || ' '|| d.referenced_owner || '.' || d.referenced_name from dba_objects ro,dba_dependencies d 

where not exists (select 'x' from dba_tab_privs p

                  where p.owner = d.referenced_owner

                  and p.table_name = d.referenced_name

                  and p.grantee in ('PUBLIC', d.owner)

                  )

and ro.status = 'VALID'

and ro.owner  = d.referenced_owner

and ro.object_name = d.referenced_name

and d.referenced_link_name is not null

and (d.owner, d.name, d.type) in (select owner, object_name, object_type from dba_objects where status = 'INVALID')

union

select o.owner || '.' || o.object_name,e.text from dba_errors e,dba_objects o

where e.text like 'PLS-%'

and e.owner  = o.owner

and e.name   = o.object_name

and e.type   = o.object_type

and o.status = 'INVALID';

===============

Components valid / invalid 

set lines 90

col version  for a12

col comp_id  for a8

col comp_name  for a35

col status for  a12

col schema like version

select comp_id,schema,status,version,comp_name from dba_registry 

order by 1;

============

set lines 120

col status   for a9

col object_type  for a20;

col owner.object for a50

select status, object_id, object_type, owner||'.'||object_name "OWNER.OBJECT"

from dba_objects

where 0=0 

and status != 'VALID' 

and object_name not like 'BIN$%' 

order by 4,2;

=====

set serveroutput on

BEGIN

   FOR r

   IN (  SELECT      'alter '

                  || REPLACE (object_type, 'BODY', '')

                  || ' '

                  || owner

                  || '.'

                  || object_name

                  || ' compile '

                  || DECODE (object_type, 'PACKAGE BODY', 'BODY')

                     stmt

           FROM   dba_objects

          WHERE   status = 'INVALID'

       ORDER BY   owner,

               DECODE (object_type,

                          'SYNONYM', 1,

                          'VIEW', 2,

                          'FUNCTION', 3,

                          'PROCEDURE', 4,

                          'PACKAGE', 7,

                          'PACKAGE BODY', 8,

                          9))

   LOOP

      BEGIN

        -- EXECUTE IMMEDIATE (r.stmt);

dbms_output.put_line(r.stmt||' ;');

      EXCEPTION

         WHEN OTHERS

         THEN

            NULL;

      END;

   END LOOP;

END;

/

===

set pagesize 0

select 

'alter '

|| decode(object_type, 

'PACKAGE BODY','PACKAGE',

'TYPE BODY','TYPE',

object_type

) || ' '

|| owner || '.' || '"' || object_name || '"'  || ' compile '

|| decode(object_type, 

'PACKAGE BODY','body;',

'TYPE BODY','body;',

';'

)

,'show error ' 

|| object_type || ' '

|| owner || '.' 

|| object_name 

from dba_objects

where object_type in ('FUNCTION','PACKAGE','PROCEDURE','VIEW','TRIGGER','PACKAGE BODY','TYPE','TYPE BODY','INDEXTYPE')

and status = 'INVALID'

--and owner not in ('SYS','SYSTEM')

order by owner,

decode(object_type,

'VIEW', 1,

'PACKAGE', 2,

'PACKAGE BODY', 3,

'PROCEDURE', 4,

'FUNCTION', 5,

'TRIGGER', 6,

9

)

/

===

SET PAGESIZE 9999
	clear columns
	clear breaks
	clear computes
	column owner format a25 heading 'Owner'
	column object_name format a30 heading 'Object Name'
	column object_type format a20 heading 'Object Type'
	column count format 999,999 heading 'Count'
	break on owner skip 2 on report
	compute sum label "Count: " of count on owner
	compute sum label "Grand Total: " of count on report
	SELECT
	owner
	, object_type
	, count(*) Count
	FROM dba_objects
	WHERE status <> 'VALID'
	GROUP BY owner, object_type
	/

=====

set pagesize 1000  LINESIZE 300

col COMP_ID format A9

col COMP_NAME format A40

col STATUS format A15

col VERSION format A12

col owner format a30

col object_name format a30

col object_type format a30

col COMMENTS for a60

col ACTION_TIME for a30

col VERSION for a25

--SPOOL DB_DD_CHECK.TXT

prompt /* Database identification */

select NAME, PLATFORM_ID, DATABASE_ROLE from v$database;

select * from V$version where banner like 'Oracle Database%';

select INSTANCE_NAME, to_char(STARTUP_TIME,'DD/MM/YYYY HH24:MI:SS') "STARTUP_TIME" from v$instance;

prompt  /* Database Component Registry status */

select comp_id, comp_name, status, version from dba_registry;

prompt  /* Level of patch in the database */

select * from dba_registry_history;

prompt /* INVALID objects count, by type -in detail */

select count(*) "INVALID Objects Count" from dba_objects where status !='VALID';

select owner, object_type, count(*) from dba_objects where status !='VALID' group by owner, object_type order by owner, object_type;

select owner, object_type, object_name, status from dba_objects where status !='VALID' order by owner, object_type, object_name;

====

spool more_info.txt
set echo on;
connect / as sysdba
@?/rdbms/admin/utlrp.sql
set pagesize 1000 linesize 200
col host_name format a30
col comp_name format a36
col version format a14
col status format a10
col action_time format a30
col action format a20
col comments format a45
col owner format a12
col object_name format a35
col object_type format a20
col name format a35
col table_name format a35
col column_name format a25
col index_name format a25
col grantee format a12
col grantor format a12
col privilege format a20
col schema_url format a60
col user format a12

col TEXT for a75
-- Instance and platform
select sysdate from dual;
select instance_name, host_name, version from v$instance;
select platform_name from v$database;
show con_name
-- Status of database components
select comp_name, version, status
from dba_registry
order by status, comp_name;
-- Registry history
select action_time, action, version, comments
from registry$history
order by 1 desc;
-- Invalid objects
select owner, object_name, object_type, status
from dba_objects
where status = 'INVALID'
and owner in ('SYS', 'XDB','MDSYS','ORDSYS','CTXSYS','PUBLIC','CTXSYS')
order by owner, object_name;
-- Object errors
select owner, name, type, text
from dba_errors
where owner in ('SYS', 'XDB','MDSYS','ORDSYS','PUBLIC','CTXSYS')
order by owner, name;
-- Duplicate objects
select owner, object_name, object_type, status
from dba_objects
where object_name in
( select object_name
from dba_objects
where owner = 'XDB' )
and owner not in ('SYS', 'XDB','MDSYS','ORDSYS','PUBLIC','CTXSYS');
-- Privileges
select owner, table_name, grantee, privilege
from dba_tab_privs
where table_name in
( 'DBMS_JOB', 'DBMS_LOB', 'DBMS_SQL',
' DBMS_STATS', 'DBMS_RANDOM', 'UTL_FILE',
'UTL_HTTP', 'UTL_RAW', 'UTL_TCP' )
and grantee = 'PUBLIC'
order by table_name;
select grantee, privilege, table_name, grantor
from dba_tab_privs
where grantor = 'SYS' and grantee = 'XDB'
order by table_name;
-- XMLType tables
select owner, storage_type, count(*) "TOTAL"
from dba_xml_tables
group by owner, storage_type
order by owner, storage_type;
select owner, table_name, storage_type
from dba_xml_tables
where storage_type in ('OBJECT-RELATIONAL', 'BINARY')
and owner != 'XDB'
order by storage_type, table_name;
-- XMLType columns
select owner, storage_type, count(*) "TOTAL"
from dba_xml_tab_cols
group by owner, storage_type
order by owner, storage_type;
select owner, table_name, column_name
from dba_xml_tab_cols
where storage_type = 'BINARY'
and owner not in ('MDSYS', 'ORDDATA', 'SYS', 'XDB')
order by owner, table_name;
-- XDB Indexes
select index_name, status, domidx_status, domidx_opstatus
from dba_indexes
where index_name in ('XDBHI_IDX', 'XDB$RESOURCE_OID_INDEX');
-- XML indexes
select index_owner, index_name, parameters, table_owner, table_name, type, index_type
from dba_xml_indexes;
-- Registered schemas
select owner, count(*) "TOTAL"
from dba_xml_schemas
group by owner;
select owner, schema_url
from dba_xml_schemas
order by 1,2;
-- Resources in the XML repository
select distinct (a.username) "USER", count (r.xmldata) "TOTAL"
from dba_users a, xdb.xdb$resource r
where sys_op_rawtonum (extractvalue (value(r),'/Resource/OwnerID/text()')) = a.USER_ID
group by a.username;
select any_path from resource_view;
-- Any XDB object outside XDB?
SELECT OBJECT_NAME,OBJECT_TYPE,OWNER FROM DBA_OBJECTS WHERE OBJECT_NAME in
(Select OBJECT_NAME from DBA_OBJECTS where owner='XDB') and owner not in ('XDB','PUBLIC');
-- Is Spatial being used?
select owner, index_name
from dba_indexes
where ityp_name = 'SPATIAL_INDEX';
select owner, table_name, column_name
from dba_tab_columns
where data_type= 'SDO_GEOMETRY'
and owner != 'MDSYS';
-- Is Multimedia being used?
set serveroutput on;
@?/ord/im/admin/imremchk.sql
spool off

==============================

export PATH=$PATH:$ORACLE_HOME/bin
cd $ORACLE_HOME/rdbms/admin
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -e -b utlrp -d $ORACLE_HOME/rdbms/admin utlrp.sql