Put a line No in taxt file

awk '{print NR $0}' anuj.txt >anujline.txt

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