user management queries

-





select count(*) from dba_tables where owner in
('APP_BPM_SIN'
,'APP_BPM_SIN_VEH'
,'APP_BPM_SIN_VEH_MIG'
,'APP_BPM_SIN_VEH_PROD'
,'APP_USRJOB');

Select 'GRANT SELECT ON '||owner ||'.'||TABLE_NAME||' TO DS_AWSBDRRVV_TEST;' From DBA_TABLES Where Owner IN ('APP_CRM_ED'
,'APP_DATMAN'
,'APP_DATMAN_DESA'
,'APP_RMAN'
,'APP_RRVV');

Select 'GRANT SELECT ON '||owner ||'.'||TABLE_NAME||' TO DS_AWSBDRRVV_TEST;' From DBA_TABLES Where Owner like 'APP%'
cpoy all the permissions in vi 1.sql
check the user creation done. if user exisit.
@1.sql

create user DS_AWSBDVULDW_TEST identified by DE#a4ZjJ8#Z;
grant connect to DE#a4ZjJ8#Z;
grant create se


col grantee format a30;
col granted_role format a30;
set lines 200 pages 3000
spool ibm_sif02.sql
select 'grant select,update,insert on '||owner||','||table_name||' to SIF_IBM02; 'from dba_tables where owner='APP_SIF' order by table_name;


select 'grant select on '||owner||'.'||table_name||' to srojas;' from dba_tables where owner='PPTONLINE';
create user srojas identified by DE#a4ZjJ8#Z default tablespace USERS temporary tablespace TEMP profile SECURITY;
grant connect to srojas;
grant select on PPTONLINE.PAYMENTS to srojas;
grant select on PPTONLINE.PAYMENTMODES to srojas;
grant select on PPTONLINE.INVOICES to srojas;
grant select on PPTONLINE.CASHIERAMOUNTS to srojas;
grant select on PPTONLINE.TICKETS to srojas;
grant select on PPTONLINE.TO_MEDIOS_PAGO_REPRO to srojas;
grant select on PPTONLINE.PASO to srojas;
grant select on PPTONLINE.IDOC_FALTANTES to srojas;
grant select on PPTONLINE.USERS to srojas;
grant select on PPTONLINE.TAXES to srojas;
grant select on PPTONLINE.SHEETSITEMS to srojas;
grant select on PPTONLINE.SHEETS to srojas;
grant select on PPTONLINE.POSDOCUMENTS to srojas;
grant select on PPTONLINE.NODES to srojas;
grant select on PPTONLINE.FOLIORANGE to srojas;
grant select on PPTONLINE.CATEGORIES to srojas;
grant select on PPTONLINE.CASHIERAMOUNTS_OLD to srojas;
grant select on PPTONLINE.FISCAL_PRINTER to srojas;
grant select on PPTONLINE.DISCOUNT_CATEGORY to srojas;
grant select on PPTONLINE.TOTALS to srojas;
grant select on PPTONLINE.SALE_CATEGORY to srojas;
grant select on PPTONLINE.TO_LIBRO_VENTAS to srojas;
grant select on PPTONLINE.TO_MEDIOS_PAGO to srojas;









95% of storage used … If you run out, you can't create, edit, and upload files. Get 30 GB of storage for ₹59.00 ₹0 for 1 month.
set pages 300 lines 300
col USERNAME for a30;
col ACCOUNT_STATUS for a30;
col DEFAULT_TABLESPACE for a30;
col TEMPORARY_TABLESPACE for a30;
col PROFILE for a25;
select username,account_status,default_tablespace,temporary_tablespace,profile from dba_users where username='GCB_SAP';






GCBADMIN


select username,account_status,default_tablespace,temporary_tablespace,profile from dba_users where username='GAL_SIF';




while assiging profile user account lock/ password expired make sure reset with same password and assign the profiles.


alter user GAL_SIF profile DBA_EXT;




select password from sys.user$ where name='GAL_SIF';




819EAF14CA32025B




alter user GAL_SIF identified by values 819EAF14CA32025B;






SET LINES 120
SET PAGES 120
COLUMN LIMIT FORMAT A30
COLUMN RESOURCE_NAME FORMAT A30
Column PROFILE format a20
SELECT * FROM DBA_PROFILES where PROFILE in ('APP_USER');




