![]() The date example above could be modified as follows: LEAST(date1, date2) Helpful Tip #2: One of our viewers suggested using the LEAST function (instead of the DECODE function) as follows: Sales Bonuses DECODE(SIGN(actual-target), -1, 'NO Bonus for you', 0,'Just made it', 1, 'Congrats, you are a winner') The SIGN/DECODE combination is also helpful for numeric comparisons e.g. The date example above could be modified as follows: DECODE(SIGN(date1-date2), 1, date2, date1) Helpful Tip #1: One of our viewers suggested combining the SIGN function with the DECODE function as follows: The formula below would equal 0, if date1 is greater than date2: (date1 - date2) - ABS(date1 - date2) Otherwise, the DECODE function should return date1.Īnswer: To accomplish this, use the DECODE function as follows: DECODE((date1 - date2) - ABS(date1 - date2), 0, date2, date1) DICT combines tables and synonyms and doesn't tell you who owns the object.Question: One of our viewers wanted to know how to use the DECODE function to compare two dates (ie: date1 and date2), where if date1 > date2, the DECODE function should return date2. ![]() CAT also shows information about materialized view logs with a TABLE_TYPE of "TABLE" which is unlikely to be what you really want. For example, the TAB and CAT views both show information about tables that are in the user's recycle bin while the _TABLES views all filter those out. Oracle has not changed these views in a long time so they often have problems with newer types of objects. ![]() In general, I would not suggest using these legacy views unless you absolutely need to backport your scripts to Oracle 6. Oracle also has a number of legacy data dictionary views- TAB, DICT, TABS, and CAT for example- that could be used. ![]() Since USER_TABLES only has information about the tables that you own, it does not have an OWNER column – the owner, by definition, is you. If you are only concerned with the tables that you own, not those that you have access to, you could use USER_TABLES: SELECT table_name Of course, you may want to exclude certain schemas like SYS and SYSTEM which have large numbers of Oracle tables that you probably don't care about.Īlternatively, if you do not have access to DBA_TABLES, you can see all the tables that your account has access to through the ALL_TABLES view: SELECT owner, table_nameĪlthough, that may be a subset of the tables available in the database ( ALL_TABLES shows you the information for all the tables that your user has been granted access to). If you do not have those privileges but need them, you can request that the DBA explicitly grants you privileges on that table, or, that the DBA grants you the SELECT ANY DICTIONARY privilege or the SELECT_CATALOG_ROLE role (either of which would allow you to query any data dictionary table). This is assuming that you have access to the DBA_TABLES data dictionary view. If you have stored procedures, you can copy them afterwards: If that is the case, use a traditional way to clone a database and then drop the old one: If some table has a trigger, it cannot be moved to another database using above method (will result Trigger in wrong schema error). If your database has no password, remove the -u username -ppassword part. There is no space between the option -p and the password.OR for table in `mysql -u root -ppassword -s -N -e "use old_db show tables from old_db "` do mysql -u root -ppassword -s -N -e "use old_db rename table old_db.$table to new_db.$table " done You will need to adjust the permissions after that.įor scripting in a shell, you can use either of the following: mysql -u username -ppassword old_db -sNe 'show tables' | while read table \ĭo mysql -u username -ppassword -sNe "rename table old_db.$table to new_db.$table" done For InnoDB, the following seems to work: create the new empty database, then rename each table in turn into the new database: RENAME TABLE old_db.table TO new_db.table
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |