Useful commands Part 3

To check with the primary key
-------------------------------
select *from user_constraints where table_name='TABLE_NAME' and constrainT_type in ('P','U');
select *from user_constraints where table_name='tblequipment' and constrainT_type in ('P','U');


SELECT *FROM USER_CONS_COLUMNS WHERE CONSTRAINT_NAME='PK_EQ';
=======================================================================


Table names
------------------------
SQL> select table_name from user_tables;

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

list out user names
----------------------------
SQL> select username from dba_users;

=======================================================================
Tablespace Location
-----------------------------

select tablespace_name,file_name from dba_data_files;

=======================================================================
For Audit_Parameter

SQL> show parameter audit;
=======================================================================
AUDIT VIEWS

SQL> SELECT view_name
FROM dba_views
WHERE view_name LIKE 'DBA%AUDIT%'
ORDER BY view_name;
VIEW_NAME
------------------------------
DBA_AUDIT_EXISTS
DBA_AUDIT_OBJECT
DBA_AUDIT_POLICIES
DBA_AUDIT_POLICY_COLUMNS
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
DBA_AUDIT_TRAIL
DBA_COMMON_AUDIT_TRAIL
DBA_FGA_AUDIT_TRAIL
DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS

VIEW_NAME
------------------------------
DBA_REPAUDIT_ATTRIBUTE
DBA_REPAUDIT_COLUMN
DBA_STMT_AUDIT_OPTS

14 rows selected.

=========================================================================
SQL> SELECT username, logoff_time, logoff_lread, logoff_pread,
logoff_lwrite, logoff_dlock
FROM sys.dba_audit_session;

=========================================================================
CHECK BACKGROUND USERS
--------------------------

SQL> select osuser,username,status,type from gv$session
where type != 'BACKGROUND';

SQL> set pages 999
SQL> set lines 132
SQL> /
=========================================================================
COLUMN username FORMAT A10
--COLUMN logoff_time FORMAT A10
--COLUMN extended_timestamp FORMAT A35
COLUMN logoff_lread FORMAT 999,999,990
COLUMN logoff_pread FORMAT 999,999,990
COLUMN logoff_lwrite FORMAT 999,999,990
COLUMN logoff_dlock FORMAT A10
break on report
compute sum of logoff_lread on report
compute sum of logoff_pread on report
compute sum of logoff_lwrite on report

SELECT username, logoff_time, logoff_lread, logoff_pread,
logoff_lwrite, logoff_dlock
FROM sys.dba_audit_session WHERE USERNAME='MAN_PROD';

clear columns //to clear formatting
========================================================================
SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
24/12/2008 16:14:50

SQL> select trunc(sysdate) from dual;

TRUNC(SYSDATE)
-------------------
24/12/2008 00:00:00
commit;

SELECT sysdate,trunc(trunc(sysdate,'mm')-1,'mm'),trunc(sysdate,'mm') from dual;
=========================================================================
SQL> select ntimestamp# from sys.aud$ where rownum<5;> select timestamp# from sys.aud$ where rownum<5;> select count(*) from sys.aud$ where ntimestamp# select * FROM dba_profiles WHERE PROFILE='DEFAULT' and resource_name='PASSWORD_VERIFY_FUNCTION';

PROFILE RESOURCE_NAME RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD
VERIFY_FUNCTION


SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION NULL;

Profile altered.
=========================================================================
select obj_name,status from dba_objects where object_name='VERIFY_FUNCTION';

SQL> DROP FUNCTION AUDIT_USER.VERIFY_FUNCTION;

Function dropped.
=========================================================================
SQL> create user suman identified by sumanred9 profile audit_profile;

User created.
=========================================================================
C:\>pscp -P 261 c:\rpms.zip spulicherla@advansrac1.advanstec.com:rpms.zip
spulicherla@advansrac1.advanstec.com's password:
Access denied
spulicherla@advansrac1.advanstec.com's password:
rpms.zip | 78751 kB | 504.8 kB/s | ETA: 00:00:00 | 100%


ssh -R 6075:localhost:6000 -p 260 -l spulicherla advansrac1.advanstec.com


==========================================================================
SQL> conn sys/erptrn1@erptrn1 as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> conn sys/erptrn1@erptrn11 as sysdba
Connected.
SQL> show user
USER is "SYS"

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

ssh -R 6075:localhost:6000 -p 261 -l spulicherla advansrac1.advanstec.com
yes

password: winter3

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

SQL> SELECT NAME,TYPE,TOTAL_MB,FREE_MB FROM V$ASM_DISKGROUP;

NAME TYPE TOTAL_MB FREE_MB
------------------------------ ------ ---------- ----------
DATA EXTERN 3000 2946

SQL> drop diskgroup data including contents;

Diskgroup dropped.

SQL> show parameter asm

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_asm_allow_only_raw_disks boolean FALSE
asm_diskgroups string
asm_diskstring string /oradata/nfsdg/*
asm_power_limit integer 11\\corp
asm_preferred_read_failure_groups string

SQL> create diskgroup data external redundancy disk
2 '/oradata/nfsdg/disk1',
3 '/oradata/nfsdg/disk2';


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

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jul 9 15:11:01 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> exit
Disconnected
[oracle ~]$ export ORACLE_HOME=$ASM_HOME
[oracle ~]$ export ORACLE_SID=+ASM1
[oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jul 9 15:11:30 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options