SQL> select password from sys.user$ where name='GCB_SAP';


PASSWORD
——————————
03BE7DDA8399B6A7


SQL>


Reset the password


alter user GCBADMIN identified by values '7808C1C4F399C276';




SET LINES 120
SET PAGES 120
SELECT * FROM DBA_PROFILES where PROFILE in (‘DEFAULT’) AND RESOURCE_NAME IN ('PASSWORD_LIFE_TIME') ORDER BY PROFILE ;




change to your desired value


SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 270;




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


set pages 300 lines 300
col USERNAME for a30;
col ACCOUNT_STATUS for a30;
col DEFAULT_TABLESPACE for a30;
col TEMPORARY_TABLESPACE for a30;
col PROFILE for a25;
select username,account_status,default_tablespace,temporary_tablespace,profile from dba_users where username in ('GCB_SAP','GCBADMIN');


alter user GCBADMIN identified by values '7808C1C4F399C276';


APP_USER




SET LINES 120
SET PAGES 120
COLUMN LIMIT FORMAT A30
COLUMN RESOURCE_NAME FORMAT A30
Column PROFILE format a20
SELECT * FROM DBA_PROFILES where PROFILE in ('APP_USER');




select password from sys.user$ where name='GCB_SAP';


set long 999999999
select dbms_metadata.get_ddl('USER','GCB_SAP') from dual;


CREATE USER "GCB_SAP" IDENTIFIED BY VALUES 'S:9106AA494C40EBEF51BAF15FB15AF4D
5D8ED2DCB393D954D81E9F41D75E2;T:A95D556B455BD0E8D6F9642B736F9A28DD4C1D8DE3415F7F
56A92FA6741C5A461EF06A84F954C0F4BB851F194DBED3814C940EBCE0354FB3AF2F2743A9890DB7
D23A01C58205B9D34337543D85BC9B33;BFA25C3E30C6CFDD'
DEFAULT TABLESPACE "TSD_GEN_M"
TEMPORARY TABLESPACE "TEMP"
PROFILE "APP_USER"
ACCOUNT LOCK


BFA25C3E30C6CFDD


alter user GCB_SAP identified by values 'BFA25C3E30C6CFDD';


conn GCBADMIN/gcBs4P_c;
Send to:








select password from sys.user$ where name='GCBADMIN';
7808C1C4F399C276
alter user GCBADMIN identified by values '7808C1C4F399C276';




User: gcb_sap password: gcBs4P_c
User: GCBADMIN password: Gc8P34ch3




User: gcb_sap password: gcBs4P_c
User: GCBADMIN password: Gc8P34ch3




alter profile APP_USER limit PASSWORD_REUSE_TIME 10;


alter user gcb_sap identified by gcBs4P_c;


alter user GCBADMIN identified by Gc8P34ch3;






alter profile APP_USER limit PASSWORD_REUSE_TIME 5;


select * from dba_profiles where profile='APP_USER' and RESOURCE_NAME in ('PASSWORD_REUSE_TIME') ;


select * from dba_sys_privs where grantee='APP_MACBHE';
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'APP_MACBHE';
col GRANTEE for a30
col OWNER for a30
col TABLE_NAME for a35
col GRANTOR for a30
col PRIVILEGE for a30
col TYPE for a30
select * from dba_tab_privs where grantee='APP_MACBHE';
select owner from ALL_TABLES where TABLE_NAME ='APP_MACBHE';


List the privilieges of the existing user (YCONTRERAS) :
select  privilege||' '||table_name  "Privileges for  User INSUDB " from  dba_tab_privs where  grantee='&data'
union
select  privilege from  dba_sys_privs where grantee='&data'
union
select granted_role from dba_role_privs where  grantee='&data';



SQL> select password from sys.user$ where name=’TVIJAY’;

PASSWORD
——————————
03BE7DDA8399B6A7

SQL>

Reset the password

alter user TVIJAY identified by values ’03BE7DDA8399B6A7′;

select password from sys.user$ where name='SCAMPOS';


4D7966652A92BD50

set long 999999999
select dbms_metadata.get_ddl('USER','SCAMPOS') from dual;


alter user INTERCP IDENTIFIED BY VALUES 4D7966652A92BD50';
select username,profile from dba_users where username='INTERCP';

