/bin/rm: Argument list too long

$ rm test_*.aud
-bash: /usr/bin/rm: Argument list too long



for i in /u01/app/oracle/admin/test/adump/test_*.aud            -->>This is the path of the file where he is located.
do
echo rm $i >> /u01/app/oracle/admin/test/adump/test.log -->>This is the parameter which generate the log file which we can check which file is going to be deleted.
rm $i    -->>It will delete the files to read $i where in "i" we have inserted the file with first command.
done

Thanks

DB Tech Solution

ORA-14460: Only one COMPRESS or NOCOMPRESS clause may be specified

ORA-14460: only one COMPRESSor NOCOMPRESS clause may be specified


Just use "transform=segment_attributes:n" parameter in import

impdp user/pass@ directory=dump_loc dumpfile=users_tb.dmp logfile=users_tb.log tables=dept -->>Error

ORA-14460: only one COMPRESSor NOCOMPRESS clause may be specified.........

Two solutions are there -

Solution -1
impdp user/pass@ directory=dump_loc dumpfile=users_tb.dmp logfile=users_tb.log tables=dept transform=segment_attributes:n


Solution -2
Just create a table prior to import, Get the script from above dump with use parameter "sqlfile" or the db where we have exported that table then

impdp user/pass@ directory=dump_loc dumpfile=users_tb.dmp logfile=users_tb.log tables=dept table_exists_action=append



Thanks
DB Tech Solutions

How to find default Permanent Tablespace

Query to Find Default Permanent Tablespace seperately -

SELECT PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';


Query to Find Default Temporary Tablespace seperately -

SELECT PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

Query to Find Both Default Tablespaces (Permanent and Temporary)

SELECT PROPERTY_NAME, PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME IN ('DEFAULT_PERMANENT_TABLESPACE','DEFAULT_TEMP_TABLESPACE');


Thanks

set sqlplus prompt name same as instance name

How to set sqlplus prompt name same as Database/Instance name


We need to change one file called glogin.sql, steps are as below...

Path where glogin.sql 

cd $ORACLE_HOME/sqlplus/admin

/u01/app/oracle/product/12.1.0/dbhome_1/sqlplus/admin

set below mention line at the end of the file

vi glogin.sql

SET SQLPROMPT 'PROD-SQL>'

save and exit

Now next login you can see your db name like PROD

Thanks

-bash: /usr/bin/rm: Argument list too long

To delete a large number of files we have to use loop in shell script like below-

for i in /u01/app/files/*.txt
do
echo rm $i >> /u01/app/files/output.log -->>this will generate the logfile which we can check which files is going to be deleted
rm $i
done

Thanks

ORA-00059: maximum number of DB_FILES exceeded

How to increase database files in Oracle Database -

SQL> show parameter db_files;

NAME      TYPE       VALUE
------------------------------------ -------------------------------- ------------------------------
db_files      integer       200 (Default Value)


SQL> alter system set db_files=300 scope=spfile;

SQL> shu immediate;

SQL> startup

SQL> show parameter db_files;

NAME      TYPE       VALUE
------------------------------------ -------------------------------- ------------------------------
db_files      integer       300



Thanks

How reclaim the space after a large delete in oracle

10 Steps to reclaim the space in oracle -

1. CREATE TABLE USER.USER_TB AS SELECT * FROM ...


2. Now check the size of that created table -

SQL> SELECT SUM(BYTES) FROM DBA_SEGMENTS WHERE OWNER='USER' AND SEGMENT_NAME='USER_TB';

SUM(BYTES)
----------
  13631488


3. Now check the tablespace size which using that segment -

Tablespace Size (MB)  Free (MB) % Free     % Used
------------------------------ ---------- ---------- ---------- ----------
USER_TABLESPACE        14 .875       6 94


4. Now delete the records from the table -

SQL> delete from USER.USER_tb;

91108 rows deleted.


5. Again check the size of that created table on which we have deleted the records -
Note - As we can see there is not space released after to delete the records..

SQL> SELECT SUM(BYTES) FROM DBA_SEGMENTS WHERE OWNER='USER' AND SEGMENT_NAME='USER_TB';

SUM(BYTES)
----------
  13631488

6. Again check the tablespace size which using that segment -
Note - As we can see there is not space released after to delete the records..

Tablespace Size (MB)  Free (MB) % Free     % Used
------------------------------ ---------- ---------- ---------- ----------
USER_TABLESPACE        14 .875       6 94


7. Now to shrink the table but before to that we need to enable row movement else it will throws an error -

SQL> ALTER TABLE USER.USER_TB ENABLE ROW MOVEMENT;

Table altered.

8. Now space reclaim -

SQL> ALTER TABLE USER.USER_TB SHRINK SPACE CASCADE;

Table altered.

9. Now check the size of that created table on which we have deleted the records -
Note - It is showing size has reclaimed..

SQL> SELECT SUM(BYTES) FROM DBA_SEGMENTS WHERE OWNER='USER' AND SEGMENT_NAME='USER_TB';

SUM(BYTES)
----------
     65536

10. Now check the tablespace size which using that segment -
Note - It is showing size has reclaimned..

Tablespace Size (MB)  Free (MB) % Free     % Used
------------------------------ ---------- ---------- ---------- ----------
USER_TABLESPACE        14    13.8125      99 1

Thanks

DB Tech Solutions