alter profile default limit PASSWORD_REUSE_TIME unlimited;

 alter user INTERCP IDENTIFIED BY VALUES '4D7966652A92BD50';


select profile, resource_name,LIMIT from dba_profiles where resource_name='PASSWORD_LOCK_TIME' and profile='DEFAULT';

alter profile default limit PASSWORD_REUSE_TIME 5;




PASSWORD
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
FED8CF4594C127D0

SQL> set long 999999999
select dbms_metadata.get_ddl('USER','SCAMPOS') from dual;SQL>

DBMS_METADATA.GET_DDL('USER','SCAMPOS')
--------------------------------------------------------------------------------

   CREATE USER "SCAMPOS" IDENTIFIED BY VALUES 'S:C34ED31592CA4C8B7BB9DF2BD97446F
A2ADC9842359BA3E12BBF09D28880;T:9F7F953AE3705B62813DB2D214D0EFEBB49B1432663E4E0B
530806E9CBE0ED13178ABE525A23B10CFACCAC4297388434C3E32069BCF1D3102A6013D9EBB8027C
93C4E6F09E6715D629B22E0DC81B3DC5;FED8CF4594C127D0'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"
      PASSWORD EXPIRE



alter user INTERCP IDENTIFIED BY VALUES 'FED8CF4594C127D0';
=====================================================================

create the profile..

CREATE PROFILE "TEST_PROF" LIMIT
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
CONNECT_TIME UNLIMITED
IDLE_TIME UNLIMITED
SESSIONS_PER_USER UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
PRIVATE_SGA UNLIMITED
COMPOSITE_LIMIT UNLIMITED
PASSWORD_LIFE_TIME 730
PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_LOCK_TIME 1
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_VERIFY_FUNCTION NULL;

alter USER AH064279 proFile TEST_PROF;
alter user  AH064279 identified by  Alavhz79;
ALTER USER AH064279 PROFILE APP_USER;
drop profile TEST_PROF;

set pages 300 lines 300
col USERNAME for a30;
col ACCOUNT_STATUS for a30;
col DEFAULT_TABLESPACE for a30;
col TEMPORARY_TABLESPACE for a30;
col PROFILE for a25;
select username,account_status,default_tablespace,temporary_tablespace,profile from dba_users where username='AH064279';



95% of storage used … If you run out, you can't create, edit, and upload files. Get 30 GB of storage for ₹59.00 ₹0 for 1 month.
alter session set current_schema=MACBHE;

CREATE TABLE MACBHE.ONLINE_TEMP_REGALOS (
	TIENDA NUMBER(4) NOT NULL,
	TERMINAL NUMBER(3) NOT NULL,
	TRANSACCION NUMBER(4) NOT NULL,
	FECHA DATE NOT NULL,
	IMPORTE_COMPRA NUMBER(11),
	TELEFONO NUMBER(10),
	INTENTOS NUMBER(4) NOT NULL,
	FLAG NUMBER(1));

grant insert on MACBHE.ONLINE_TEMP_REGALOS TO APP_MONEDAS;



col username format a30
col account_status format a30
select username, account_status from dba_users where username ='APP_MONEDAS';



SELECT created FROM dba_objects  WHERE object_name = ONLINE_TEMP_REGALOS  AND owner = MACBHE    AND object_type = 'TABLE';



set lines 190 pages 3000
col username format a30
col host_name format a30
col instance_name format...for a30
col instance_name format a30



col grantee format a30;
col granted_role format a30;

set lines 200 pages 3000
spool ibm_sif02.sql
select 'grant select,update,insert on '||owner||','||table_name||' to SIF_IBM02; 'from dba_tables where owner='APP_SIF' order by table_name;

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

create user mcajusol identified by DE#a4ZjJ8#Z default tablespace PSDEFAULT temporary tablespace PSTEMP profile DEFAULT;
grant connect to DS_FACELE_WEB;

sjimenez
vcabrales
saguilar
mrojasg



alter profile APP_USER limit PASSWORD_REUSE_TIME unlimited ;
alter profile APP_USER limit PASSWORD_REUSE_MAX unlimited;

alter profile APP_USER limit PASSWORD_REUSE_TIME 5;
alter profile APP_USER limit PASSWORD_REUSE_MAX DEFAULT;
C05700E3F2C862F1
alter user GCBADMIN identified by values 'C05700E3F2C862F1';


select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||';' from dba_tab_privs where owner='BDD_BTWEB' and table_name in ('FSH012','FSH013','FSH014','FSH015','FSH016','FSH031','FSD011','FSD014','FSD010','FSD016','FSD015','FSD601','FSD602','FSD024','FSR011','FSD611','FSR601','FSR022','FSD612',
'X054021','X054023','FPP033','FPP251','FPP044','FPP044A','FST034','FST035','FST036','FST037','FST038','FST039','FST040','FPP026','FPP028','FSR014','FSH005','FSD012','FSE113') order by table_name;


  SQL> select GRANTEE,GRANTED_ROLE,DEFAULT_ROLE from dba_role_privs where GRANTEE='KQQL147';
  GRANTEE                        GRANTED_ROLE                   DEF
  ------------------------------ ------------------------------ ---
  KQQL147                        AZ_CONNECT                     YES
 
  SQL> select role,privilege,admin_option from role_sys_privs where role='AZ_CONNECT';

  ROLE                           PRIVILEGE                                ADM
  ------------------------------ ---------------------------------------- ---
  AZ_CONNECT                     CREATE SESSION                           NO
  AZ_CONNECT                     CREATE SYNONYM                           NO

  AFTER:
  ******
  SQL>GRANT EHDA_READ TO KQQL147
  SQL>GRANT ESS064_READ TO KQQL147

  SQL> select GRANTEE,GRANTED_ROLE,DEFAULT_ROLE from dba_role_privs where GRANTEE='KQQL147';

  GRANTEE                        GRANTED_ROLE                   DEF
  ------------------------------ ------------------------------ ---
  KQQL147                        AZ_CONNECT                     YES
  KQQL147                        EHDA_READ                      YES
  KQQL147                        ESS064_READ                    YES
	select role,privilege,admin_option from role_sys_privs where role='AZ_CONNECT';
	select role,privilege,admin_option from role_sys_privs where role='AZ_RESOURCE';
	select role,privilege,admin_option from role_sys_privs where role='AQ_ADMINISTRATOR_ROLE';

	select owner,object_name,object_type from dba_objects where object_type='TABLE' and owner='SWAPRO' OR OWNER='RUD163_REPORT';


	SQL>spool abc.sql;
	SQL>Select 'grant select on RUD163_REPORT.'||table_name||' to KWBH960;' from dba_tables where owner='RUD163_REPORT';	
	SQL>spool off;
	SQL>@abc.sql

	SQL>spool abcd.sql
	SQL>Select 'grant select on SWAPRO.'||table_name||' to KWBH960;' from dba_tables where owner='SWAPRO';
select OBJECT_TYPE,STATUS,owner,object_name from dba_objects where STATUS='INVALID';


SELECT 'DROP '||OBJECT_TYPE||' '||OWNER||'."'||OBJECT_NAME||'";' from dba_objects where owner='MSPPSTG' and object_type in('JAVA RESOURCE','JAVA CLASS');
SELECT 'DROP '||OBJECT_TYPE||' '||OWNER||'.'||OBJECT_NAME||' CASCADE constraints PURGE;' from dba_objects where owner='SIEBEL' and object_type='TABLE';
	137 rows selected.
	SQL> select OWNER,TABLE_NAME,GRANTEE,PRIVILEGE from dba_tab_privs where table_name='RUD163_AT';




32)DATAPUMP-Schema level EXP/IMP:
##################################

SQL> select name from v$database;

NAME
---------
P41X


SQL> select sum(bytes)/1024/1024/1024 from dba_segments where owner='MSPRODLIVE';

SUM(BYTES)/1024/1024/1024
-------------------------
               41.7158203

SQL> !df -h
Filesystem             size   used  avail capacity  Mounted on
/dev/md/dsk/d100        51G    24G    26G    49%    /
/proc                    0K     0K     0K     0%    /proc
mnttab                   0K     0K     0K     0%    /etc/mnttab
fd                       0K     0K     0K     0%    /dev/fd
swap                    17G   120K    17G     1%    /var/run
swap                   512M    48K   512M     1%    /tmp
/dev/md/dsk/d130       486M    10M   427M     3%    /global/.devices/node@2
/dev/md/dsk/d150       486M    10M   427M     3%    /global/.devices/node@1
arch                    49G    12G    37G    25%    /arch
oradata                 79G    75G   4.5G    95%    /oradata
oradatb                149G    46G   103G    31%    /oradatb
oralog                  49G    22G    27G    46%    /oralog



SQL> select OBJECT_TYPE,STATUS,count(*) from dba_objects where owner='MSPPLIVE' GROUP BY OBJECT_TYPE,STATUS;

OBJECT_TYPE         STATUS    COUNT(*)
------------------- ------- ----------
TYPE                VALID           28
PROCEDURE           VALID            4
PACKAGE BODY        VALID            5
FUNCTION            VALID            1
PACKAGE             VALID            7
INDEX               VALID          311
SEQUENCE            VALID           40
TRIGGER             VALID            5
JAVA RESOURCE       VALID           18
TABLE               VALID          202
JAVA CLASS          VALID          635

OBJECT_TYPE         STATUS    COUNT(*)
------------------- ------- ----------
PROCEDURE           INVALID          3
LOB                 VALID           35
PACKAGE BODY        INVALID          2
DATABASE LINK       VALID            2

15 rows selected.



SQL> select count(*) from dba_users;

  COUNT(*)
----------
        24

SQL>select * from dba_directories;
 

OWNER DIRECTORY_NAME                                DIRECTORY_PATH
----- --------------------------------------------- -----------------------------------------------------------------
SYS   MSPRODLIVETMPDIR                              D:\Program Files\Mediasurface\5.5.0.63\Server\oratmp
SYS   DATA_PUMP_DIR                                 /app/oracle/product/10.2/rdbms/log/
SYS   ORACLE_OCM_CONFIG_DIR                         /app/oracle/product/10.2/ccr/hosts/sesoxextclu15s1/state


SQL> CREATE  DIRECTORY EXP_DIR AS '/u05/oradata/BBPDFL/expdp';

Directory created.

SQL> GRANT read, write ON DIRECTORY EXP_DIR to system;

Grant succeeded.

SQL>select * from dba_directories;
 

OWNER DIRECTORY_NAME                                DIRECTORY_PATH
----- --------------------------------------------- -----------------------------------------------------------------
SYS   MSPRODLIVETMPDIR                              D:\Program Files\Mediasurface\5.5.0.63\Server\oratmp
SYS   DATA_PUMP_DIR                                 /app/oracle/product/10.2/rdbms/log/
SYS   ORACLE_OCM_CONFIG_DIR                         /app/oracle/product/10.2/ccr/hosts/sesoxextclu15s1/state
SYS   EXP_DMP                                       /oradatb/export


nohup expdp system/blank15u directory=EXP_DIR dumpfile=SIEBEL_P57x.dmp logfile=SIEBEL_P57x.log parallel=4 SCHEMAS=SIEBEL JOB_NAME=SIEBEL_JOB

Multiple schemas export using Datapump: --

expdp / DIRECTORY=EXP_DIR DUMPFILE=LTKTEST_schemas.dmp logfile=LTKTEST_schemas.log parallel=3 SCHEMAS=LTKTAX,LTKCONTENT,LTKLINKS


To Lowyer version : ---

expdp / as sysdba directory=EXP_DIR dumpfile=USRREPOS.dmp logfile=USRREPOS.log parallel=4 SCHEMAS=USRREPOS JOB_NAME=USRREPOS_JOB VERSION=10.2.0.1.0

multiple Tables export using datapump: --

expdp LAWTEL/******** directory=EXP_DIR dumpfile=Tablesprod_3107.dmp logfile=Tablesprod_310
7.log parallel=3 tables=lawtel.Accounts,lawtel.Accountservices,lawtel.Accountserviceusers,lawtel.Users,lawtel.EUtickerdata,la
wtel.tickers JOB_NAME=LAWTEL_JOB version=10.2.0.1



SQL>select OWNER_NAME,JOB_NAME,STATE,ATTACHED_SESSIONS from dba_datapump_jobs;


OWNER_NAME JOB_NAME                   		    STATE                          ATTACHED_SESSIONS DATAPUMP_SESSIONS
---------- ------------------------------ ------------------------------ ----------------- -----------------
ORA102     MSPRODLIVE_P41X                        EXECUTING                                      1                 6



nohup scp MSPRODLIVE_P41X.dmp sesoxextclu13s1:/oradatb/import/

nohup scp sesoxextclu15s1:/oradatb/export/MSPRODLIVE_P41X.dmp /global/syslog/pist .



SQL> select name from v$database;

NAME
---------
V46X

SQL> select count(*) from dba_users;

  COUNT(*)
----------
        25
SQL> select sum(bytes)/1024/1024/1024 from  dba_segments where owner='MSPPSTG';

SUM(BYTES)/1024/1024/1024
-------------------------
               28.2384033
SQL>select OBJECT_TYPE,STATUS,count(*) from dba_objects where owner='MSPPSTG' GROUP BY OBJECT_TYPE,STATUS;

select OBJECT_TYPE,STATUS,owner,object_name from dba_objects where STATUS='INVALID';


SELECT 'DROP '||OBJECT_TYPE||' '||OWNER||'."'||OBJECT_NAME||'";' from dba_objects where owner='MSPPSTG' and object_type in('JAVA RESOURCE','JAVA CLASS');
SELECT 'DROP '||OBJECT_TYPE||' '||OWNER||'.'||OBJECT_NAME||' CASCADE constraints PURGE;' from dba_objects where owner='SIEBEL' and object_type='TABLE';


SQL> select tablespace_name,sum(bytes)/1024/1024/1024 from dba_segments where owner='MSPRODSTG' group by tablespace_name;

TABLESPACE_NAME                SUM(BYTES)/1024/1024/1024
------------------------------ -------------------------
MSBINARY                                      17.7685547
MSAUDIO                                        .70526123
MSIMAGE                                       2.20605469
MSFIELDS                                      1.07098389
MSXML                                             .15625
MSPRODTS ledhu                                     .000244141
MSVIDEO                                       .243103027
SYSAUX                                        30.0360718
MSLIVE                                        .118530273
MSDOC                                         .298950195
MSCORE                                        3.95513916

TABLESPACE_NAME                SUM(BYTES)/1024/1024/1024
------------------------------ -------------------------
MSTASK                                        4.10302734
MSINDEXES                                     2.15734863

13 rows selected.


nohup impdp system/blank14u@v53x  directory=EXP_DMP dumpfile=MSPRODSTG_P40X.dmp logfile=MSPRODSTG2MSPPSTG_P40XtoV53X.log REMAP_SCHEMA=MSPRODSTG:MSPPSTG REMAP_TABLESPACE=MSPRODTS:MSPREPRODTS


impdp lawtel/lawtel  directory=EXP_DIR dumpfile=UserIPaddress_prod.dmp logfile=UserIPaddress_prod.log REMAP_SCHEMA=LAWTEL:LAWTEL tables=USERIPADDRESS

impdp lawtel/lawtel  directory=EXP_DIR dumpfile=Tablesprod_3107.dmp logfile=Tablesprod_3107.log REMAP_SCHEMA=LAWTEL:LAWTEL tables=Accounts,Accountservices,Accountserviceusers,Users,EUtickerdata,tickers JOB_NAME=LAWTEL_DEV_JOB


Importing of  multiple schemas using impdp:-
--------------------------------------------
impdp / DIRECTORY=EXP_DIR DUMPFILE=LTKLIVE_schemas.dmp logfile=LTKTEST_IMP_schemas.log parallel=3 SCHEMAS=LTKTAX,LTKCONTENT,LTKLINKS

SQL>select OWNER_NAME,JOB_NAME,STATE,ATTACHED_SESSIONS,DATAPUMP_SESSIONS from dba_datapump_jobs;



Table level refresh-EXPDP:
##########################



expdp LAWTEL/******** directory=EXP_DIR dumpfile=Tablesprod_3107.dmp logfile=Tablesprod_3107.log parallel=3 
tables=lawtel.Accounts,lawtel.Accountservices,lawtel.Accountserviceusers,lawtel.Users,lawtel.EUtickerdata,lawtel.tickers JOB_NAME=LAWTEL_JOB version=10.2.0.1

exp / file=/data/oracle/backups/Lawtel_tables_dev.dmp log=/data/oracle/backups/lawtel_tables_dev.log direct=y  tables=lawtel.Accounts,lawtel.Accountservices,lawtel.Accountserviceusers,lawtel.Users,lawtel.EUtickerdata,lawtel.tickers statistics=none


drop table lawtel.Accounts;
drop table lawtel.Accountservices;
drop table lawtel.Accountserviceusers;
drop table lawtel.EUtickerdata;
drop table lawtel.tickers;


drop table lawtel.Users casecade;

impdp lawtel/lawtel  directory=EXP_DIR dumpfile=Tablesprod_3107.dmp logfile=Tablesprod_3107.log REMAP_SCHEMA=LAWTEL:LAWTEL tables=Accounts,Accountservices,Accountserviceusers,Users,EUtickerdata,tickers JOB_NAME=LAWTEL_DEV_JOB


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

oracle@uksc-ltf-dbt:/data/oracle/backups> more Tablesprod_3107.log
;;;
Import: Release 10.2.0.1.0 - 64bit Production on Tuesday, 31 July, 2012 15:45:04

Copyright (c) 2003, 2005, Oracle.  All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Master table "LAWTEL"."LAWTEL_DEV_JOB" successfully loaded/unloaded
Starting "LAWTEL"."LAWTEL_DEV_JOB":  lawtel/******** directory=EXP_DIR dumpfile=Tablesprod_3107.dmp logfile=Tablesprod_3107.l
og REMAP_SCHEMA=LAWTEL:LAWTEL tables=Accounts,Accountservices,Accountserviceusers,Users,EUtickerdata,tickers JOB_NAME=LAWTEL_
DEV_JOB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "LAWTEL"."ACCOUNTS"                         1.318 MB    7699 rows
. . imported "LAWTEL"."TICKERS"                          18.83 KB      41 rows
. . imported "LAWTEL"."USERS"                            21.19 MB  187446 rows
. . imported "LAWTEL"."ACCOUNTSERVICES"                  783.6 KB   17124 rows
. . imported "LAWTEL"."ACCOUNTSERVICEUSERS"              3.739 MB  292858 rows
. . imported "LAWTEL"."EUTICKERDATA"                     8.203 KB      14 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "LAWTEL"."LAWTEL_DEV_JOB" successfully completed at 15:45:37

__________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________

33) CREATE ROLE--GRANT SELECT PRIVS to that ROLE:
##################################################

SQL> select GRANTEE,GRANTED_ROLE,DEFAULT_ROLE from dba_role_privs where GRANTEE='KFQC418';

GRANTEE    GRANTED_ROLE                   DEF
---------- ------------------------------ ---
KFQC418    AZ_CONNECT                     YES


SQL>create role SSE_READ;

created.


SQL>Select 'grant select on SIEBEL.'||table_name||' to SSE_READ;' from dba_tables where owner='SIEBEL';

SQL>spool sse_read.sql
SQl>/
SQL>spool off;
SQL>@sse_read.sql

SQL>select GRANTEE,OWNER,PRIVILEGE from dba_tab_privs where GRANTEE='SSE_READ';

SQL>GRANT SSE_READ TO KFQC418;


SQL> select GRANTEE,GRANTED_ROLE,DEFAULT_ROLE from dba_role_privs where GRANTEE='KFQC418';

GRANTEE    GRANTED_ROLE                   DEF
---------- ------------------------------ ---
KFQC418    AZ_CONNECT                     YES
KFQC418    SSE_READ                       YES
__________________________________________________________________________________________________________________________________________________________________________


select owner,segment_name,sum(bytes)/1024/1024/1024,tablespace_name from dba_Segments
where segment_name='BSNL_PAYMENTS_EXTRACT' and owner='GENEVA_ADMIN' group by owner,segment_name,tablespace_name;
OWNER                          SEGMENT_NAME                                                                      SUM(BYTES)/1024/1024/1024 TABLESPACE_NAME
------------------------------ --------------------------------------------------------------------------------- ------------------------- ------------------------------
GENEVA_ADMIN                   BSNL_PAYMENTS_EXTRACT                                                                            111.117188 BSNL_CUSTOM_TAB2_TS


select count(*) from GENEVA_ADMIN.BSNL_PAYMENTS_EXTRACT ;

select index_name ,status from dba_indexes where table_name='BSNL_PAYMENTS_EXTRACT' and owner='GENEVA_ADMIN';

INDEX_NAME                     STATUS
------------------------------ --------
IND4_BSNL_PAYMENTS_EXT         VALID
IND_LOC2_PAYMENTS_EXTRACT      VALID
IND_LOC1_PAYMENTS_EXTRACT      VALID
IND_LOC3_PAYMENTS_EXTRACT      VALID


select TABLE_OWNER,TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME from DBA_TAB_PARTITIONS where TABLE_NAME like 'COSTEDEVENT_PK%'


select segment_name ,owner,sum(bytes/1024/1024/1024) from dba_segments where owner in ('GENEVA_ADMIN') 
and segment_name in ('BSNL_PAYMENTS_EXTRACT') group by segment_name,owner 2  ;


Find metadata
*********************
set heading off;
set echo off;
Set pages 999;
set long 90000;

spool ddl_list.sql
select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual; 
select dbms_metadata.get_ddl('INDEX','DEPT_IDX','SCOTT') from dual;
spool off;


SQL>  select dbms_metadata.get_ddl('INDEX','SYS_IL0000009919C00005$$','ORABPEL') from dual;



























































Comments

Post a Comment

Popular posts from this blog

MY NOTEPAD

-
set lines 200 pages 200; col END_TIME for a20; col ELAPSED_TIME for a25; col START_TIME for a20 SELECT T.SESSION_KEY, T.INPUT_TYPE, T.STATUS, T.SUM_BACKUP_PIECES_IN_GB, T.START_TIME, T.END_TIME, T.SECONDS ELAPSED_TIME FROM ( SELECT SESSION_KEY, INPUT_TYPE, STATUS, OUTPUT_BYTES/1024/1024/1024 SUM_BACKUP_PIECES_IN_GB, TO_CHAR (START_TIME, 'dd/mm/yyyy hh24:mi') START_TIME, TO_CHAR (END_TIME, 'dd/mm/yyyy hh24:mi') END_TIME, TIME_TAKEN_DISPLAY SECONDS FROM V$RMAN_BACKUP_JOB_DETAILS WHERE INPUT_TYPE='ARCHIVELOG' ORDER BY SESSION_KEY DESC) T WHERE ROWNUM <= 1; ================ ASM disk group free space ============== set sqlblanklines on set lines 180 set pages 999le col "Group Name" form a25 col "Disk Name" form a30 col "State" form a15 ...
Read more

Oracle OEM Holistic Patch

-
  Oracle OEM Holistic Patch  The Concept Starting with Oracle Enterprise Manager (OEM) 13.5 RU24 (October 2024), Oracle introduced the Holistic Patch for OEM — a unified patching model that consolidates several separate updates into a single coordinated package. Previously, administrators had to patch: * The OMS and Agents with the quarterly Release Update (RU). * OEM Application via the System Patch Bundle (SPB). * WebLogic CPU and JDK manually. There was an element of complication because of the need to keep the solution homogeneous. With the Holistic Patch, the last two steps are merged into a single patch bundle. Now we have a new Holistic patch for OEM that is released with each new OMS Release Update. Prerequisites Apply RU 24 Patch  PATCH 36761595  /later on all OMSes before applying Oct Holistic Patch   PATCH 37305292             (OR) Apply RU 25 Patch  PATCH 37057635  /later on all OMSes before applying ...
Read more

Upgrade Oracle 19c DB to 21c using Auto Upgrade utility inprogress

-
Image
  Upgrade Oracle 11g to 21c using AutoUpgrade utility: How do I upgrade Oracle database to 21c?  install the 21c oracle binaries into the server . Oracle Virtual Bix  Oracle Linux 7 Oracle Database 19c currently running Upgrading 19c database to 21c directly Oracle Database 19c on premises is available – and one of the key features is the new Auto Upgrade utility. In the following days I will publish several blog posts explaining and showcasing the new Auto Upgrade. What is the Auto Upgrade? The Oracle Database Auto Upgrade utility is a new tiny little command line tool which allows you to upgrade your databases in an unattended way. I call it the Hands-Free Upgrade. The new Auto Upgrade utility in Oracle 19che idea of the tool is to run the prechecks against multiple databases, fix 99% of the potential issues, set a restore point in case something goes wrong – and then upgrade your databases. And of course, do the post upgrade, recompilation and time zone adjustment. The...
Read more